select twoqudao as 名称,count(Invoice)as 单数1 from Customer_Service
where twoqudao like '%%' and
twoqudao<>'汇展' and
twoqudao<>'非训' and
twoqudao<>'泥煤' and
sjfddate is null and
kehuqianshoudate is null or
kehuqianshoudate=''
group by twoqudao
--得出如下结果
/*姓名 单数1
张三 2
李思 2
王二 1
*/
select twoqudao as 名称,count(Invoice)as 单数2 from Customer_Service
where twoqudao like '%%' and
twoqudao<>'汇展' and
twoqudao<>'非训' 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
*/
解决方案 »
- 用 varchar 型保存数字是规范的设计吗,哪种情况下需要用字符型来保存数字呢?
- ------------十进制转十六进制------------
- 关于下面一段查询的问题。
- 求一个关于树结构的sql语句
- 求条sql语句
- 新建SQL SERVER注册失败,报“无效的连接。(ConnectionOpen(Invalid Instance()))”,怎么设置也不行!
- sql 存储过程那位大神帮我看看那块出错了?
- sql 2005 动力费分摊到零件精确分配 精度不够 出现较大误差
- numeric字段的数字怎么读?
- 在SQL Server 2000 中如何写Select语句,使得Select语句可以返回Select结果集中的任意行?如第五行
- 小弟第一次求救SQL封号问题
- 【分享】,分享几个常用的分组函数,配简单事例。
select twoqudao as 名称,count(Invoice)as 单数1 from Customer_Service
where twoqudao like '%%' and
twoqudao<>'汇展' and
twoqudao<>'非训' and
twoqudao<>'泥煤' and
sjfddate is null and
(isnull(kehuqianshoudate,'')='' or isnull(sjfddate,'')='')
group by twoqudao
--应是这样子
select twoqudao as 名称,
count(case when isnull(kehuqianshoudate,'')='' then Invoice else null end)as 单数1
count(case when isnull(sjfddate,'')='' then Invoice else null end)as 单数2
from Customer_Service
where twoqudao like '%%' and
twoqudao<>'汇展' and
twoqudao<>'非训' and
twoqudao<>'泥煤' and
sjfddate is null and
(isnull(kehuqianshoudate,'')='' or isnull(sjfddate,'')='')
group by twoqudao
select twoqudao as 名称,
count(case when isnull(kehuqianshoudate,'')='' then Invoice else null end)as 单数1
count(case when isnull(sjfddate,'')='' then Invoice else null end)as 单数2
from Customer_Service
where twoqudao like '%%' and
twoqudao<>'汇展' and
twoqudao<>'非训' and
twoqudao<>'泥煤' and
--sjfddate is null and去掉,呵呵
(isnull(kehuqianshoudate,'')='' or isnull(sjfddate,'')='')
group by twoqudao
a.*,b.单数2
from
(
select twoqudao as 名称,count(Invoice)as 单数1 from Customer_Service
where twoqudao like '%%' and
twoqudao<>'汇展' and
twoqudao<>'非训' and
twoqudao<>'泥煤' and
sjfddate is null and
kehuqianshoudate is null or
kehuqianshoudate=''
group by twoqudao
)a
left join
(
select twoqudao as 名称,count(Invoice)as 单数2 from Customer_Service
where twoqudao like '%%' and
twoqudao<>'汇展' and
twoqudao<>'非训' and
twoqudao<>'泥煤' and
sjfddate is null or
sjfddate=''
group by twoqudao
)b
on
a.名称=b.名称
a.*,b.单数2
from
(
select twoqudao as 名称,count(Invoice)as 单数1 from Customer_Service
where twoqudao like '%%' and
twoqudao<>'汇展' and
twoqudao<>'非训' and
twoqudao<>'泥煤' and
sjfddate is null and
kehuqianshoudate is null or
kehuqianshoudate=''
group by twoqudao
)a
left join
(
select twoqudao as 名称,count(Invoice)as 单数2 from Customer_Service
where twoqudao like '%%' and
twoqudao<>'汇展' and
twoqudao<>'非训' and
twoqudao<>'泥煤' and
sjfddate is null or
sjfddate=''
group by twoqudao
)b
on
a.名称=b.名称