如果还是这样的话 select sum(case when cus_status='未确认' then 1 else 0 end) as cus_none, sum(case when cus_status='确认' then 1 else 0 end) as cus_confirm , sum(case when cus_status='看房' then 1 else 0 end) as cus_abandon, sum(case when cus_status='签约' then 1 else 0 end) as cus_sign , sum(case when cus_status='已放弃' then 1 else 0 end) as cus_abandon from min_customs where datediff(year,cus_time,getdate())=0
将字段为null该为0,还有这个问题??帮忙看一下,谢谢!
nullif()不能实现!我试过了!
假设你的数据只有2014年的。 select yuefen,cus_status,count(cus_status) as amount from ( select month(cus_time) as yuefen,cus_status from min_customs ) as a group by yuefen,cus_status order by yuefen如果不是你要的结果,你还是把结果贴出来,不然不知道你要的是什么。
感觉楼主是想要2014年 对月份分组, 然后得出各个状态的的行数。select sum(case when cus_status='未确认' then 1 else 0 end) as cus_none, sum(case when cus_status='确认' then 1 else 0 end) as cus_confirm , sum(case when cus_status='看房' then 1 else 0 end) as cus_abandon, sum(case when cus_status='签约' then 1 else 0 end) as cus_sign , sum(case when cus_status='已放弃' then 1 else 0 end) as cus_abandon, from min_customs where datepart(yy,cus_time)='2014' group by datepart(mm,cus_time)
select sum(case when cus_status='未确认' then 1 else 0 end) as cus_none, sum(case when cus_status='确认' then 1 else 0 end) as cus_confirm , sum(case when cus_status='看房' then 1 else 0 end) as cus_abandon, sum(case when cus_status='签约' then 1 else 0 end) as cus_sign , sum(case when cus_status='已放弃' then 1 else 0 end) as cus_abandon,month(cus_time) as yue from min_customs where datepart(yy,cus_time)='2014' group by datepart(mm,cus_time),month(cus_time) 月份已经出来了!只差显示的是所有月份的行,不管里面有没有数据!!帮帮忙,大家!!!
SELECT a.number AS yue, b.cus_none , b.cus_confirm , b.cus_abandon , b.cus_sign , b.cus_abandon FROM ( SELECT number FROM master..spt_values P WHERE type = 'P' AND number BETWEEN 1 AND 12 ) a LEFT JOIN ( SELECT SUM(CASE WHEN cus_status = '未确认' THEN 1 ELSE 0 END) AS cus_none , SUM(CASE WHEN cus_status = '确认' THEN 1 ELSE 0 END) AS cus_confirm , SUM(CASE WHEN cus_status = '看房' THEN 1 ELSE 0 END) AS cus_abandon , SUM(CASE WHEN cus_status = '签约' THEN 1 ELSE 0 END) AS cus_sign , SUM(CASE WHEN cus_status = '已放弃' THEN 1 ELSE 0 END) AS cus_abandon , MONTH(cus_time) AS yue FROM min_customs WHERE DATEPART(yy, cus_time) = '2014' GROUP BY DATEPART(mm, cus_time) , MONTH(cus_time) ) b ON a.number = b.yue参考
SELECT a.number AS yue, isnull( b.cus_none ,0), isnull(b.cus_confirm ,0), isnull(b.cus_abandon ,0), isnull( b.cus_sign ,0), isnull( b.cus_abandon,0) FROM ( SELECT number FROM master..spt_values P WHERE type = 'P' AND number BETWEEN 1 AND 12 ) a LEFT JOIN ( SELECT SUM(CASE WHEN cus_status = '未确认' THEN 1 ELSE 0 END) AS cus_none , SUM(CASE WHEN cus_status = '确认' THEN 1 ELSE 0 END) AS cus_confirm , SUM(CASE WHEN cus_status = '看房' THEN 1 ELSE 0 END) AS cus_abandon , SUM(CASE WHEN cus_status = '签约' THEN 1 ELSE 0 END) AS cus_sign , SUM(CASE WHEN cus_status = '已放弃' THEN 1 ELSE 0 END) AS cus_abandon , MONTH(cus_time) AS yue FROM min_customs WHERE DATEPART(yy, cus_time) = '2014' GROUP BY DATEPART(mm, cus_time) , MONTH(cus_time) ) b ON a.number = b.yue 试试
sum(case when cus_status='未确认' then 1 else 0 end) as cus_none,
sum(case when cus_status='确认' then 1 else 0 end) as cus_confirm ,
sum(case when cus_status='看房' then 1 else 0 end) as cus_abandon,
sum(case when cus_status='签约' then 1 else 0 end) as cus_sign ,
sum(case when cus_status='已放弃' then 1 else 0 end) as cus_abandon
from min_customs where datediff(year,cus_time,getdate())=0
将字段为null该为0,还有这个问题??帮忙看一下,谢谢!
select yuefen,cus_status,count(cus_status) as amount from (
select month(cus_time) as yuefen,cus_status from min_customs
) as a group by yuefen,cus_status order by yuefen如果不是你要的结果,你还是把结果贴出来,不然不知道你要的是什么。
sum(case when cus_status='未确认' then 1 else 0 end) as cus_none,
sum(case when cus_status='确认' then 1 else 0 end) as cus_confirm ,
sum(case when cus_status='看房' then 1 else 0 end) as cus_abandon,
sum(case when cus_status='签约' then 1 else 0 end) as cus_sign ,
sum(case when cus_status='已放弃' then 1 else 0 end) as cus_abandon,
from min_customs where datepart(yy,cus_time)='2014'
group by datepart(mm,cus_time)
但现在只是显示有数据的页面,我想显示的是所有月份的行,不管里面有没有数据!有个字段标志每一行是几月份!再次求指点~~~实现的我再会20分给大家的!谢谢
select
sum(case when cus_status='未确认' then 1 else 0 end) as cus_none,
sum(case when cus_status='确认' then 1 else 0 end) as cus_confirm ,
sum(case when cus_status='看房' then 1 else 0 end) as cus_abandon,
sum(case when cus_status='签约' then 1 else 0 end) as cus_sign ,
sum(case when cus_status='已放弃' then 1 else 0 end) as cus_abandon,month(cus_time) as yue
from min_customs where datepart(yy,cus_time)='2014'
group by datepart(mm,cus_time),month(cus_time) 月份已经出来了!只差显示的是所有月份的行,不管里面有没有数据!!帮帮忙,大家!!!
b.cus_none ,
b.cus_confirm ,
b.cus_abandon ,
b.cus_sign ,
b.cus_abandon
FROM ( SELECT number
FROM master..spt_values P
WHERE type = 'P'
AND number BETWEEN 1 AND 12
) a
LEFT JOIN ( SELECT SUM(CASE WHEN cus_status = '未确认' THEN 1
ELSE 0
END) AS cus_none ,
SUM(CASE WHEN cus_status = '确认' THEN 1
ELSE 0
END) AS cus_confirm ,
SUM(CASE WHEN cus_status = '看房' THEN 1
ELSE 0
END) AS cus_abandon ,
SUM(CASE WHEN cus_status = '签约' THEN 1
ELSE 0
END) AS cus_sign ,
SUM(CASE WHEN cus_status = '已放弃' THEN 1
ELSE 0
END) AS cus_abandon ,
MONTH(cus_time) AS yue
FROM min_customs
WHERE DATEPART(yy, cus_time) = '2014'
GROUP BY DATEPART(mm, cus_time) ,
MONTH(cus_time)
) b ON a.number = b.yue参考
但是nullif不管用,我想将字段为null改为数字0!!大家能帮我再看一下吗?谢谢!
isnull( b.cus_none ,0),
isnull(b.cus_confirm ,0),
isnull(b.cus_abandon ,0),
isnull( b.cus_sign ,0),
isnull( b.cus_abandon,0)
FROM ( SELECT number
FROM master..spt_values P
WHERE type = 'P'
AND number BETWEEN 1 AND 12
) a
LEFT JOIN ( SELECT SUM(CASE WHEN cus_status = '未确认' THEN 1
ELSE 0
END) AS cus_none ,
SUM(CASE WHEN cus_status = '确认' THEN 1
ELSE 0
END) AS cus_confirm ,
SUM(CASE WHEN cus_status = '看房' THEN 1
ELSE 0
END) AS cus_abandon ,
SUM(CASE WHEN cus_status = '签约' THEN 1
ELSE 0
END) AS cus_sign ,
SUM(CASE WHEN cus_status = '已放弃' THEN 1
ELSE 0
END) AS cus_abandon ,
MONTH(cus_time) AS yue
FROM min_customs
WHERE DATEPART(yy, cus_time) = '2014'
GROUP BY DATEPART(mm, cus_time) ,
MONTH(cus_time)
) b ON a.number = b.yue
试试