Select a.test_id,a.test_descr, Wave0 =sum(case b.wave when 0 then 1 else 0 end), Wave1 =sum(case b.wave when 1 then 1 else 0 end), from test as a inner join test_result as b on a.test_id=b.test_id-------------
from 前多了个逗号,不用order by 行吗? Select a.test_id,a.test_descr, Wave0 =sum(case b.wave when 0 then 1 else 0 end), Wave1 =sum(case b.wave when 1 then 1 else 0 end), from test as a inner join test_result as b on a.test_id=b.test_id
Select a.test_id,a.test_descr, Wave0 =sum(case b.wave when 0 then 1 else 0 end), Wave1 =sum(case b.wave when 1 then 1 else 0 end), from test as a inner join test_result as b on a.test_id=b.test_id order by a.test_id,a.test_descr
Select a.test_id,a.test_descr, Wave0 =sum(case b.wave when 0 then 1 else 0 end), Wave1 =sum(case b.wave when 1 then 1 else 0 end) from test a inner join test_result b on a.test_id=b.test_id group by a.test_id,a.test_descr
select a.*,b.wave0,b.wave1 from test a join (select test_id,wave0=sum(case wave when 0 then 1 else 0 end) ,wave1=sum(case wave when 1 then 1 else 0 end) from test_result group by test_id)b on a.test_id=b.test_id
select a.*,b.wave0,b.wave1 from test a join (select test_id,wave0=sum(case wave when 0 then 1 else 0 end) ,wave1=sum(case wave when 1 then 1 else 0 end) from test_result group by test_id)b on a.test_id=b.test_id测试OK:1 aa 1 1 2 bb 1 0
谢谢各位帮忙, chinaandys(风流泪,雨含笑) 是对的
Select a.test_id,a.test_descr, Wave0 =sum(case b.wave when 0 then 1 else 0 end), Wave1 =sum(case b.wave when 1 then 1 else 0 end), from test a inner join test_result b on a.test_id=b.test_id
Wave0 =sum(case b.wave when 0 then 1 else 0 end),
Wave1 =sum(case b.wave when 1 then 1 else 0 end),
from test as a inner join test_result as b
on a.test_id=b.test_id-------------
Select a.test_id,a.test_descr,
Wave0 =sum(case b.wave when 0 then 1 else 0 end),
Wave1 =sum(case b.wave when 1 then 1 else 0 end),
from test as a inner join test_result as b
on a.test_id=b.test_id
Wave0 =sum(case b.wave when 0 then 1 else 0 end),
Wave1 =sum(case b.wave when 1 then 1 else 0 end),
from test as a inner join test_result as b
on a.test_id=b.test_id
order by a.test_id,a.test_descr
Wave0 =sum(case b.wave when 0 then 1 else 0 end),
Wave1 =sum(case b.wave when 1 then 1 else 0 end)
from test a inner join test_result b
on a.test_id=b.test_id
group by a.test_id,a.test_descr
test_descr 是text类型
test_id test_descr wave0 wave1
1 aa 1 1
2 bb 1 0
(select test_id,wave0=sum(case wave when 0 then 1 else 0 end)
,wave1=sum(case wave when 1 then 1 else 0 end)
from test_result group by test_id)b
on a.test_id=b.test_id
(select test_id,wave0=sum(case wave when 0 then 1 else 0 end)
,wave1=sum(case wave when 1 then 1 else 0 end)
from test_result group by test_id)b
on a.test_id=b.test_id测试OK:1 aa 1 1
2 bb 1 0
Wave0 =sum(case b.wave when 0 then 1 else 0 end),
Wave1 =sum(case b.wave when 1 then 1 else 0 end),
from test a inner join test_result b
on a.test_id=b.test_id