求一合并SQL语句
- SQL code
select twoqudao as 名称,count(Invoice)as 单数1 from Customer_Service where twoqudao like '%%' andtwoqudao<>'汇展' andtwoqudao<>'非训' andtwoqudao<>'泥煤' andsjfddate is null andkehuqianshoudate is null orkehuqianshoudate='' group by twoqudao--得出如下结果/*姓名 单数1张三 2李思 2王二 1*/select twoqudao as 名称,count(Invoice)as 单数2 from Customer_Servicewhere twoqudao like '%%' andtwoqudao<>'汇展' andtwoqudao<>'非训' and twoqudao<>'泥煤' and sjfddate is null or sjfddate='' group by twoqudao--得出如下结果/*姓名 单数2张三 3李思 2王二 8三五 9*/--最终想要的结果/*姓名 单数1 单数2张三 2 3李思 2 2王二 1 8三五 Null 9*/
[解决办法]
- SQL code
select twoqudao as 名称,count(Invoice)as 单数1 from Customer_Service where twoqudao like '%%' andtwoqudao<>'汇展' andtwoqudao<>'非训' andtwoqudao<>'泥煤' andsjfddate is null and(isnull(kehuqianshoudate,'')='' or isnull(sjfddate,'')='')group by twoqudao
[解决办法]
- SQL code
select a.*,b.单数2from(select twoqudao as 名称,count(Invoice)as 单数1 from Customer_Service where twoqudao like '%%' andtwoqudao<>'汇展' andtwoqudao<>'非训' andtwoqudao<>'泥煤' andsjfddate is null andkehuqianshoudate is null orkehuqianshoudate='' group by twoqudao)aleft join(select twoqudao as 名称,count(Invoice)as 单数2 from Customer_Servicewhere twoqudao like '%%' andtwoqudao<>'汇展' andtwoqudao<>'非训' and twoqudao<>'泥煤' and sjfddate is null or sjfddate='' group by twoqudao)bon a.名称=b.名称