这样子的分时段按日统计,sql语句要怎么写

Question

有个销售数据表简化表示是这样的
time(销售时间)           je(销售金额)
2008-10-15 08:04:29.670  8.8000
2007-11-28 08:11:51.733  66.0000
2008-01-16 08:14:31.187  16.5000
2008-01-17 08:09:10.000  3.9200
2008-01-18 08:04:28.013  17.0000
2008-01-19 08:03:40.467  3.8000
2008-01-20 08:43:06.543  1.0000
2008-01-21 08:01:00.793  12.8000
2008-01-22 08:11:17.293  11.0000
2008-01-23 08:02:35.090  9.0000
销售人员是两个班(早班00:00-14:59,晚班13:00-23:59),要按日统计这个月两个班每天的销售金额,sql语句要怎么写啊,请哪位老大,指教一下

Answer 1

select convert(varchar(10),time,120) [日期] , sum(je) je from tb group by convert(varchar(10),time,120)

Answer 2

SELECT 
CONVERT(VARCHAR(10),TIME,120)AS TIME
SUM(CASE WHEN DATAPART(HH,TIME)>=0 
AND DATAPART(HH,TIME)<13 THEN JE ELSE 0 END)AS NUM,
SUM(CASE WHEN DATAPART(HH,TIME)>=13 AND DATAPART(HH,TIME)<24 THEN JE ELSE 0 END)AS NUM1
FROM TB GROUP BY CONVERT(VARCHAR(10),TIME,120)

Answer 3

create table tb(time datetime, je decimal(18,4))
insert into tb values('2008-10-15 08:04:29.670' , 8.8000) 
insert into tb values('2007-11-28 08:11:51.733' , 66.0000) 
insert into tb values('2008-01-16 08:14:31.187' , 16.5000) 
insert into tb values('2008-01-17 08:09:10.000' , 3.9200) 
insert into tb values('2008-01-18 08:04:28.013' , 17.0000) 
insert into tb values('2008-01-19 08:03:40.467' , 3.8000 )
insert into tb values('2008-01-20 08:43:06.543' , 1.0000 )
insert into tb values('2008-01-21 08:01:00.793' , 12.8000 )
insert into tb values('2008-01-22 08:11:17.293' , 11.0000 )
insert into tb values('2008-01-23 08:02:35.090' , 9.0000 )
goselect convert(varchar(10),time,120) [日期] , sum(je) je from tb group by convert(varchar(10),time,120) order by [日期]drop table tb/*
日期         je                                       
---------- ---------------------------------------- 
2007-11-28 66.0000
2008-01-16 16.5000
2008-01-17 3.9200
2008-01-18 17.0000
2008-01-19 3.8000
2008-01-20 1.0000
2008-01-21 12.8000
2008-01-22 11.0000
2008-01-23 9.0000
2008-10-15 8.8000（所影响的行数为 10 行）
*/

Answer 4

SELECT TIME,SUM(NUM)NUM,SUM(NUM1) NUM1FROM 
(
SELECT 
CONVERT(VARCHAR(10),TIME,120)AS TIME,SUM(CASE WHEN DATAPART(HH,TIME)>=0 
AND DATAPART(HH,TIME)<13 THEN JE ELSE 0 END)AS NUM,SUM(CASE WHEN DATAPART(HH,TIME)>=13 AND DATAPART(HH,TIME)<24 THEN JE ELSE 0 END)AS NUM1FROM TB 
)AS T
GROUP BY TIME

Answer 5

select convert(char(8),time,120),type,SUM(je) * from 
(select *,case when convert(char(5),time,108) between '00:00' and '12:59' then '早'else '晚' end as type from 表 ) a
group by convert(char(8),time,120),type 

Answer 6

