第一个表
id name
1 li
2 wang
3 li
4 zhao
第二个表
id inout num
1 0 100
1 0 50
1 1 100
1 1 200
2 0 100
2 0 100
2 1 50
2 1 50
3 0 100
3 0 200
3 1 100
3 1 50
利用两个表查询,结果如下
id name num0 num1
1 li 150 300
2 wang 200 100
3 li 300 150
4 zhao 0 0
其中num0为同一id下inout=0之和,num1为同一id下inout=1之和,表一中id为主键,表一有几个记录,查询结果就有几个记录
id name
1 li
2 wang
3 li
4 zhao
第二个表
id inout num
1 0 100
1 0 50
1 1 100
1 1 200
2 0 100
2 0 100
2 1 50
2 1 50
3 0 100
3 0 200
3 1 100
3 1 50
利用两个表查询,结果如下
id name num0 num1
1 li 150 300
2 wang 200 100
3 li 300 150
4 zhao 0 0
其中num0为同一id下inout=0之和,num1为同一id下inout=1之和,表一中id为主键,表一有几个记录,查询结果就有几个记录
select id,name,
(select sum(num) from tab2 b ,tab1 a where a.id=b.id and inout='0')as num0,
(select sum(num) from tab2 b ,tab1 a where a.id=b.id and inout='1')as num1
from tab1 a ,tab2 b
where a.id=b.id
group id,name
你好,你的sql语句在查询分析器运行后,得到的num0和num1是所有inout=0和inout=1之和,我需要的是同一id下inout=0和inout=1之和
insert into @t1
select 1, 'li' union all
select 2, 'wang' union all
select 3, 'li' union all
select 4, 'zhao'declare @t2 table(id int,inout int,num int)
insert into @t2
select 1, 0, 100 union all
select 1, 0, 50 union all
select 1, 1, 100 union all
select 1, 1, 200 union all
select 2, 0, 100 union all
select 2, 0, 100 union all
select 2, 1, 50 union all
select 2, 1, 50 union all
select 3, 0, 100 union all
select 3, 0, 200 union all
select 3, 1, 100 union all
select 3, 1, 50select a.id,
a.name,
sum(case when b.inout=0 then b.num else 0 end) num0,
sum(case when b.inout=1 then b.num else 0 end) num1 from @t1 a
left join @t2 b
on a.id=b.id
group by a.id,a.name/*
id name num0 num1
----------- ---------- ----------- -----------
1 li 150 300
3 li 300 150
2 wang 200 100
4 zhao 0 0
*/
还有,当表一中的id在表二中没有出现时,查询也应该有该条目,num0和num1都为0而已