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 |