表结构
F_COMPANY F_ISSUBMIT F_ISVALID
A 0 1
A 1 1
A 1 1
B 0 1
B 0 1
.....要求按F_COMPANY分组,分别提取出每个公司的总的记录数、有效的记录数(F_ISVALID=1)、已经提交的记录数(F_ISSUBMIT=1 AND F_ISVALID=1)、未提交的记录数(F_ISSUBMIT=0 AND F_ISVALID=1)
F_COMPANY F_ISSUBMIT F_ISVALID
A 0 1
A 1 1
A 1 1
B 0 1
B 0 1
.....要求按F_COMPANY分组,分别提取出每个公司的总的记录数、有效的记录数(F_ISVALID=1)、已经提交的记录数(F_ISSUBMIT=1 AND F_ISVALID=1)、未提交的记录数(F_ISSUBMIT=0 AND F_ISVALID=1)
COUNT(*) AS 总数,
SUM(CASE WHEN F_ISSUBMIT=1 AND F_ISVALID=1 THEN 1 ELSE 0 END) AS 已经提交的记录数,
SUM(CASE WHEN F_ISSUBMIT=0 AND F_ISVALID=1 THEN 1 ELSE 0 END) AS 未提交的记录数
FROM tb
GROUP BY F_COMPANY
F_COMPANY,
COUNT(F_COMPANY)AS 总记录数,
有效的记录数,
已经提交的记录数,
未提交的记录数from tb ttjoin
(SELECT F_ISVALID ,COUNT(*) AS 有效的记录数 FROM TB WHERE F_ISVALID =1 GROUP BY F_COMPANY ) as ton tt.F_COMPANY=t.F_COMPANY
join (SELECT F_ISVALID ,COUNT(*) AS 已经提交的记录数 FROM TB F_ISSUBMIT=1 AND F_ISVALID=1 GROUP BY F_COMPANY )as t1
on tt.F_COMPANY=t1.F_COMPANYjoin
(SELECT F_ISVALID ,COUNT(*) AS 未提交的记录数 FROM TB F_ISSUBMIT=0 AND F_ISVALID=1 GROUP BY F_COMPANY )as t2
on tt.F_COMPANY=t2.F_COMPANY
[有效记录数]=(select count(*) from @Tb where F_ISVALID=1 ),
[提交记录数]=(select count(*) from @Tb where F_ISSUBMIT=1 AND F_ISVALID=1 ),
[未提交记录数]=(select count(*) from @Tb where F_ISSUBMIT=0 AND F_ISVALID=1)
from @Tb
group by f_comany--我是这样写的,提示:列名 'f_comany' 无效,怎么改就可以了?