原来的sql语句:
select date_month,sum(number) from table1 group by date_month
这是按照月汇总,列出每个月的销量
结果:
date_month number
12 100
11 200
10 200
.
.
.
1 500我现在要的结果是:
date_month number
12 100
11 200
11以前 1500
就是说我想将date_month<11的汇总到一条
select date_month,sum(number) from table1 group by date_month
这是按照月汇总,列出每个月的销量
结果:
date_month number
12 100
11 200
10 200
.
.
.
1 500我现在要的结果是:
date_month number
12 100
11 200
11以前 1500
就是说我想将date_month<11的汇总到一条
from (Select case when date_month<11 then 11 else date_month end date_month,number from table1) x group by date_month
select date_month,sum(number)
from (Select case when date_month<11 then '11以前' else cast(date_month as varchar(10)) end date_month,
number from table1) x group by date_month
union all
select '11以前' date_month,sum(number) from table1 where date_month < 11 group by date_month
FROM (
SELECT CASE
WHEN date_month<11 THEN '11以前'
WHEN date_month=11 THEN '11'
ELSE '12'
END AS date_month
,SUM(number) number
FROM table1
GROUP BY
CASE
WHEN date_month<11 THEN '11以前'
WHEN date_month=11 THEN '11'
ELSE '12'
END
) t
ORDER BY
CHARINDEX(','+date_month+',' ,',12,11,11以前,')
union all
select date_month,sum(number) from table1 where date_month > 11 group by date_month
select '<11',sum(number) from table1 where date_month<11
union all
select date_month,sum(number) from table1 where date_month > 11 group by date_month
我的sql是2页A4以上的 用union 我的代码就太长了
为了简单说明我才举了个特例
union all select 11,200
union all select 11,100
union all select 12,200
union all select 10,200
union all select 9,200
union all select 8,200select case when date_month < 11 then '11以前' else convert(varchar,date_month) end date_month
,sum(number) number
from table1
group by case when date_month < 11 then '11以前' else convert(varchar,date_month) end
order by case (case when date_month < 11 then '11以前' else convert(varchar,date_month) end ) when '11以前' then 1 else 0 end
而3楼的完全可以使用,但是我觉得效率很差,我上万条数据,每条先去改主键的名字再汇总,效率很低
10楼的很准确 order稍微有点问题,应该说你的回答最符合我的需求