有两个表,一个表存储时间段(TimeVar),另外一个表,销售数据(Sales),表结构及测试数据分别如下:
CREATE TABLE [TimeVar]
(
[Id] [int] NOT NULL,
[Time] [varchar](50) NOT NULL
)
insert into TimeVar(Id,Time)
select 1,'08:00' union all
select 2,'12:00' union all
select 3,'16:00' union all
select 4,'20:00' CREATE TABLE [Sales](
[SalesId] [int] NULL,
[SalesTime] [datetime] NULL,
[SalesMoney] [decimal](18, 3) NULL
)
INSERT INTO [Sales] ([SalesId],[SalesTime],[SalesMoney])
select 1,'2013-9-1 7:23',10 union all
select 1,'2013-9-1 9:10',20 union all
select 1,'2013-9-1 12:20',30 union all
select 1,'2013-9-1 14:30',40 union all
select 1,'2013-9-1 16:23',50 union all
select 1,'2013-9-1 22:10',60 union all
select 1,'2013-9-2 17:23',70 union all
select 1,'2013-9-3 18:13',80 union all
select 1,'2013-9-7 20:23',90 union all
select 1,'2013-9-7 22:15',100 union all
select 1,'2013-9-10 13:02',110 union all
select 1,'2013-9-10 14:20',120 union all
select 1,'2013-9-10 20:43',130 如果查询9月份的数据(存储过程有一个参数,传查询的月份),其中时间段及销售数据是从表里面取的,有可能会变化的。想得到下面的查询结果。多谢!!!
sql存储
CREATE TABLE [TimeVar]
(
[Id] [int] NOT NULL,
[Time] [varchar](50) NOT NULL
)
insert into TimeVar(Id,Time)
select 1,'08:00' union all
select 2,'12:00' union all
select 3,'16:00' union all
select 4,'20:00' CREATE TABLE [Sales](
[SalesId] [int] NULL,
[SalesTime] [datetime] NULL,
[SalesMoney] [decimal](18, 3) NULL
)
INSERT INTO [Sales] ([SalesId],[SalesTime],[SalesMoney])
select 1,'2013-9-1 7:23',10 union all
select 1,'2013-9-1 9:10',20 union all
select 1,'2013-9-1 12:20',30 union all
select 1,'2013-9-1 14:30',40 union all
select 1,'2013-9-1 16:23',50 union all
select 1,'2013-9-1 22:10',60 union all
select 1,'2013-9-2 17:23',70 union all
select 1,'2013-9-3 18:13',80 union all
select 1,'2013-9-7 20:23',90 union all
select 1,'2013-9-7 22:15',100 union all
select 1,'2013-9-10 13:02',110 union all
select 1,'2013-9-10 14:20',120 union all
select 1,'2013-9-10 20:43',130 如果查询9月份的数据(存储过程有一个参数,传查询的月份),其中时间段及销售数据是从表里面取的,有可能会变化的。想得到下面的查询结果。多谢!!!
sql存储
declare @startdate datetime
set @startdate='2013-09-01' -->外部传进来的参数select convert(varchar(10),a.日期,120) as 日期,isnull([08:00以前],0) as [08:00以前],isnull([08:00-12:00],0) as [08:00-12:00],
isnull([12:00-16:00],0) as [12:00-16:00],isnull([16:00-20:00],0) as [16:00-20:00],
isnull([20:00以后],0) as [20:00以后]
from (
select 日期=DATEADD(day,number,@startdate)
from (select number,@startdate as dt from master..spt_values where type='P' and number<=31)t
)A
left join (
select CONVERT(varchar(10),[SalesTime],120) as 日期,
SUM([08:00以前]) as [08:00以前],SUM([08:00-12:00]) as [08:00-12:00],
SUM([12:00-16:00]) as [12:00-16:00],SUM([16:00-20:00]) as [16:00-20:00],
SUM([20:00以后]) as [20:00以后]
from
(select [SalesTime],
[08:00以前]=case when CONVERT(varchar(5),[SalesTime],108)<'08:00' then [SalesMoney] end
,[08:00-12:00]=case when CONVERT(varchar(5),[SalesTime],108) between '08:00' and '12:00' then [SalesMoney] end
,[12:00-16:00]=case when CONVERT(varchar(5),[SalesTime],108) between '12:00' and '16:00' then [SalesMoney] end
,[16:00-20:00]=case when CONVERT(varchar(5),[SalesTime],108) between '16:00' and '20:00' then [SalesMoney] end
,[20:00以后]=case when CONVERT(varchar(5),[SalesTime],108)>'20:00' then [SalesMoney] end
from [Sales]
)t
group by CONVERT(varchar(10),[SalesTime],120)
)B on a.日期=B.日期
where convert(varchar(7),a.日期,120)=CONVERT(varchar(7),@startdate,120)
/*
日期 08:00以前 08:00-12:00 12:00-16:00 16:00-20:00 20:00以后
--------------------------------------------------------------------------
2013-09-01 10.000 20.000 70.000 50.000 60.000
2013-09-02 0.000 0.000 0.000 70.000 0.000
2013-09-03 0.000 0.000 0.000 80.000 0.000
2013-09-04 0.000 0.000 0.000 0.000 0.000
2013-09-05 0.000 0.000 0.000 0.000 0.000
2013-09-06 0.000 0.000 0.000 0.000 0.000
2013-09-07 0.000 0.000 0.000 0.000 190.000
2013-09-08 0.000 0.000 0.000 0.000 0.000
2013-09-09 0.000 0.000 0.000 0.000 0.000
2013-09-10 0.000 0.000 230.000 0.000 130.000
2013-09-11 0.000 0.000 0.000 0.000 0.000
2013-09-12 0.000 0.000 0.000 0.000 0.000
2013-09-13 0.000 0.000 0.000 0.000 0.000
2013-09-14 0.000 0.000 0.000 0.000 0.000
2013-09-15 0.000 0.000 0.000 0.000 0.000
2013-09-16 0.000 0.000 0.000 0.000 0.000
2013-09-17 0.000 0.000 0.000 0.000 0.000
2013-09-18 0.000 0.000 0.000 0.000 0.000
2013-09-19 0.000 0.000 0.000 0.000 0.000
2013-09-20 0.000 0.000 0.000 0.000 0.000
2013-09-21 0.000 0.000 0.000 0.000 0.000
2013-09-22 0.000 0.000 0.000 0.000 0.000
2013-09-23 0.000 0.000 0.000 0.000 0.000
2013-09-24 0.000 0.000 0.000 0.000 0.000
2013-09-25 0.000 0.000 0.000 0.000 0.000
2013-09-26 0.000 0.000 0.000 0.000 0.000
2013-09-27 0.000 0.000 0.000 0.000 0.000
2013-09-28 0.000 0.000 0.000 0.000 0.000
2013-09-29 0.000 0.000 0.000 0.000 0.000
2013-09-30 0.000 0.000 0.000 0.000 0.000
*/
@YearMonth varchar(7) -->外部传进来年月,格式:yyyy-MM
as
set nocount on declare @startdate datetime
set @startdate=@YearMonth+'-01' -->外部传进来的参数select convert(varchar(10),a.日期,120) as 日期,isnull([08:00以前],0) as [08:00以前],isnull([08:00-12:00],0) as [08:00-12:00],
isnull([12:00-16:00],0) as [12:00-16:00],isnull([16:00-20:00],0) as [16:00-20:00],
isnull([20:00以后],0) as [20:00以后]
from
(select 日期=DATEADD(day,number,@startdate) from master..spt_values where type='P' and number<=30)A
left join (
select CONVERT(varchar(10),[SalesTime],120) as 日期,
SUM([08:00以前]) as [08:00以前],SUM([08:00-12:00]) as [08:00-12:00],
SUM([12:00-16:00]) as [12:00-16:00],SUM([16:00-20:00]) as [16:00-20:00],
SUM([20:00以后]) as [20:00以后]
from
(select [SalesTime],
[08:00以前]=case when CONVERT(varchar(5),[SalesTime],108)<'08:00' then [SalesMoney] end
,[08:00-12:00]=case when CONVERT(varchar(5),[SalesTime],108) between '08:00' and '12:00' then [SalesMoney] end
,[12:00-16:00]=case when CONVERT(varchar(5),[SalesTime],108) between '12:00' and '16:00' then [SalesMoney] end
,[16:00-20:00]=case when CONVERT(varchar(5),[SalesTime],108) between '16:00' and '20:00' then [SalesMoney] end
,[20:00以后]=case when CONVERT(varchar(5),[SalesTime],108)>'20:00' then [SalesMoney] end
from [Sales]
)t
group by CONVERT(varchar(10),[SalesTime],120)
)B on a.日期=B.日期
where convert(varchar(7),a.日期,120)=CONVERT(varchar(7),@startdate,120)go--执行
exec proc_Sale '2013-09'
/*
日期 08:00以前 08:00-12:00 12:00-16:00 16:00-20:00 20:00以后
--------------------------------------------------------------------------
2013-09-01 10.000 20.000 70.000 50.000 60.000
2013-09-02 0.000 0.000 0.000 70.000 0.000
2013-09-03 0.000 0.000 0.000 80.000 0.000
2013-09-04 0.000 0.000 0.000 0.000 0.000
2013-09-05 0.000 0.000 0.000 0.000 0.000
2013-09-06 0.000 0.000 0.000 0.000 0.000
2013-09-07 0.000 0.000 0.000 0.000 190.000
2013-09-08 0.000 0.000 0.000 0.000 0.000
2013-09-09 0.000 0.000 0.000 0.000 0.000
2013-09-10 0.000 0.000 230.000 0.000 130.000
2013-09-11 0.000 0.000 0.000 0.000 0.000
2013-09-12 0.000 0.000 0.000 0.000 0.000
2013-09-13 0.000 0.000 0.000 0.000 0.000
2013-09-14 0.000 0.000 0.000 0.000 0.000
2013-09-15 0.000 0.000 0.000 0.000 0.000
2013-09-16 0.000 0.000 0.000 0.000 0.000
2013-09-17 0.000 0.000 0.000 0.000 0.000
2013-09-18 0.000 0.000 0.000 0.000 0.000
2013-09-19 0.000 0.000 0.000 0.000 0.000
2013-09-20 0.000 0.000 0.000 0.000 0.000
2013-09-21 0.000 0.000 0.000 0.000 0.000
2013-09-22 0.000 0.000 0.000 0.000 0.000
2013-09-23 0.000 0.000 0.000 0.000 0.000
2013-09-24 0.000 0.000 0.000 0.000 0.000
2013-09-25 0.000 0.000 0.000 0.000 0.000
2013-09-26 0.000 0.000 0.000 0.000 0.000
2013-09-27 0.000 0.000 0.000 0.000 0.000
2013-09-28 0.000 0.000 0.000 0.000 0.000
2013-09-29 0.000 0.000 0.000 0.000 0.000
2013-09-30 0.000 0.000 0.000 0.000 0.000
*/
动态的也可,那建议你修改时间分段那个表的结构:
CREATE TABLE [TimeVar]
(
[Id] [int] NOT NULL,
[StartTime] [varchar](8) NOT NULL, --->8位就可以了
[EndTime] [varchar](8) NOT NULL
) 这样之后可以通过动态sql语句生成统计.
参考这里的例子一:
http://blog.csdn.net/hdhai9451/article/details/5026933你看是否可以结合我上面的代码,你自己写一个.我家里是sql2000不太好弄
select '00:00' tm0, min(time) tm1, MIN(time)+'以前' name from TimeVar
union
select time tm0
,(select min(Time) from TimeVar t3 where t3.Time>t1.Time) tm1
,time+(select isnull('到'+min(Time),'以后') from TimeVar t3 where t3.Time>t1.Time) name
from TimeVar t1declare @sql nvarchar(max)
set @sql = '
with tms as (
select ''00:00'' tm0, min(time) tm1, MIN(time)+''以前'' name from TimeVar
union
select time tm0
,(select min(Time) from TimeVar t3 where t3.Time>t1.Time) tm1
,time+(select isnull(''到''+min(Time),''以后'') from TimeVar t3 where t3.Time>t1.Time) name
from TimeVar t1
)
select day'
select @sql = @sql + ',SUM(case tm when '''+Convert(varchar(100),tm0)+''' then SalesMoney end) ['+Convert(varchar(100),name)+']'
from (select distinct top 1000 tm0,name from @tms order by 1) as a
select @sql = @sql+' from (
select DATEPART(day, salestime) day
, SalesMoney
,(select max(tm0) tm from tms where tm0<=RIGHT(convert(varchar(16), s.salestime, 120), 5) ) tm
from Sales s
) ttt group by day order by day'exec sp_executesql @sql结果:
day 08:00以前 08:00到12:00 12:00到16:00 16:00到20:00 20:00以后
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 10.000 20.000 70.000 50.000 60.000
2 NULL NULL NULL 70.000 NULL
3 NULL NULL NULL 80.000 NULL
7 NULL NULL NULL NULL 190.000
10 NULL NULL 230.000 NULL
首先对于这样每天都会增加数据的销售表SaleMst,SaleDtl,表中数据量会不断增加
那首先做的是将从表里面将某个销售日期区间的相关数据提出来,如#TEMP
这样做很大部分提高性能,因为如果直接去查表里,然后通过各种转换来得到时间段的手动都会读整张表,会很慢
接着有做一个时段表,如:#TimeRange,然后JOIN结果集#Temp,再取时间段的销售情况*/
DECLARE @StartSaleDate CHAR(8),@EndSaleDate CHAR(8)
CREATE TABLE #TimeRange(TimeRange VARCHAR(2))
DECLARE @TimeCnt int=9
INSERT INTO #TimeRange(TimeRange) VALUES('00')
WHILE(@TimeCnt<24)
BEGIN
INSERT INTO #TimeRange(TimeRange) SELECT (REPLICATE('0',2-LEN(CAST(@TimeCnt AS VARCHAR(2))))) + CAST(@TimeCnt AS VARCHAR(2))
SET @TimeCnt=@TimeCnt+1
END
CREATE TABLE #TEMP
(
SaleNo CHAR(15)
,BrandCode VARCHAR(4)
,ShopCode CHAR(4)
,SaleMode CHAR(1)
,SaleQty INT
,EstimateSaleAmtForConsumer DECIMAL(19,2)
,DiscountAmt DECIMAL(19,2)
,SaleAmt DECIMAL(19,2)
,InDateTime DATETIME
)
INSERT INTO #TEMP
SELECT SM.SaleNo
,SM.BrandCode
,SM.ShopCode
,SM.SaleMode
,SM.SaleQty
,SM.EstimateSaleAmtForConsumer
,SM.DiscountAmt
,CASE WHEN SM.SaleQty < 0 AND SM.SaleAmt > 0 THEN SM.SaleAmt * -1 ELSE SM.SaleAmt END AS SaleAmt
,SM.InDateTime
FROM SaleMst AS SM WITH(NOLOCK)
--JOIN #TempShop AS TS ON SM.BrandCode=TS.BrandCode AND SM.ShopCode=TS.ShopCode
WHERE SM.Dates BETWEEN @StartSaleDate AND @EndSaleDate
SELECT TR.TimeRange + ':00' + '~' +
CASE WHEN TR.TimeRange = '00' THEN '09:00'
ELSE CONVERT(VARCHAR(2),(CONVERT(INT,TR.TimeRange) + 1)) + ':00' END AS TimeRange
,COUNT(SM.SaleNo) AS ReceiptQty
,SUM( CASE WHEN SM.SaleMode='S' THEN SM.SaleQty
ELSE 0
END ) AS SaleQty
,SUM(CASE WHEN SM.SaleMode IN('R','A','N') THEN SM.SaleQty
ELSE 0
END ) AS ReturnQty
,ISNULL(SUM(SM.EstimateSaleAmtForConsumer),0.00) AS EstimateSaleAmt
,ISNULL(SUM(SM.DiscountAmt),0.00) AS DiscountAmt
,ISNULL(SUM(SM.SaleAmt),0.00) AS DecisionPriceSaleAmt
FROM #TimeRange AS TR
LEFT JOIN #TEMP AS SM ON TR.TimeRange
= CASE WHEN DATEPART(HOUR,SM.InDateTime) < 9 THEN '00'
ELSE (REPLICATE('0',2-LEN(CAST(DATEPART(HOUR,SM.InDateTime) AS VARCHAR(2))))) + CAST(DATEPART(HOUR,SM.InDateTime) AS VARCHAR(2))
END
GROUP BY TR.TimeRange
ORDER BY TR.TimeRange
CREATE TABLE [TimeVar](
[Id] [int] NOT NULL,
[TimeStart] [time](0) NOT NULL,
[TimeEnd] [time](0) NULL,
[name] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_TimeVar] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
Id TimeStart TimeEnd name
----------- ---------------- ---------------- --------------------
1 00:00:00 08:00:00 08:00以前
2 08:00:00 12:00:00 08:00到12:00
3 12:00:00 16:00:00 12:00到16:00
4 16:00:00 20:00:00 16:00到20:00
5 20:00:00 NULL 20:00以后
前两行测试用,如果传参数应去掉
declare @tm0 date = '2013-09-01'
declare @tm1 date = '2013-10-01'
declare @sql nvarchar(max) = 'select day'
select @sql = @sql + ',SUM(case id when '+Convert(varchar(5),id)+' then SalesMoney end) ['+name+']'
from timevar a order by id
set @sql = @sql+' from (
select DATEPART(day, salestime) day,SalesMoney,v.id
from Sales s
join timevar v
on convert(time,s.salestime)>=v.timestart and (convert(time,s.salestime)<v.timeend or v.timeend is null)
where s.salestime >= @tm0 and s.salestime < @tm1
) ttt group by day order by day'
exec sp_executesql @sql,N'@tm0 datetime,@tm1 datetime',@tm0,@tm1
day 08:00以前 08:00到12:00 12:00到16:00 16:00到20:00 20:00以后
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 10.000 20.000 70.000 50.000 60.000
2 NULL NULL NULL 70.000 NULL
3 NULL NULL NULL 80.000 NULL
7 NULL NULL NULL NULL 190.000
10 NULL NULL 230.000 NULL 130.000
goCREATE TABLE [TimeVar]
(
[Id] [int] NOT NULL,
[StartTime] [varchar](8) NOT NULL, --->8位就可以了
[EndTime] [varchar](8) NOT NULL
)
insert into TimeVar(Id,StartTime,EndTime)
select 1,'00:00:00','08:00:00' union all
select 2,'08:00:00','12:00:00' union all
select 3,'12:00:00','16:00:00' union all
select 4,'16:00:00','20:00:00' union all
select 5,'20:00:00','23:59:59'
if exists(select 1 from sysobjects where id=object_id('Sales')) drop table Sales
goCREATE TABLE [Sales](
[SalesId] [int] NULL,
[SalesTime] [datetime] NULL,
[SalesMoney] [decimal](18, 3) NULL
)
INSERT INTO [Sales] ([SalesId],[SalesTime],[SalesMoney])
select 1,'2013-9-1 7:23',10 union all
select 1,'2013-9-1 9:10',20 union all
select 1,'2013-9-1 12:20',30 union all
select 1,'2013-9-1 14:30',40 union all
select 1,'2013-9-1 16:23',50 union all
select 1,'2013-9-1 22:10',60 union all
select 1,'2013-9-2 17:23',70 union all
select 1,'2013-9-3 18:13',80 union all
select 1,'2013-9-7 20:23',90 union all
select 1,'2013-9-7 22:15',100 union all
select 1,'2013-9-10 13:02',110 union all
select 1,'2013-9-10 14:20',120 union all
select 1,'2013-9-10 20:43',130
--简化,并改为存储过程create proc proc_Sale
@YearMonth varchar(7) -->外部传进来年月,格式:yyyy-MM
as
set nocount on declare @startdate varchar(10)
set @startdate=@YearMonth+'-01' -->外部传进来的参数declare @SQL varchar(8000)
declare @sql1 varchar(2000),@sql2 varchar(1000),@sql3 varchar(1000)
select @sql1='',@sql2='',@sql3=''
select @sql1=@sql1+',['+StartTime+' - '+EndTime+']=case when convert(varchar(19),[SalesTime],120)>=convert(varchar(11),[SalesTime],120)+'''+StartTime
+''' and convert(varchar(19),[SalesTime],120)<convert(varchar(11),[SalesTime],120)+'''+EndTime+''' then [SalesMoney] end',
@sql2=@sql2+',sum(['+StartTime+' - '+EndTime+']) as ['+StartTime+' - '+EndTime+']',
@sql3=@sql3+',isnull(['+StartTime+' - '+EndTime+'],0) as ['+StartTime+' - '+EndTime+']'
from TimeVarset @SQL='select convert(varchar(10),a.日期,120) as 日期'+@sql3+
' from
(select 日期=DATEADD(day,number,'''+@startdate+''') from master..spt_values where type=''P'' and number<=30)A
left join (
select CONVERT(varchar(10),[SalesTime],120) as 日期'+@sql2+
' from
(select [SalesTime]'+@sql1+
' from [Sales]
)t
group by CONVERT(varchar(10),[SalesTime],120)
)B on a.日期=B.日期
where convert(varchar(7),a.日期,120)=CONVERT(varchar(7),'''+@startdate+''',120)'EXEC (@SQL)GO--执行
exec proc_Sale '2013-09'-- DROP PROC proc_Sale/*
[00:00:00 - 08:00:00] [08:00:00 - 12:00:00] [12:00:00 - 16:00:00] [16:00:00 - 20:00:00] [20:00:00 - 23:59:59]
-------------------------------------------------------------------------------------------------------------------------
2013-09-01 10.000 20.000 70.000 50.000 60.000
2013-09-02 .000 .000 .000 70.000 .000
2013-09-03 .000 .000 .000 80.000 .000
2013-09-04 .000 .000 .000 .000 .000
2013-09-05 .000 .000 .000 .000 .000
2013-09-06 .000 .000 .000 .000 .000
2013-09-07 .000 .000 .000 .000 190.000
2013-09-08 .000 .000 .000 .000 .000
2013-09-09 .000 .000 .000 .000 .000
2013-09-10 .000 .000 230.000 .000 130.000
2013-09-11 .000 .000 .000 .000 .000
2013-09-12 .000 .000 .000 .000 .000
2013-09-13 .000 .000 .000 .000 .000
2013-09-14 .000 .000 .000 .000 .000
2013-09-15 .000 .000 .000 .000 .000
2013-09-16 .000 .000 .000 .000 .000
2013-09-17 .000 .000 .000 .000 .000
2013-09-18 .000 .000 .000 .000 .000
2013-09-19 .000 .000 .000 .000 .000
2013-09-20 .000 .000 .000 .000 .000
2013-09-21 .000 .000 .000 .000 .000
2013-09-22 .000 .000 .000 .000 .000
2013-09-23 .000 .000 .000 .000 .000
2013-09-24 .000 .000 .000 .000 .000
2013-09-25 .000 .000 .000 .000 .000
2013-09-26 .000 .000 .000 .000 .000
2013-09-27 .000 .000 .000 .000 .000
2013-09-28 .000 .000 .000 .000 .000
2013-09-29 .000 .000 .000 .000 .000
2013-09-30 .000 .000 .000 .000 .000
*/
这是sql server2000做出来的
if object_id('xsfb') is not null drop proc xsfb
go
create proc xsfb
@yue int
as
declare @t table(yue varchar(30))
declare @nian varchar(4)
set @nian=convert(varchar(4),getdate(),23)
declare @monthstart varchar(30)
set @monthstart=@nian+'-0'+cast(@yue as varchar(4))+'-01'
declare @monthtemp varchar(30)
set @monthtemp=@nian+'-0'+cast(@yue+1 as varchar(4))+'-'+'01'
declare @monthend varchar(30)
set @monthend=convert(varchar(10),dateadd(dd,-1,@monthtemp),23)
while(@monthstart<=@monthend)
begin
insert into @t select @monthstart
set @monthstart=convert(varchar(10),dateadd(dd,1,@monthstart),23)
end
select [日期]=cast(day(a.yue) as varchar)+'号',[8点以前]=isnull(b.[8点以前],0),
[08点到12点]=isnull(b.[08点到12点],0),
[12点到16点]=isnull(b.[12点到16点],0),
[16点到20点]=isnull(b.[16点到20点],0),
[20:00以后]=isnull(b.[20:00以后],0)
from @t a left join (select [rq]=convert(varchar(10),salestime,23),
[8点以前]=max(case when convert(varchar(5),salestime,24)<'08:00' then salesmoney else 0 end),
[08点到12点]=max(case when convert(varchar(5),salestime,24)>='08:00' and convert(varchar(5),salestime,24)<'12:00' then salesmoney else 0 end),
[12点到16点]=max(case when convert(varchar(5),salestime,24)>='12:00' and convert(varchar(5),salestime,24)<'16:00' then salesmoney else 0 end),
[16点到20点]=max(case when convert(varchar(5),salestime,24)>='16:00' and convert(varchar(5),salestime,24)<'20:00' then salesmoney else 0 end),
[20:00以后]=max(case when convert(varchar(5),salestime,24)>='20:00' then salesmoney else 0 end)
from sales group by convert(varchar(10),salestime,23)) b on a.yue=b.rq