求一条两个表的查询语句
第一个表
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*/