- SQL code
--总单数select twoqudao as 客户,COUNT(Invoice) as 总单数 from Connect where twoqudao is not null group by twoqudao/*客户 总单数道外 3盛物 211邦物 437勤美 5011邦快 836丰速 1鸿物 568莞包 2*/--已完成单数select twoqudao as 客户,COUNT(Invoice) as 已完成单数 from Connect where twoqudao is not null and khdate is not null group by twoqudao/*客户 已完成单数道外 3盛物 204邦物 349勤美 4986邦快 822丰速 1鸿物 518莞包 2*/--未完成单数select twoqudao as 客户,COUNT(Invoice) as 未完成单数 from Connect where twoqudao is not null and khdate is null group by twoqudao/*客户 未完成单数道外 0盛物 7邦物 88勤美 25邦快 14丰速 0鸿物 50莞包 0*/--超期单数select twoqudao as 客户,COUNT(Invoice) as 超期单数 from Connect where twoqudao is not null and khdate is null and cqdate<0 group by twoqudao /*客户 超期单数道外 0盛物 6邦物 48勤美 3邦快 12丰速 0鸿物 38莞包 0 */把四条语句合并成一条,得出以下结果--得出结果/*客户 总单数 已完成单数 未完成单数 超期单数道外 3 3 0 0盛物 211 204 7 6邦物 437 349 88 48勤美 5011 4986 25 3邦快 836 822 14 12丰速 1 1 0 0鸿物 568 518 50 38莞包 2 2 0 0*/
------解决方案--------------------
- SQL code
SELECT * FROM T1.twoqudao as 客户,T2.总单数,T3.已完成单数,T4.超期单数FROM (select twoqudao ,COUNT(Invoice) as 总单数 from Connect where twoqudao is not null group by twoqudao) T1,(select twoqudao ,COUNT(Invoice) as 已完成单数 from Connect where twoqudao is not null and khdate is not null group by twoqudao) T2,(select twoqudao ,COUNT(Invoice) as 未完成单数 from Connect where twoqudao is not null and khdate is null group by twoqudao) T3,(select twoqudao ,COUNT(Invoice) as 超期单数 from Connect where twoqudao is not null and khdate is null and cqdate<0 group by twoqudao )T4WHERE T1.twoqudao=T2.twoqudao AND T2.twoqudao=T3.twoqudao AND T3.twoqudao=T4.twoqudao
------解决方案--------------------
- SQL code
SELECT T1.twoqudao as 客户,T2.总单数,T3.已完成单数,T4.超期单数FROM (select twoqudao ,COUNT(Invoice) as 总单数 from Connect where twoqudao is not null group by twoqudao) T1,(select twoqudao ,COUNT(Invoice) as 已完成单数 from Connect where twoqudao is not null and khdate is not null group by twoqudao) T2,(select twoqudao ,COUNT(Invoice) as 未完成单数 from Connect where twoqudao is not null and khdate is null group by twoqudao) T3,(select twoqudao ,COUNT(Invoice) as 超期单数 from Connect where twoqudao is not null and khdate is null and cqdate<0 group by twoqudao )T4WHERE T1.twoqudao=T2.twoqudao AND T2.twoqudao=T3.twoqudao AND T3.twoqudao=T4.twoqudao
------解决方案--------------------
select twoqudao as 客户,(select COUNT(1) from Connect b where b.twoqudao=a.twoqudao) as '总单数',
(select COUNT(1) from Connect c where khdate is not null and c.twoqudao=a.twoqudao) as '已完成单数',
(select COUNT(1) from Connect d where khdate is null and d.twoqudao=a.twoqudao) as '未完成单数',
(select COUNT(1) from Connect e where khdate is not null and cqdate<0 and e.twoqudao=a.twoqudao) as '超期单数'
from Connect a
group by twoqudao
order by '总单数' desc