求条SQL语句,多谢

求条SQL语句,谢谢

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