或者 Select sz,sjsfty, count(*)as total from l_Zjgcsshspb where sjsfty='合格'group by sz,sjsfty union Select sz,sjsfty, count(*)as total from l_Zjgcsshspb where sjsfty='不合格'group by sz,sjsfty
Select sz,sjsfty, count(*)as total from l_Zjgcsshspb group by sz,sjsfty 简单点就可以了吧,弄那么复杂干什么,试试看。
select sz,'合格' as 是否合格,sum(case when sjsfty='合格' then 1 else 0 end ) as total from l_zjgcsshsph group by sz union select sz,'不合格',sum(case when sjsfty='不合格' then 1 else 0 end ) from l_zjgcsshsph gruop by sz order by sz,是否合格
这在sql server下支持,在access下确不行,这是标准的sql语句吗?
不是标准的,CASE在ACCESS中不支持。
在access下执行没有问题,我测试了
没试过:(可以用IIF代替select sz,'合格' as 是否合格,sum(iif(sjsfty='合格' , 1 , 0)) as total from l_zjgcsshsph group by sz union select sz,'不合格',sum(iif(sjsfty='不合格' , 1 , 0)) from l_zjgcsshsph gruop by sz order by sz,是否合格
Select sz,sjsfty, count(*)as total from l_Zjgcsshspb where sjsfty='合格'group by sz,sjsfty
union
Select sz,sjsfty, count(*)as total from l_Zjgcsshspb where sjsfty='不合格'group by sz,sjsfty
union
select sz,'不合格',sum(case when sjsfty='不合格' then 1 else 0 end )
from l_zjgcsshsph gruop by sz
order by sz,是否合格
union
select sz,'不合格',sum(iif(sjsfty='不合格' , 1 , 0))
from l_zjgcsshsph gruop by sz
order by sz,是否合格
为什么iif在access里就能用呢?iif在sql server2000里能用吗?