高分相送,一个很经典的SQL语句,是高手就进来讨论下!
表A
ID COL1 COL2
1 0.5 SDS
2 2 SD
3 1.5 FG
4 1 GG
5 4 GGH
-------------------------
表B
ID A B C FLAG
1 12 200 300 1
1 13 300 400 0
3 14 400 500 0
4 22 500 600 0
表A与表B必须关联,且得按ID分组,写条SQL语句,查询出如下结果
(不能使用其他存储或者函数)
----------------------------------------
ID | A.COL1 |按flag=1统计A次数|按flag=1统计A次数| B求和 |
1 0.5 1 0 500
2 2 0 0 0
3 1.5 0 1 400
4 1 0 1 500
5 4 0 0 0
[解决办法]
怎么有2列 "按flag=1统计A次数 ",什么意思?
[解决办法]
1 13 300 400 0 --> ０是１吧？
按flag=1统计A次数| B求和 --> 是flag=０吧
好难明白
[解决办法]
是个好题目,顶下先
[解决办法]
是不是你要的结果?
select a.id,a.col1,isnull(bb.c_a,0),isnull(cc.c_a,0),isnull(dd.b_sum,0) from a,
(select id,c_a=count(*) from b where flag= '1 ' group by id) bb,
(select id,c_a=count(*) from b where flag= '0 ' group by id) cc,
(select id,b_sum=sum(B) from b group by id) dd
where
a.id*=bb.id and a.id*=cc.id and a.id*=dd.id
------解决方案--------------------
select a.id,a.col1,isnull(flag_1,0),isnull(flag_0,0),isnull(B_Total,0) from a temp_a
left outer join
(select
id
,sum(case when flag = 1 then 1 else 0 end) as flag_1
,sum(case when flag = 1 then 1 else 0 end) as flag_0
,sum(B) B_Total
from b group by id) temp_b
on temp_a.id = temp_b.id
[解决办法]
简单问题复杂化，不知道你想说什么。。。。。。。。。。
jf
[解决办法]
create table A(ID int,COL1 numeric(9,2),COL2 varchar(10))
insert a
select 1,0.5, 'SDS '
union all select 2,2, 'SD '
union all select 3, 1.5, 'FG '
union all select 4, 1 , 'GG '
union all select 5, 4 , 'GGH '
-------------------------
create table B(ID int, A int, B int, C int, FLAG int)
insert b
select 1 , 12 , 200, 300, 1
union all select 1, 13, 300, 400, 0
union all select 3 , 14, 400, 500, 0
union all select 4, 22, 500, 600, 0
select a.id,a.col1,[按flag=1统计A次数]=isnull(sum(t.flag),0),[按flag=0统计A次数]=isnull(sum(case when t.flag=0 then 1 end ),0),B求和=isnull(t.s,0)
from a left join (
select id,s=b+c,flag from b t where exists (select * from b where id=t.id and t.b+c <isnull(b+c,0)) or id in (select id from b group by id having count(id)=1)
) t on a.id =t.id
group by a.id,a.col1,t.s
drop table a,b
[解决办法]
结果的第一行
ID | A.COL1 |按flag=1统计A次数|按flag=1统计A次数| B求和 |
1 0.5 1 0 500
第二个按flag=1统计A的次数为什么是零？是因为ID=1有一条flag=1 的记录吗？
[解决办法]
select
A.ID ,
A.COL1,
SUM(case B.FLAG when 1 then 1 else 0 end),
SUM(case B.FLAG when 0 then 1 else 0 end),
SUM(B.B)
from
A
left join
B
on
A.ID=B.ID
group by
A.ID,A.COL1
[解决办法]
--可以做参考
select A.ID,A.COL1,sum(case when B.flag=1 then 1 else 0 end) a,
sum(case when B.flag=0 then 1 else 0 end) b,sum(B.B)
from A left join B on A.ID=B.ID
group by A.ID,A.COL1
[解决办法]
select A.[ID],A.[COL1],
sum(case B.flag when 1 then 1 else 0 end) Flag_1,
sum(case B.flag when 0 then 1 else 0 end) Flag_0,
isnull(sum(B.B),0) Sum_B
from A
left join B on A.[ID]=B.[ID]
group by A.[ID],A.[COL1] order by a.[id]
[解决办法]
呵呵
[解决办法]
帮忙顶,楼主,我是菜鸟,将题目再发一片,真不是很明白哟!
[解决办法]
好帖,收藏............
[解决办法]
潜力帖,关注ING.........
[解决办法]
用子查询，没有什么难度吧！
[解决办法]
mark
[解决办法]
收藏，以后慢慢看
