求解SQL写法(50分)

  • 主题发起人 WilliamGui
  • 开始时间
W

WilliamGui

Unregistered / Unconfirmed
GUEST, unregistred user!
有一个表结构与数据如下:
itemcode moldno shapeqty moldthroughput outmoldqty
-------------------- ------------ -------------- -------------- ----------
ddddadf HS018 400 130 1
07010090500150 HS018 500 130 1
07010090500151 HS018 500 130 1
......
假如moldno相同，取shapeqty大值的记录，如果大值的记录同时有多于一条记录的，
比如上面举例数据中有两条500最大值的记录，则返其中一条记录，
这条SQL语句如何写????? 需要返回所有字段。我想把它定义成游标来用。
 
补充一点只有itemcode字段唯一
 
Select * from table where itemcode=(Select Top 1 Itemcode Max(Shapeqty) from Table)
 
select top 1 * from TABLE
order by itemcode,moldno,shapeqty desc
group by itemcode,moldno

 
select * from table where itemcode=(select top 1 itemcode max(shapeqty) from table)
 
我作过类似的查询，思路应该是这样，具体要你自己去试：
select a.itemcode,a.moldno,a.shapeqty,a.moldthroughput,a.outmoldqty from 表 a,表 b
where (a.itemcode=b.itemcode) and (a.itemcode>b.itemcode)
group by a.moldno,a.shapeqey having max(a.shapeqty)
 
SELECT A。* FROM TABLE A INNER JOIN
（SELECT moldno，MAX（shapeqty ） AS SHAPEQTY，MAX（itemcode） AS itemcode
FROM TABLE GROUP BY moldno） B
ON A.moldno=B.moldno AND A.shapeqty=B.shapeqty AND A.itemcode=B.itemcode
 
搞错了应该是itemcode与moldno合起来才是唯一的
 
拜托大家试一试好不好，有很多都有语法错误，
可以说大家以上的回答都不管用
 
SELECT STOP 1 * FROM TABLE WHERE shapeqty = (SELECT MAX(shapeqty) FROM TABLE )
 
筆誤﹐應該是:
SELECT TOP 1 * FROM TABLE WHERE shapeqty = (SELECT MAX(shapeqty) FROM TABLE )
 
请试一试：
select max(shapeqty) as shapeqty from table group by moldno,shapeqty
 
select * from
tablename a
where a.shapeqty =
(select max(shapeqty) from tablename as b
where a.moldno = b.moldno
and a.itemcode = b.itemcode
group by b.itemcode,b.moldno)
order by itemcode,moldno

试试，如果行加分
 
测试过的:
SELECT *
FROM TableName D JOIN (
SELECT MAX(a.itemcode) as ItemCode,b.moldno as moldno,b.SHAPEQTY as SHAPEQTY
FROM TableName A JOIN
(SELECT moldno,MAX (shapeqty) AS SHAPEQTY
FROM TableName
GROUP BY moldno
) B
ON A.moldno=B.moldno AND A.shapeqty=B.shapeqty
GROUP BY b.moldno,b.SHAPEQTY
) C
ON D.moldno=C.moldno AND D.shapeqty=C.shapeqty AND D.ItemCode = C.ItemCode

 
您必须登录或注册才可回复。

Similar threads

Ray
[转]终于可以发布Delphi下收点OSGI精髓皮毛而设计的插件框架WisdomPluginFramework
回复
4
查看
4K
chlkuei
C
W
求SQL写法(30分)
回复
11
查看
62
WilliamGui
W
H
sql语句怎么写啊(50分)
回复
13
查看
66
ldaan3
L
龙
急需解决的问题???????????????(100分)
回复
6
查看
64
龙之天涯
龙
D
sql语句(50分)
回复
11
查看
82
delphi188
D
顶部