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')
   ,('A0008', 'TSMC','1','p1')
   ,('A0008', 'TSMC','2','p2')
select *
from TestX


X1
X2
X3
X4


A0001
Tsla
1
p1

A0008
TSMC
1
p1

A0008
TSMC
2
p2


-- case 1
SELECT Top 1 Num as NumLose,X2,X3
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


NumLose
X2
X3


-- 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


A0008
A0002