我有这莫一组数, 我想按照‘Shift’ 来取值,取每个‘Shift’ 的前两名(但要确保所取值不在第二表中, 如果有,则顺延),请问如何写???
Score Shift
20 B
30 B
10 B
78 A
19 A
5 A
40 C
25 C
6 C另一个表
Score Shift
20 B
Score Shift
20 B
30 B
10 B
78 A
19 A
5 A
40 C
25 C
6 C另一个表
Score Shift
20 B
and 2>(select count(1) from tb a where a.shift=shift and a.score>score)注,有重复值时此取法不准。需要标识列参与运算
INSERT TEST
SELECT 20 ,'B' UNION ALL
SELECT 30 ,'B' UNION ALL
SELECT 10 ,'B' UNION ALL
SELECT 78 ,'A' UNION ALL
SELECT 19 ,'A' UNION ALL
SELECT 5 ,'A' UNION ALL
SELECT 40 ,'C' UNION ALL
SELECT 25 ,'C' UNION ALL
SELECT 6 ,'C'
SELECT * FROM TESTCREATE TABLE B(core INT,Shift VARCHAR(10))
INSERT B
SELECT 20,'B'
--SELECT * FROM BSELECT *
FROM TEST m
WHERE
(CORE IN
(
SELECT TOP 2 CORE FROM TEST WHERE SHIFT = m.SHIFT AND CORE NOT IN(SELECT CORE FROM B))
)
ORDER BY SHIFTDROP TABLE TEST,B
没有自动向后顺延呀。
我是这样写的SELECT TOP 100 PERCENT A.BadgeNumber, A.Score, A.FYPD
FROM dbo.TestLastPDFin A CROSS JOIN
dbo.LastPD_WarningLetter B
WHERE (NOT EXISTS
(SELECT 1
FROM dbo.TestLastPDFin
WHERE PE = A.PE AND Score > A.Score
HAVING COUNT(*) > 1)) AND (A.BadgeNumber NOT IN
(SELECT b.BadgeN
FROM dbo.LastPD_WarningLetter))
ORDER BY A.Score DESC