接这个问题的:http://topic.csdn.net/u/20110324/16/f1c10fb1-2370-44f6-906f-dbdc6cd34f26.htmlselect
convert(varchar(10),riqi,120) as riqi,
chehao,
sum(case sytle when'小车' then qian end) as 小车,
sum(case sytle when'直送' then qian end) as 直送
from
ceshi
group by
convert(varchar(10),riqi,120),chehao汇总出来的表 我想 写判断 大致内容是 根据直送判断 如果直送不等于空值,那么统计 一个月的值
小车判断 如果直送不等于空值 那么该条小车的信息不统计, 指统计直送为空值的值。然后 分别总和 经过判断的 直送 跟 小车的和求教各位 新人学习贴写上为什么这样写。好方便学习 不用再次麻烦大家。。
convert(varchar(10),riqi,120) as riqi,
chehao,
sum(case sytle when'小车' then qian end) as 小车,
sum(case sytle when'直送' then qian end) as 直送
from
ceshi
group by
convert(varchar(10),riqi,120),chehao汇总出来的表 我想 写判断 大致内容是 根据直送判断 如果直送不等于空值,那么统计 一个月的值
小车判断 如果直送不等于空值 那么该条小车的信息不统计, 指统计直送为空值的值。然后 分别总和 经过判断的 直送 跟 小车的和求教各位 新人学习贴写上为什么这样写。好方便学习 不用再次麻烦大家。。
declare @table table (riqi datetime,chehao int,sytle varchar(4),qian int)
insert into @table
select '2011-01-01 00:00:00',1548,'小车',50 union all
select '2011-01-01 00:00:00',1548,'小车',50 union all
select '2011-01-01 00:00:00',1549,'小车',50 union all
select '2011-01-01 00:00:00',1549,'小车',50 union all
select '2011-01-01 00:00:00',1548,'直送',100 union all
select '2011-02-01 00:00:00',1548,'直送',50 union all
select '2011-02-01 00:00:00',1548,'小车',50 union all
select '2011-02-01 00:00:00',1549,'小车',50 union all
select '2011-02-01 00:00:00',1549,'小车',50 union all
select '2011-03-01 00:00:00',1549,'小车',50 union all
select '2011-02-01 00:00:00',1548,'直送',100select
convert(varchar(10),riqi,120) as riqi,
chehao,
sum(case sytle when'小车' then qian end) as 小车,
sum(case sytle when'直送' then qian end) as 直送
from
@table
group by
convert(varchar(10),riqi,120),chehao
/*
riqi chehao 小车 直送
---------- ----------- ----------- -----------
2011-01-01 1548 100 100
2011-02-01 1548 50 150
2011-01-01 1549 100 NULL
2011-02-01 1549 100 NULL
2011-03-01 1549 50 NULL
*/根据这个结果,告诉我你想要什么样的结果?
我想要 统计直送的总和 根据日期统计 不根据车号要有判断
/*
riqi chehao 小车 直送
---------- ----------- ----------- -----------
2011-01-01 1548 100 100
2011-01-11 1548 50 150
2011-01-21 1549 100 NULL
2011-01-22 1549 100 NULL
2011-01-23 1549 51 NULL
*/
比如 1548 小车跟 直送 都有金额 这样就取 直送的值。
如果像 1549 直送没有金额 那么就直接统计 小车的值如果根据上述5条记录 正确的答案 会是 :1月份里(对你的数据日期改动了下好理解点。)
直送金额:250 小车金额:是 251
declare @table table (riqi datetime,chehao int,sytle varchar(4),qian int)
insert into @table
select '2011-01-01 00:00:00',1548,'小车',50 union all
select '2011-01-01 00:00:00',1548,'小车',50 union all
select '2011-01-01 00:00:00',1549,'小车',50 union all
select '2011-01-01 00:00:00',1549,'小车',50 union all
select '2011-01-01 00:00:00',1548,'直送',100 union all
select '2011-02-01 00:00:00',1548,'直送',50 union all
select '2011-02-01 00:00:00',1548,'小车',50 union all
select '2011-02-01 00:00:00',1549,'小车',50 union all
select '2011-02-01 00:00:00',1549,'小车',50 union all
select '2011-03-01 00:00:00',1549,'小车',50 union all
select '2011-02-01 00:00:00',1548,'直送',100;with maco as(
select
convert(varchar(10),riqi,120) as riqi,
chehao,
sum(case sytle when'小车' then qian end) as 小车,
sum(case sytle when'直送' then qian end) as 直送
from @table group by
convert(varchar(10),riqi,120),chehao
)select
convert(varchar(7),riqi,120) as riqi,sum(
case when 直送 is null then 小车 end
) as 小车,sum(isnull(直送,0)) as 直送
from maco
group by convert(varchar(7),riqi,120)
declare @table table (riqi datetime,chehao int,小车 int,直送 int)
insert into @table
select '2011-01-01',1548,100,100 union all
select '2011-01-11',1548,50,150 union all
select '2011-01-21',1549,100,null union all
select '2011-01-22',1549,100,null union all
select '2011-01-23',1549,51,nullselect
convert(varchar(7),riqi,120) as riqi,sum(
case when 直送 is null then 小车 end
) as 小车,sum(isnull(直送,0)) as 直送
from
@table
group by convert(varchar(7),riqi,120)
/*
riqi 小车 直送
------- ----------- -----------
2011-01 251 250
*/
insert into @table
select '2011-01-01 00:00:00',1548,'小车',50 union all
select '2011-01-01 00:00:00',1548,'小车',50 union all
select '2011-01-01 00:00:00',1549,'小车',50 union all
select '2011-01-01 00:00:00',1549,'小车',50 union all
select '2011-01-01 00:00:00',1548,'直送',100 union all
select '2011-02-01 00:00:00',1548,'直送',50 union all
select '2011-02-01 00:00:00',1548,'小车',50 union all
select '2011-02-01 00:00:00',1549,'小车',50 union all
select '2011-02-01 00:00:00',1549,'小车',50 union all
select '2011-03-01 00:00:00',1549,'小车',50 union all
select '2011-02-01 00:00:00',1548,'直送',100select
convert(varchar(10),riqi,120) as riqi,
chehao,
sum(case sytle when'小车' then qian end) as 小车,
sum(case sytle when'直送' then qian end) as 直送
from
@table
group by
convert(varchar(10),riqi,120),chehao这段的数据 然后select
convert(varchar(7),riqi,120) as riqi,sum(
case when 直送 is null then 小车 end
) as 小车,sum(isnull(直送,0)) as 直送
from
@table
group by convert(varchar(7),riqi,120)
才是 正解的木· ·你自己写一个表格 总和出 结果。不就错了么??
insert into @table
select '2011-01-01 00:00:00',1548,'小车',50 union all
select '2011-01-01 00:00:00',1548,'小车',50 union all
select '2011-01-01 00:00:00',1549,'小车',50 union all
select '2011-01-01 00:00:00',1549,'小车',50 union all
select '2011-01-01 00:00:00',1548,'直送',100 union all
select '2011-02-01 00:00:00',1548,'直送',50 union all
select '2011-02-01 00:00:00',1548,'小车',50 union all
select '2011-02-01 00:00:00',1549,'小车',50 union all
select '2011-02-01 00:00:00',1549,'小车',50 union all
select '2011-03-01 00:00:00',1549,'小车',50 union all
select '2011-02-01 00:00:00',1548,'直送',100select
convert(varchar(7),riqi,120) as riqi,sum(
case when 直送 is null then 小车 end) as 小车,sum(isnull(直送,0)) as 直送
from (
select
convert(varchar(10),riqi,120) as riqi,
chehao,
sum(case sytle when'小车' then qian end) as 小车,
sum(case sytle when'直送' then qian end) as 直送
from @table group by
convert(varchar(10),riqi,120),chehao
) maco group by convert(varchar(7),riqi,120)
select convert(varchar(7),riqi,120) as riqi,case when 直送 is not null then sum(直送) END 直送, case when 直送 is null then sum(小车) END 小车 from (
select convert(varchar(10),riqi,120) as riqi, chehao, sum(case sytle when'小车' then qian end) as 小车, sum(case sytle when'直送' then qian end) as 直送 from ceshi group by convert(varchar(7),riqi,120),chehao) a where 1=1 ) b
select riqi,sum(直送) 直送,sum(小车) 小车 from
select convert(varchar(7),riqi,120) as riqi,case when 直送 is not null then sum(直送) END 直送, case when 直送 is null then sum(小车) END 小车 from (
select convert(varchar(10),riqi,120) as riqi, chehao, sum(case sytle when'小车' then qian end) as 小车, sum(case sytle when'直送' then qian end) as 直送 from ceshi group by convert(varchar(7),riqi,120),chehao) a where 1=1 group by convert(varchar(7),riqi,120)) b group by riqi