简单问题不会做

[九斤半]

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

[dawugui]

啥意思?

[OracleRoob]

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 *
from A as t
where id not in (SELECT TOP 2 ID FROM A GROUP BY ID ORDER BY SUM(QTY))
-- 要求一语句，得到除TOP 2 ID 以外所有ID的明细记录，如何做？


DROP TABLE A

[九斤半]

不知不为过，不学就是错！

我要求除此TOP 2 ID以外的ID的明细记录，

not in 我用过了，太慢。

[wgzaaa]

这样不行？
SELECT A.* FROM A where id not in (SELECT TOP 2 ID FROM A GROUP BY ID ORDER BY SUM(QTY))

[OracleRoob]

不用not in 就用关联，如：

select A.*
from A
left join B on a.id=b.id
where b.id is null

[wgzaaa]

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

[九斤半]

呵呵，是啊，关联很快，就是不知道如何得到除此TOP 2 ID以外的ID的明细记录



SELECT TOP 2 ID FROM A GROUP BY ID ORDER BY SUM(QTY)) -- 这一句是此TOP 2 ID的记录，OK。

????????????????????????????????????????????????????? -- 另一句不会写。

[OracleRoob]

left join要比not in 效率高

在Access中，这一点体现的尤其明显，用not in 数据量一大，就没有响应了。

[九斤半]

:)

wgzaaa的MS可以了，我一直在想如何得到其余的ID记录集，没这样想过。

[九斤半]

不知不为过，不学就是错！ 先回帖，没注意看到...谢谢大家。

[dawugui]

看了看,还是not in 的好.
我用过两表各20多万的not in 的操作,是oracle 数据库,服务器配置较高.花时间3-5分钟.
如果非要整个几百上千万的记录来not in,那就爽歪歪了.

[wgzaaa]

wangtiecheng 也是这意思。想多了要注意休息，
回复老出不来，唉新版问题还是我机器的问题?江民对它感兴趣