怎样从一个查询中查询出指定的记录呀?

[XuFrank]

现有一个查询
SELECT a.Line, b.DefectID,SUM(Badness_Qty) AS BadQty
FROM QAM_ProductInspection a, QAM_PInsptDefect b
WHERE a.SID=b.SID AND BuildDate>='2006-10-01' AND BuildDate<='2006-11-30'
GROUP BY a.Line,b.DefectID
查到的记录为
Line DefectID BadQty
100 A1 16
100 A2 5
100 A3 1
200 A3 2
200 B1 5

现在要从这些记录查询出每个Line最大BadQty的记录:
100 A1 16
200 B1 5
如果用GROUP BY Line,只能MAX(BadQty),但DefectID怎样给带出来呢????????
有没有什么好的方法呀?????

[dawugui]

SELECT A.* FROM TB A,
(SELECT LINE,MAX(BADQTY) AS BADQTY FROM TB GROUP BY LINE) B
WHERE A.LINE = B.LINE AND A.BADQTY = B.BADQTY

此处TB为你的
Line DefectID BadQty
100 A1 16
100 A2 5
100 A3 1
200 A3 2
200 B1 5

[XuFrank]

谢谢!
能不能有更简单的查询,我只想再查询一次得出记录???????

[akuzou]

SELECT A.* FROM TB A where BadQty in (SELECT MAX(BADQTY) AS BADQTY FROM TB GROUP BY LINE)

[XuFrank]

这样嵌套出来的SQL语句太长,而且也不好懂,
有没有什么简单明了的方法呀........

SELECT a.*
FROM (SELECT a.Line, b.DefectID,SUM(Badness_Qty) AS BadQty
FROM QAM_ProductInspection a, QAM_PInsptDefect b
WHERE a.SID=b.SID
AND BuildDate>='2006-10-01' AND BuildDate<='2006-11-30'
GROUP BY a.Line,b.DefectID) a,
(
SELECT a.Line, MAX(a.BadQty) AS BadQty
FROM
(SELECT a.Line, b.DefectID,SUM(Badness_Qty) AS BadQty
FROM QAM_ProductInspection a, QAM_PInsptDefect b
WHERE a.SID=b.SID
AND BuildDate>='2006-10-01' AND BuildDate<='2006-11-30'
GROUP BY a.Line,b.DefectID) a
GROUP BY a.Line
) b
WHERE a.Line=b.Line AND a.BadQty=b.BadQty

[crazyflower]

create table ttt
(
line nvarchar(255),
defectid nvarchar(255),
badqty int
)

insert into ttt select '100','a1',16
union all select '100','a2',5
union all select '100','a3',1
union all select '200','a3',2
union all select '200','b1',5

select B.line,A.defectid,B.badqty
from
(select line,max(badqty) as badqty
from ttt
group by line) B
left join
(select line,defectid,badqty from ttt) A
on A.line=B.line and A.badqty=B.badqty

drop table ttt

[XuFrank]

如果用游标取代嵌套,会不会快些.....

DECLARE csr CURSOR /*定义 */
FOR SELECT a.*
FROM ( SELECT a.Line, b.DefectID,SUM(Badness_Qty) AS BadQty
FROM QAM_ProductInspection a, QAM_PInsptDefect b
WHERE a.SID=b.SID AND (BuildDate>=@tmpdate AND BuildDate<=@vdate)
GROUP BY a.Line,b.DefectID) a
ORDER BY a.Line ASC, a.BadQty DESC

OPEN csr /*打开 */

FETCH csr INTO @line ,@defectid, @badqty /*循环,相当于MoveNext */
WHILE (@@FETCH_STATUS<>0) /*测试是否到了结尾*/
BEGIN
IF(@prevline<>@line)
BEGIN
SET @prevline=@line
得到当前@line的最大ID记录
END
FETCH csr INTO @line ,@defectid, @badqty
END
CLOSE csr /*关闭 */
DEALLOCATE csr /*释放 */