create table gm
(销售时间 varchar(50),销售商品 varchar(10),销售数量 int,单价 decimal(5,1))insert into gm
select '2014-01-01 09:01','a',2,3.0 union all
select '2014-01-01 09:05','a',1,3.0 union all
select '2014-01-01 10:03','b',2,3.0 union all
select '2014-01-01 14:03','a',1,3.0 union all
select '2014-01-02 08:25','a',2,3.0 union all
select '2014-01-02 09:03','a',2,3.0 union all
select '2014-01-02 10:03','b',1,3.0
select convert(varchar,cast(销售时间 as datetime),23) '销售日期',
sum(销售数量) '销售数量',
'8:25-9:25' '时间段'
from gm
where cast(销售时间 as time) between '08:25:00' and '09:25:00'
group by convert(varchar,cast(销售时间 as datetime),23)/*
销售日期 销售数量 时间段
------------------------------ ----------- ---------
2014-01-01 3 8:25-9:25
2014-01-02 4 8:25-9:25(2 row(s) affected)
*/
解决方案 »
- 请教一个两表连接的SQL语句
- 加1结果不美满
- group by 和distinct 怎么隐含有排序?怎样去掉这个顺序?
- 怎样搜索出所有触发器,然后SQL语句删除?
- 弱问题
- numeric類型和decimal 類型有什麼不同,從decimal類型轉到numeric類型會不會數據出錉
- update问题
- 请问这两个设计方式哪种速度更快些???
- The index entry for row ID was not found in index ID 6, of table 1646628909, in database 'mydatabase'.
- LINUX7.1上安装ORACLE8。1。6是否能成功!会出现什么问题?
- SQL查询
- SQL 2008 发布订阅的时间怎么测
不好意思,刚才没说清楚,我用的是SQLSERVER2000,提示:
类型 time 不是已定义的系统类型。
是不是SQLSERVER2000以上就没有问题?
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-07 16:12:33
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([销售时间] datetime,[销售商品] varchar(1),[销售数量] int,[单价] numeric(2,1))
insert [tb]
select '2014-01-01 09:01','a',2,3.0 union all
select '2014-01-01 09:05','a',1,3.0 union all
select '2014-01-01 10:03','b',2,3.0 union all
select '2014-01-01 14:03','a',1,3.0 union all
select '2014-01-02 08:25','a',2,3.0 union all
select '2014-01-02 09:03','a',2,3.0 union all
select '2014-01-02 10:03','b',1,3.0
--------------开始查询--------------------------
SELECT
CONVERT(VARCHAR(10),销售时间,120) AS 时间,
SUM(CASE WHEN CONVERT(VARCHAR(5),销售时间,108) BETWEEN '08:25' AND '09:25' THEN 销售数量 ELSE 0 END) AS 销售数量,
' 8:25 - 9:25' AS 时间段
FROM
tb
GROUP BY
CONVERT(VARCHAR(10),销售时间,120)
----------------结果----------------------------
/*时间 销售数量 时间段
---------- ----------- --------------
2014-01-01 3 8:25 - 9:25
2014-01-02 4 8:25 - 9:25(2 行受影响)*/
(销售时间 varchar(50),销售商品 varchar(10),销售数量 int,单价 decimal(5,1))
insert into gm
select '2014-01-01 09:01','a',2,3.0 union all
select '2014-01-01 09:05','a',1,3.0 union all
select '2014-01-01 10:03','b',2,3.0 union all
select '2014-01-01 14:03','a',1,3.0 union all
select '2014-01-02 08:25','a',2,3.0 union all
select '2014-01-02 09:03','a',2,3.0 union all
select '2014-01-02 10:03','b',1,3.0
select convert(varchar,cast(销售时间 as datetime),23) '销售日期',
sum(销售数量) '销售数量',
'8:25-9:25' '时间段'
from gm
where convert(varchar(5),cast(销售时间 as datetime),114) between '08:25' and '09:25'
group by convert(varchar,cast(销售时间 as datetime),23)/*
销售日期 销售数量 时间段
------------------------------ ----------- ---------
2014-01-01 3 8:25-9:25
2014-01-02 4 8:25-9:25(2 row(s) affected)
*/