不合适的字段类型设置 把个简单问题搞的如此复杂 暂当整形数处理drop table #tmp go drop table #tmp1 go declare @big bigint set @big = 1 select @big mi_time into #tmp delete from #tmp insert into #tmp select 201104161010 union all select 201105171230 union all select 201104181800 union all select 201104192350 union all select 201104190110 union all select 201104190700 union all select 201104180300 union all select 201104181100select * from #tmpselect mi_time, case (mi_time % 10000)/100 when 7 then '7点-9点' when 8 then '7点-9点' when 9 then '7点-9点' when 11 then '11点-13点' when 12 then '11点-13点' when 13 then '11点-13点' when 17 then '17点-19点' when 18 then '17点-19点' when 19 then '17点-19点' when 23 then '23点-5点' when 24 then '23点-5点' when 0 then '23点-5点' when 1 then '23点-5点' when 2 then '23点-5点' when 3 then '23点-5点' when 4 then '23点-5点' when 5 then '23点-5点' end Area into #tmp1 from #tmp where mi_time >= 201104150710 and mi_time <= 201104200700select * from #tmp1 select SUM([7点-9点]), SUM([11点-13点]), SUM([17点-19点]), SUM([23点-5点]) from (select Count(case Area when '7点-9点' then mi_time end) '7点-9点', Count(case Area when '11点-13点' then mi_time end) '11点-13点', Count(case Area when '17点-19点' then mi_time end) '17点-19点', Count(case Area when '23点-5点' then mi_time end) '23点-5点' from #tmp1 group by area) a
go
drop table #tmp1
go
declare @big bigint
set @big = 1
select @big mi_time into #tmp
delete from #tmp
insert into #tmp
select 201104161010
union all select 201105171230
union all select 201104181800
union all select 201104192350
union all select 201104190110
union all select 201104190700
union all select 201104180300
union all select 201104181100select * from #tmpselect mi_time, case (mi_time % 10000)/100
when 7 then '7点-9点'
when 8 then '7点-9点'
when 9 then '7点-9点'
when 11 then '11点-13点'
when 12 then '11点-13点'
when 13 then '11点-13点'
when 17 then '17点-19点'
when 18 then '17点-19点'
when 19 then '17点-19点'
when 23 then '23点-5点'
when 24 then '23点-5点'
when 0 then '23点-5点'
when 1 then '23点-5点'
when 2 then '23点-5点'
when 3 then '23点-5点'
when 4 then '23点-5点'
when 5 then '23点-5点'
end Area into #tmp1 from #tmp where mi_time >= 201104150710 and mi_time <= 201104200700select * from #tmp1
select SUM([7点-9点]), SUM([11点-13点]), SUM([17点-19点]), SUM([23点-5点]) from
(select
Count(case Area when '7点-9点' then mi_time end) '7点-9点',
Count(case Area when '11点-13点' then mi_time end) '11点-13点',
Count(case Area when '17点-19点' then mi_time end) '17点-19点',
Count(case Area when '23点-5点' then mi_time end) '23点-5点'
from #tmp1 group by area) a