Question

sql中有子查询如何group by！

select (select u.use002 from ysdata.dbo.xtuse u where g.gzl010 = u.use001) as bm,
g.gzl011,g.gzl002,g.gzl005,sum(datediff(MI,g.gzl006,g.gzl007))
from ysbook_010.dbo.ysgzl g
group by bm,g.gzl011,g.gzl002,g.gzl005
这样bm无效，去掉又不行。要怎么办？

Answer 1

select (select u.use002 from ysdata.dbo.xtuse u where g.gzl010 = u.use001) as bm,
g.gzl011,g.gzl002,g.gzl005,sum(datediff(MI,g.gzl006,g.gzl007))
from ysbook_010.dbo.ysgzl g
group by g.gzl010,g.gzl011,g.gzl002,g.gzl005
把bm换成g.gzl010

Answer 2

你可以这样写select u.use002,
g.gzl011,g.gzl002,g.gzl005,sum(datediff(MI,g.gzl006,g.gzl007))
from ysbook_010.dbo.ysgzl g,ysdata.dbo.xtuse u
where g.gzl010 = u.use001
group u.use002,g.gzl011,g.gzl002,g.gzl005

Answer 3

你这个又要嵌套又要汇总的查询，效率肯定不高，写一个存储过程，先分别查询出来，然后在合并起来。

Answer 4

AS只是对这一列起了个别名吧，你试试group by u.use002...或者bm.u.use002....呢？