--> 测试数据：@tb
declare @tb table([time] datetime,[je] numeric(6,4))
insert @tb
select '2008-10-15 08:04:29.670',8.8000 union all
select '2007-11-28 08:11:51.733',66.0000 union all
select '2008-01-16 08:14:31.187',16.5000 union all
select '2008-01-17 08:09:10.000',3.9200 union all
select '2008-01-18 08:04:28.013',17.0000 union all
select '2008-01-19 08:03:40.467',3.8000 union all
select '2008-01-20 08:43:06.543',1.0000 union all
select '2008-01-21 08:01:00.793',12.8000 union all
select '2008-01-22 08:11:17.293',11.0000 union all
select '2008-01-23 08:02:35.090',9.0000
 select [销售时间]=convert(varchar(10),time,120),
[00:00-14:59]=sum(case when convert(varchar(10),time,108) between '00:00:00' and '14:59:59' then je else 0 end),
[13:00-23:59]=sum(case when convert(varchar(10),time,108) between '13:00:00' and '23:59:59' then je else 0 end)
 from @tb group by convert(varchar(10),time,120)/*
销售时间       00:00-14:59                             13:00-23:59
---------- --------------------------------------- ---------------------------------------
2007-11-28 66.0000                                 0.0000
2008-01-16 16.5000                                 0.0000
2008-01-17 3.9200                                  0.0000
2008-01-18 17.0000                                 0.0000
2008-01-19 3.8000                                  0.0000
2008-01-20 1.0000                                  0.0000
2008-01-21 12.8000                                 0.0000
2008-01-22 11.0000                                 0.0000
2008-01-23 9.0000                                  0.0000
2008-10-15 8.8000                                  0.0000
*/

Answer 7

--你的班次怎么会出现重复时间?create table tb(time datetime, je decimal(18,4))
insert into tb values('2008-10-15 08:04:29.670' , 8.8000) 
insert into tb values('2007-11-28 08:11:51.733' , 66.0000) 
insert into tb values('2008-01-16 08:14:31.187' , 16.5000) 
insert into tb values('2008-01-17 08:09:10.000' , 3.9200) 
insert into tb values('2008-01-18 08:04:28.013' , 17.0000) 
insert into tb values('2008-01-19 08:03:40.467' , 3.8000 )
insert into tb values('2008-01-20 08:43:06.543' , 1.0000 )
insert into tb values('2008-01-21 08:01:00.793' , 12.8000 )
insert into tb values('2008-01-22 08:11:17.293' , 11.0000 )
insert into tb values('2008-01-23 08:02:35.090' , 9.0000 )
goselect banci , sum(je) je from
(
  select case when datepart(hh,time) between 0 and 11 then '00:00-11:59' else '12:00-23:59' end banci, je from tb
) m
group by bancidrop table tb/*
banci       je                                       
----------- ---------------------------------------- 
00:00-11:59 149.8200（所影响的行数为 1 行）
*/

Answer 8

create table tb(time datetime, je decimal(18,4))
insert into tb values('2008-10-15 08:04:29.670' , 8.8000) 
insert into tb values('2007-11-28 08:11:51.733' , 66.0000) 
insert into tb values('2008-01-16 08:14:31.187' , 16.5000) 
insert into tb values('2008-01-17 08:09:10.000' , 3.9200) 
insert into tb values('2008-01-18 08:04:28.013' , 17.0000) 
insert into tb values('2008-01-19 08:03:40.467' , 3.8000 )
insert into tb values('2008-01-20 08:43:06.543' , 1.0000 )
insert into tb values('2008-01-21 15:01:00.793' , 12.8000 )
insert into tb values('2008-01-22 08:11:17.293' , 11.0000 )
insert into tb values('2008-01-23 21:02:35.090' , 9.0000 )
go--DROP TABLE TB
SELECT TIME,SUM(NUM)NUM,SUM(NUM1) NUM1FROM 
(
SELECT 
CONVERT(VARCHAR(10),TIME,120)AS TIME,SUM(CASE WHEN DATEPART(HH,TIME)>=0 
AND DATEPART(HH,TIME)<13 THEN JE ELSE 0 END)AS NUM,SUM(CASE WHEN DATEPART(HH,TIME)>=13 AND DATEPART(HH,TIME)<24 THEN JE ELSE 0 END)AS NUM1FROM TB GROUP BY CONVERT(VARCHAR(10),TIME,120)
)AS T
GROUP BY TIME
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）TIME       NUM                                      NUM1                                     
---------- ---------------------------------------- ---------------------------------------- 
2007-11-28 66.0000                                  .0000
2008-01-16 16.5000                                  .0000
2008-01-17 3.9200                                   .0000
2008-01-18 17.0000                                  .0000
2008-01-19 3.8000                                   .0000
2008-01-20 1.0000                                   .0000
2008-01-21 .0000                                    12.8000
2008-01-22 11.0000                                  .0000
2008-01-23 .0000                                    9.0000
2008-10-15 8.8000                                   .0000（所影响的行数为 10 行）

