select tk_pici, tk_kechen, sum(case when tk_chengji='合格'then 1 else 0 end) as expr1002 from b_tongkao group by tk_pici, tk_kechen
access 不支持case 结构,关键是我觉得我鞋的代码没什么问题
SELECT tk_pici,tk_kecheng ,(select count(*) from tb_tongkao AS T2 WHERE T1.tk_pici=T2.tk_pici AND T1.tk_kecheng=T2.tk_kecheng AND tk_chengji='合格') FROM tb_tongkao AS T1 --应该是这样,不过子查询不怎么好--CASE 没有 就用 SWITCH替代 select tk_pici, tk_kechen, SUM(SWITCH( tk_chengji='合格',1, True,0))as expr1002 from b_tongkao group by tk_pici, tk_kechen
SELECT tk_pici,tk_kecheng ,(select count(*) from tb_tongkao AS T2 WHERE T1.tk_pici=T2.tk_pici AND T1.tk_kecheng=T2.tk_kecheng AND tk_chengji='合格') FROM tb_tongkao AS T1你的子查询少了个关键的东西
select
tk_pici,
tk_kechen,
sum(case when tk_chengji='合格'then 1 else 0 end) as expr1002
from b_tongkao
group by tk_pici, tk_kechen
,(select count(*) from tb_tongkao AS T2
WHERE T1.tk_pici=T2.tk_pici
AND T1.tk_kecheng=T2.tk_kecheng
AND tk_chengji='合格')
FROM tb_tongkao AS T1
--应该是这样,不过子查询不怎么好--CASE 没有 就用 SWITCH替代
select
tk_pici,
tk_kechen,
SUM(SWITCH(
tk_chengji='合格',1,
True,0))as expr1002
from b_tongkao
group by tk_pici, tk_kechen
,(select count(*) from tb_tongkao AS T2
WHERE T1.tk_pici=T2.tk_pici
AND T1.tk_kecheng=T2.tk_kecheng
AND tk_chengji='合格')
FROM tb_tongkao AS T1你的子查询少了个关键的东西