CREATE TABLE A(ID NCHAR(5),QTY INT)
INSERT A(ID,QTY) SELECT 'A0001',457
INSERT A(ID,QTY) SELECT 'A0002',789
INSERT A(ID,QTY) SELECT 'A0003',145
INSERT A(ID,QTY) SELECT 'A0002',877
INSERT A(ID,QTY) SELECT 'A0003',123
INSERT A(ID,QTY) SELECT 'A0002',756
INSERT A(ID,QTY) SELECT 'A0007',999
INSERT A(ID,QTY) SELECT 'A0006',577
INSERT A(ID,QTY) SELECT 'A0005',478
--SELECT * FROM A
--SELECT DISTINCT ID FROM A
SELECT TOP 2 ID FROM A GROUP BY ID ORDER BY SUM(QTY)
-- 用时2秒
SELECT A.* FROM A JOIN (SELECT TOP 2 ID FROM A GROUP BY ID ORDER BY SUM(QTY))I ON A.ID=I.ID
-- 要求一语句，得到除TOP 2 ID 以外所有ID的明细记录，如何做？
DROP TABLE A
------解决方案--------------------
不用not in 就用关联，如：
select A.*
from A
left join B on a.id=b.id
where b.id is null
------解决方案--------------------
--try
SELECT A.* FROM A left JOIN (SELECT TOP 2 ID FROM A GROUP BY ID ORDER BY SUM(QTY))I ON A.ID=I.ID where i.id is null