Answer 9

if object_id('tb') is not null
drop table tb
go
create table tb([time] datetime,je decimal(10,4))
go
insert into tb select 
'2008-10-15 08:04:29.670',8.8000 union all select
'2007-11-28 08:11:51.733',66.0000 union all select
'2008-01-16 08:14:31.187',16.5000 union all select
'2008-01-17 08:09:10.000',3.9200 union all select
'2008-01-18 08:04:28.013',17.0000 union all select
'2008-01-19 08:03:40.467',3.8000 union all select
'2008-01-20 08:43:06.543',1.0000 union all select
'2008-01-21 08:01:00.793',12.8000 union all select
'2008-01-22 08:11:17.293',11.0000 union all select
'2008-01-23 14:02:35.090',9.0000 
goSelect  Convert(varchar(10),[time],102),case when DATEPART(hh,[time])<13 then '早班' else '晚班' end, Sum(je)
from tb
Group by Convert(varchar(10),[time],102),case when DATEPART(hh,[time])<13 then '早班' else '晚班' end/*---------- ---- ---------------------------------------
2008.01.23 晚班   9.0000
2007.11.28 早班   66.0000
2008.01.16 早班   16.5000
2008.01.17 早班   3.9200
2008.01.18 早班   17.0000
2008.01.19 早班   3.8000
2008.01.20 早班   1.0000
2008.01.21 早班   12.8000
2008.01.22 早班   11.0000
2008.10.15 早班   8.8000(10 行受影响)
*/

Answer 10

create table tb(time datetime, je decimal(18,4))
insert into tb values('2008-10-15 08:04:29.670' , 8.8000) 
insert into tb values('2008-10-15 21:04:29.670' , 10.8000) 
insert into tb values('2007-11-28 08:11:51.733' , 66.0000) 
insert into tb values('2008-01-16 08:14:31.187' , 16.5000) 
insert into tb values('2008-01-17 08:09:10.000' , 3.9200) 
insert into tb values('2008-01-18 08:04:28.013' , 17.0000) 
insert into tb values('2008-01-19 08:03:40.467' , 3.8000 )
insert into tb values('2008-01-20 08:43:06.543' , 1.0000 )
insert into tb values('2008-01-21 15:01:00.793' , 12.8000 )
insert into tb values('2008-01-22 08:11:17.293' , 11.0000 )
insert into tb values('2008-01-23 21:02:35.090' , 9.0000 )
go--DROP TABLE TB
SELECT TIME,SUM(NUM)NUM,SUM(NUM1) NUM1FROM 
(
SELECT 
CONVERT(VARCHAR(10),TIME,120)AS TIME,SUM(CASE WHEN DATEPART(HH,TIME)>=0 
AND DATEPART(HH,TIME)<13 THEN JE ELSE 0 END)AS NUM,SUM(CASE WHEN DATEPART(HH,TIME)>=13 AND DATEPART(HH,TIME)<24 THEN JE ELSE 0 END)AS NUM1FROM TB GROUP BY CONVERT(VARCHAR(10),TIME,120)
)AS T
GROUP BY TIME
TIME       NUM                                      NUM1                                     
---------- ---------------------------------------- ---------------------------------------- 
2007-11-28 66.0000                                  .0000
2008-01-16 16.5000                                  .0000
2008-01-17 3.9200                                   .0000
2008-01-18 17.0000                                  .0000
2008-01-19 3.8000                                   .0000
2008-01-20 1.0000                                   .0000
2008-01-21 .0000                                    12.8000
2008-01-22 11.0000                                  .0000
2008-01-23 .0000                                    9.0000
2008-10-15 8.8000                                   10.8000（所影响的行数为 10 行）

