SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TestX(
    X1 varchar(20) --代碼
  , X2 varchar(20) --公司
  , X3 varchar(20) --序號
  , X4 varchar(20) --人員
);

INSERT INTO
  TestX(X1,X2,X3,X4)
VALUES
    ('A0001', 'Tsla','1','p1')
  , ('A0001', 'Tsla','2','p2')
  , ('A0001', 'Tsla','4','p4')
  , ('A0002', 'TSMC','1','p1')
  , ('A0003', 'Google','1','p1')
  , ('A0005', 'Apple','1','p1')
  
select *
from TestX


X1
X2
X3
X4


A0001
Tsla
1
p1

A0001
Tsla
2
p2

A0001
Tsla
4
p4

A0002
TSMC
1
p1

A0003
Google
1
p1

A0005
Apple
1
p1


-- case 1
SELECT Top 1 X2,X3,Num as NumLose
FROM (
SELECT AA.X2,AA.X3,COUNT(1) AS Num
FROM TestX AS AA
INNER JOIN TestX AS BB ON BB.X2 = AA.X2 AND BB.X3 <= AA.X3
GROUP BY AA.X2,AA.X3
) AS TEMP
WHERE X2='Tsla'
AND X3<>CONVERT(VARCHAR,Num)
ORDER BY X2,Num


X2
X3
NumLose


Tsla
4
3


-- case 2
SELECT Top 1 X1,Code as CodeLose
FROM (
SELECT AA.X1,'A'+RIGHT(CONVERT(VARCHAR,10000+COUNT(1)),4) AS Code
FROM (
SELECT DISTINCT X1
FROM TestX) AS AA
INNER JOIN (SELECT DISTINCT X1
FROM TestX) AS BB ON BB.X1 <= AA.X1 
GROUP BY AA.X1
) AS TEMP
WHERE X1<>Code
ORDER BY X1,Code


X1
CodeLose


A0005
A0004

X1	X2	X3	X4
A0001	Tsla	1	p1
A0001	Tsla	2	p2
A0001	Tsla	4	p4
A0002	TSMC	1	p1
A0003	Google	1	p1
A0005	Apple	1	p1