select qh,bh,
sum(case when exists (select * from xlb where xlb.zcm=yhb.zcm) then 1 else 0 end),
sum(case when exists (select * from xlb where xlb.zcm=yhb.zcm) then xlb.sl else 0 end)
from yhb join xlb group by qh,bh上面三行是一条tsql语句,为了让大家看清我分了三行
执行就出错:大意是聚合函数中不能包含聚合函数或子查询,我想知道的是怎样改这个语句才能实现我上面的意思,我想句子虽然不对,却说明了我想干什么
谢谢大家
sum(case when exists (select * from xlb where xlb.zcm=yhb.zcm) then 1 else 0 end),
sum(case when exists (select * from xlb where xlb.zcm=yhb.zcm) then xlb.sl else 0 end)
from yhb join xlb group by qh,bh上面三行是一条tsql语句,为了让大家看清我分了三行
执行就出错:大意是聚合函数中不能包含聚合函数或子查询,我想知道的是怎样改这个语句才能实现我上面的意思,我想句子虽然不对,却说明了我想干什么
谢谢大家
count(1) as sumcount,
sum(xlb.sl) as sumsl
from yhb join xlb
on yhb.zcm=xlb.zcm
group by qh,bh
第二个是查sum(xlb.sl)的总和
count(*),
sum(xlb.sl)
from yhb join xlb on yhb.zcm=xlb.zcm
group by qh,bh
sum(case when x.zcm is null then 0 else 1 end),
sum(isnull(x.sl,0))
from yhb y
join xlb x on x.zcm = y.zcm
group by y.qh,y.bh
INSERT @a SELECT 1,2,3
UNION ALL SELECT 1,2,5
UNION ALL SELECT 2,3,4DECLARE @b TABLE(c INT,d INT)
INSERT @b SELECT 3,2
UNION ALL SELECT 3,3--小枫的写法
SELECT a.a,a.b,COUNT(1), SUM(b.d)
FROM @a a
INNER JOIN @b b
ON a.c=b.c
GROUP BY a.a,a.b
--楼主的意图
--SELECT a.a,a.b,
-- SUM(CASE WHEN EXISTS (SELECT 1 FROM @b WHERE c=a.c) THEN 1 ELSE 0 END),
/*
执行时,主查询@a第一行,c=3, CASE WHEN 从@b中找c=3的记录,存在,记1
主查询@a第二行,c=5,CASE WHEN 从@b中找c=5的记录,不存在,记0
即 a=1,b=2的分组,得到的值为1
下面的同理
*/
-- SUM(CASE WHEN EXISTS (SELECT 1 FROM @b WHERE c=a.c) THEN d ELSE 0 END)
-- FROM @a a
-- GROUP BY a.a,a.b--所以在这里我不清楚,楼主的意思是小枫的写法,还是自己字面的表达.如果是字面的表达,那么楼主确认后我再写出语句。
select qh,bh,
sum(case when exists (select * from xlb where xlb.zcm=yhb.zcm) then 1 else 0 end),
sum(case when exists (select * from xlb where xlb.zcm=yhb.zcm) then xlb.sl else 0 end)
from yhb join xlb On xlb.zcm=yhb.zcm group by qh,bh