Answer 11

create table tb(time datetime, je decimal(18,4))
insert into tb values('2008-10-15 08:04:29.670' , 8.8000) 
insert into tb values('2007-11-28 08:11:51.733' , 66.0000) 
insert into tb values('2008-01-16 08:14:31.187' , 16.5000) 
insert into tb values('2008-01-17 08:09:10.000' , 3.9200) 
insert into tb values('2008-01-18 08:04:28.013' , 17.0000) 
insert into tb values('2008-01-19 08:03:40.467' , 3.8000 )
insert into tb values('2008-01-20 08:43:06.543' , 1.0000 )
insert into tb values('2008-01-21 08:01:00.793' , 12.8000 )
insert into tb values('2008-01-22 08:11:17.293' , 11.0000 )
insert into tb values('2008-01-23 08:02:35.090' , 9.0000 )
goselect banci , sum(je) je from
(
  select case when datepart(hh,time) between 0 and 14 then '00:00-14:59' else '15:00-23:59' end banci, je from tb
) m
group by bancidrop table tb/*
banci       je                                       
----------- ---------------------------------------- 
00:00-14:59 149.8200（所影响的行数为 1 行）
*/

Answer 12

create table tb(time datetime, je decimal(18,4))
insert into tb values('2008-10-15 08:04:29.670' , 8.8000) 
insert into tb values('2008-10-15 21:04:29.670' , 10.8000) 
insert into tb values('2007-11-28 08:11:51.733' , 66.0000) 
insert into tb values('2008-01-16 08:14:31.187' , 16.5000) 
insert into tb values('2008-01-17 08:09:10.000' , 3.9200) 
insert into tb values('2008-01-18 08:04:28.013' , 17.0000) 
insert into tb values('2008-01-19 08:03:40.467' , 3.8000 )
insert into tb values('2008-01-20 08:43:06.543' , 1.0000 )
insert into tb values('2008-01-21 15:01:00.793' , 12.8000 )
insert into tb values('2008-01-22 08:11:17.293' , 11.0000 )
insert into tb values('2008-01-23 21:02:35.090' , 9.0000 )
go--DROP TABLE TB
SELECT TIME,SUM(NUM)NUM,SUM(NUM1) NUM1FROM 
(
SELECT 
CONVERT(VARCHAR(10),TIME,120)AS TIME,SUM(CASE WHEN DATEPART(HH,TIME)>=0 
AND DATEPART(HH,TIME)<15 THEN JE ELSE 0 END)AS NUM,SUM(CASE WHEN DATEPART(HH,TIME)>=15 AND DATEPART(HH,TIME)<24 THEN JE ELSE 0 END)AS NUM1FROM TB GROUP BY CONVERT(VARCHAR(10),TIME,120)
)AS T
GROUP BY TIME
TIME       NUM                                      NUM1                                     
---------- ---------------------------------------- ---------------------------------------- 
2007-11-28 66.0000                                  .0000
2008-01-16 16.5000                                  .0000
2008-01-17 3.9200                                   .0000
2008-01-18 17.0000                                  .0000
2008-01-19 3.8000                                   .0000
2008-01-20 1.0000                                   .0000
2008-01-21 .0000                                    12.8000
2008-01-22 11.0000                                  .0000
2008-01-23 .0000                                    9.0000
2008-10-15 8.8000                                   10.8000（所影响的行数为 10 行）

