有一个表,表中的记录如下:
序号 工号 数量 日期
6 2681 1100.0 2007-05-05
7 2681 1250.0 2007-10-15
9 2157 1400.0 2007-10-17
10 2890 1200.0 2007-10-17
11 1791 1700.0 2007-10-17
13 2746 1200.0 2007-10-18
14 2890 1350.0 2007-10-18
15 3220 1400.0 2007-10-18
16 3094 1400.0 2007-10-18
17 2087 1350.0 2007-10-24
18 3175 1350.0 2007-10-26
19 2890 1450.0 2007-10-26 现在我想用一条查询语句,将其中各工号中数量最大的‘记录’的序号找出来???? 我用select 工号,数量 form 表 group by 序号 ,能找出各工号中数量最大的记录,但是得不到我想要的序号?苦想了很久,一直没有实现。请大家帮帮我吧
序号 工号 数量 日期
6 2681 1100.0 2007-05-05
7 2681 1250.0 2007-10-15
9 2157 1400.0 2007-10-17
10 2890 1200.0 2007-10-17
11 1791 1700.0 2007-10-17
13 2746 1200.0 2007-10-18
14 2890 1350.0 2007-10-18
15 3220 1400.0 2007-10-18
16 3094 1400.0 2007-10-18
17 2087 1350.0 2007-10-24
18 3175 1350.0 2007-10-26
19 2890 1450.0 2007-10-26 现在我想用一条查询语句,将其中各工号中数量最大的‘记录’的序号找出来???? 我用select 工号,数量 form 表 group by 序号 ,能找出各工号中数量最大的记录,但是得不到我想要的序号?苦想了很久,一直没有实现。请大家帮帮我吧
FROM TABLE1 A
WHERE NOT EXISTS
(SELECT 1 FROM TABLE1 WHERE 工号=a.工号 and 数量>A.数量 )
7 2681 1250.0 2007-10-15
9 2157 1400.0 2007-10-17
10 2890 1200.0 2007-10-17
11 1791 1700.0 2007-10-17
13 2746 1200.0 2007-10-18
14 2890 1350.0 2007-10-18
15 3220 1400.0 2007-10-18
16 3094 1400.0 2007-10-18
17 2087 1350.0 2007-10-24
18 3175 1350.0 2007-10-26
19 2890 1450.0 2007-10-26select a.* from test as a,(select sID,Max(sSL)sSL from test where sID
in (select sID from test group by sID)
group by sID)as b
where a.sID=b.sID and a.sSL=b.sSL
order by a.sNo7 2681 1250.0 2007-10-15
9 2157 1400.0 2007-10-17
11 1791 1700.0 2007-10-17
13 2746 1200.0 2007-10-18
15 3220 1400.0 2007-10-18
16 3094 1400.0 2007-10-18
17 2087 1350.0 2007-10-24
18 3175 1350.0 2007-10-26
19 2890 1450.0 2007-10-26