SoldQTY SoldTime20 2009-4-18 0:00:00
2 2009-4-18 0:00:00
1 2009-4-18 0:00:00
3 2009-4-18 0:00:00
1 2009-4-18 0:00:00
12 2009-4-18 0:00:00
1 2009-5-8 0:00:00
1 2009-5-8 0:00:00
10 2009-5-9 0:00:00
2 2009-5-9 0:00:00
1 2009-5-9 0:00:00
2 2009-5-9 0:00:00
2 2009-5-11 0:00:00
2 2009-5-11 0:00:00
1 2009-5-12 0:00:00
4 2009-5-12 0:00:00
2 2009-5-12 0:00:00
结果应该是这样
39 2009-4-18
2 2009-5-8
15 2009-5-9
4 2009-5-11
7 2009-5-12
2 2009-4-18 0:00:00
1 2009-4-18 0:00:00
3 2009-4-18 0:00:00
1 2009-4-18 0:00:00
12 2009-4-18 0:00:00
1 2009-5-8 0:00:00
1 2009-5-8 0:00:00
10 2009-5-9 0:00:00
2 2009-5-9 0:00:00
1 2009-5-9 0:00:00
2 2009-5-9 0:00:00
2 2009-5-11 0:00:00
2 2009-5-11 0:00:00
1 2009-5-12 0:00:00
4 2009-5-12 0:00:00
2 2009-5-12 0:00:00
结果应该是这样
39 2009-4-18
2 2009-5-8
15 2009-5-9
4 2009-5-11
7 2009-5-12
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-09 11:30:12
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([SoldQTY] int,[SoldTime] Datetime)
Insert tb
Select 20,'2009-4-18' union all
Select 2,'2009-4-18' union all
Select 1,'2009-4-18' union all
Select 3,'2009-4-18' union all
Select 1,'2009-4-18' union all
Select 12,'2009-4-18' union all
Select 1,'2009-5-8' union all
Select 1,'2009-5-8' union all
Select 10,'2009-5-9' union all
Select 2,'2009-5-9' union all
Select 1,'2009-5-9' union all
Select 2,'2009-5-9' union all
Select 2,'2009-5-11' union all
Select 2,'2009-5-11' union all
Select 1,'2009-5-12' union all
Select 4,'2009-5-12' union all
Select 2,'2009-5-12'
Go
--Select * from tb-->SQL查询如下:
select sum([SoldQTY]) [SoldQTY],[SoldTime]
from tb
group by [SoldTime]
/*
SoldQTY SoldTime
----------- -----------------------
39 2009-04-18 00:00:00.000
2 2009-05-08 00:00:00.000
15 2009-05-09 00:00:00.000
4 2009-05-11 00:00:00.000
7 2009-05-12 00:00:00.000(5 行受影响)
*/
from tab group by SoldQTY
select sum(SoldQTY),substring(convert(varchar(20),SoldTime,121),1,10)
from tb
group by substring(convert(varchar(20),SoldTime,121),1,10)
from tb
group by Left([SoldTime],10)
INSERT @TB
SELECT 20, '2009-4-18 0:00:00' UNION ALL
SELECT 2, '2009-4-18 0:00:00' UNION ALL
SELECT 1, '2009-4-18 0:00:00' UNION ALL
SELECT 3, '2009-4-18 0:00:00' UNION ALL
SELECT 1, '2009-4-18 0:00:00' UNION ALL
SELECT 12, '2009-4-18 0:00:00' UNION ALL
SELECT 1, '2009-5-8 0:00:00' UNION ALL
SELECT 1, '2009-5-8 0:00:00' UNION ALL
SELECT 10, '2009-5-9 0:00:00' UNION ALL
SELECT 2, '2009-5-9 0:00:00' UNION ALL
SELECT 1, '2009-5-9 0:00:00' UNION ALL
SELECT 2, '2009-5-9 0:00:00' UNION ALL
SELECT 2, '2009-5-11 0:00:00' UNION ALL
SELECT 2, '2009-5-11 0:00:00' UNION ALL
SELECT 1, '2009-5-12 0:00:00' UNION ALL
SELECT 4, '2009-5-12 0:00:00' UNION ALL
SELECT 2, '2009-5-12 0:00:00'SELECT SUM([SoldQTY]),CONVERT(VARCHAR(10),[SoldTime],120)
FROM @TB
GROUP BY CONVERT(VARCHAR(10),[SoldTime],120)
/*
----------- ----------
39 2009-04-18
2 2009-05-08
15 2009-05-09
4 2009-05-11
7 2009-05-12
*/
12 2009-04-13 07:54:57.000
10 2009-04-13 07:55:15.000
10 2009-04-13 07:55:40.000
39 2009-04-18 00:00:00.000
2 2009-05-08 00:00:00.000
15 2009-05-09 00:00:00.000
4 2009-05-11 00:00:00.000
Group by convert(varchar(10),SoldTime,20)
Drop table [tb]
Go
Create table [tb]([SoldQTY] int,[SoldTime] Datetime)
Insert tb
Select 20,'2009-4-18' union all
Select 2,'2009-4-18' union all
Select 1,'2009-4-18' union all
Select 3,'2009-4-18' union all
Select 1,'2009-4-18' union all
Select 12,'2009-4-18' union all
Select 1,'2009-5-8' union all
Select 1,'2009-5-8' union all
Select 10,'2009-5-9' union all
Select 2,'2009-5-9' union all
Select 1,'2009-5-9' union all
Select 2,'2009-5-9' union all
Select 2,'2009-5-11' union all
Select 2,'2009-5-11' union all
Select 1,'2009-5-12' union all
Select 4,'2009-5-12' union all
Select 2,'2009-5-12'
Go
--Select * from tb-->SQL查询如下:
select sum([SoldQTY]) [SoldQTY],[SoldTime]=convert(varchar(10),[SoldTime],120)
from tb
group by [SoldTime]
/*
39 2009-04-18
2 2009-05-08
15 2009-05-09
4 2009-05-11
7 2009-05-12
*/
Group by convert(varchar(10),SoldTime,20)
保证可以,给分吧!
FROM 表
GROUP BY CONVERT(VARCHAR(10),SoldTime,120)