Answer 13

to dawugui
没有班次这个字段啊,学习实践一下,马上结贴
弱弱的问一下,这个120是什么意思啊,查帮助文档没查到
CONVERT(VARCHAR(10),TIME,120)

Answer 14

班次是我根据你的内容做出的虚拟字段.CONVERT(VARCHAR(10),TIME,120)
CAST 和 CONVERT
将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。语法
使用 CAST：CAST ( expression AS data_type ) 使用 CONVERT：CONVERT (data_type[(length)], expression [, style])参数
expression是任何有效的 Microsoft® SQL Server™ 表达式。有关更多信息，请参见表达式。 data_type目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息，请参见数据类型。 lengthnchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。 style日期格式样式，借以将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）；或者字符串格式样式，借以将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。在表中，左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100，可获得包括世纪数位的四位年份 (yyyy)。不带世纪数位 (yy) 带世纪数位 (yyyy) 
标准 
输入/输出** 
- 0 或 100 (*)  默认值 mon dd yyyy hh:miAM（或 PM） 
1 101 美国 mm/dd/yyyy 
2 102 ANSI yy.mm.dd 
3 103 英国/法国 dd/mm/yy 
4 104 德国 dd.mm.yy 
5 105 意大利 dd-mm-yy 
6 106 - dd mon yy 
7 107 - mon dd, yy 
8 108 - hh:mm:ss 
- 9 或 109 (*)  默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM） 
10 110 美国 mm-dd-yy 
11 111 日本 yy/mm/dd 
12 112 ISO yymmdd 
- 13 或 113 (*)  欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h) 
14 114 - hh:mi:ss:mmm(24h) 
- 20 或 120 (*)  ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff] 
- 21 或 121 (*)  ODBC 规范（带毫秒） yyyy-mm-dd hh:mm:ss[.fff] 
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm（不含空格） 
- 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM 
- 131* 科威特 dd/mm/yy hh:mi:ss:mmmAM 

Answer 15

把日期转换成YYYY-MM-DD格式
如果是112就是YYYYMMDD格式

Answer 16

--哦,你是要每天每个班次的?create table tb(time datetime, je decimal(18,4))
insert into tb values('2008-10-15 08:04:29.670' , 8.8000) 
insert into tb values('2007-11-28 08:11:51.733' , 66.0000) 
insert into tb values('2008-01-16 08:14:31.187' , 16.5000) 
insert into tb values('2008-01-17 08:09:10.000' , 3.9200) 
insert into tb values('2008-01-18 08:04:28.013' , 17.0000) 
insert into tb values('2008-01-19 08:03:40.467' , 3.8000 )
insert into tb values('2008-01-20 08:43:06.543' , 1.0000 )
insert into tb values('2008-01-21 08:01:00.793' , 12.8000 )
insert into tb values('2008-01-22 08:11:17.293' , 11.0000 )
insert into tb values('2008-01-23 08:02:35.090' , 9.0000 )
goselect banci , sum(je) je from
(
  select case when datepart(hh,time) between 0 and 14 then convert(varchar(11),time,120) + '00:00-14:59' else convert(varchar(11),time,120) + '15:00-23:59' end banci, je from tb
) m
group by bancidrop table tb/*
banci                  je                                       
---------------------- ---------------------------------------- 
2007-11-28 00:00-14:59 66.0000
2008-01-16 00:00-14:59 16.5000
2008-01-17 00:00-14:59 3.9200
2008-01-18 00:00-14:59 17.0000
2008-01-19 00:00-14:59 3.8000
2008-01-20 00:00-14:59 1.0000
2008-01-21 00:00-14:59 12.8000
2008-01-22 00:00-14:59 11.0000
2008-01-23 00:00-14:59 9.0000
2008-10-15 00:00-14:59 8.8000（所影响的行数为 10 行）
*/

Answer 17

18楼里面,我根据你的time字段生成一个班次的虚拟字段,然后再group by banci