第一个表
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为主键，表一有几个记录，查询结果就有几个记录
------解决方案--------------------
- SQL code
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 num1from tab1 a ,tab2 b where a.id=b.idgroup id,name
------解决方案--------------------
- SQL code
declare @t1 table(id int,name varchar(10))insert into @t1select 1, 'li' union allselect 2, 'wang' union allselect 3, 'li' union allselect 4, 'zhao'declare @t2 table(id int,inout int,num int)insert into @t2select 1, 0, 100 union allselect 1, 0, 50 union allselect 1, 1, 100 union allselect 1, 1, 200 union allselect 2, 0, 100 union allselect 2, 0, 100 union allselect 2, 1, 50 union allselect 2, 1, 50 union allselect 3, 0, 100 union allselect 3, 0, 200 union allselect 3, 1, 100 union allselect 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 aleft join @t2 bon a.id=b.idgroup by a.id,a.name/*id name num0 num1----------- ---------- ----------- -----------1 li 150 3003 li 300 1502 wang 200 1004 zhao 0 0*/