表A:
id name num date
1 aa 1 2009-5-1
2 bb 2 2009-5-2
3 cc 1 2009-5-3
4 aa 3 2009-5-6
5 aa 2 2009-5-10
6 bb 5 2009-5-12
7 cc 1 2009-5-15
8 cc 1 2009-5-17
9 cc 2 2009-5-18
10 aa 1 2009-5-19
11 bb 4 2009-5-20
12 bb 6 2009-5-23
13 aa 3 2009-5-27我想按周统计name的num总数
统计的结果应该如下:
第一周 第二周 第三周 第四周 第五周
name num name num name num name num name num
bb 2 aa 5 bb 5 bb 10 aa 3
aa 1 bb 0 cc 2 cc 2 bb 0
cc 1 cc 0 aa 0 aa 1 cc 0
以上的"第X周"也是根据date自动生成的,要是后面还有数据,就会显示第六周、第七周。哪位高手直接这样的sql语句怎么写,帮我解决下,谢谢
id name num date
1 aa 1 2009-5-1
2 bb 2 2009-5-2
3 cc 1 2009-5-3
4 aa 3 2009-5-6
5 aa 2 2009-5-10
6 bb 5 2009-5-12
7 cc 1 2009-5-15
8 cc 1 2009-5-17
9 cc 2 2009-5-18
10 aa 1 2009-5-19
11 bb 4 2009-5-20
12 bb 6 2009-5-23
13 aa 3 2009-5-27我想按周统计name的num总数
统计的结果应该如下:
第一周 第二周 第三周 第四周 第五周
name num name num name num name num name num
bb 2 aa 5 bb 5 bb 10 aa 3
aa 1 bb 0 cc 2 cc 2 bb 0
cc 1 cc 0 aa 0 aa 1 cc 0
以上的"第X周"也是根据date自动生成的,要是后面还有数据,就会显示第六周、第七周。哪位高手直接这样的sql语句怎么写,帮我解决下,谢谢
应该是datediff吧 萧兄?
能把sql语句直接粘出来不?
--> 生成测试数据: #tb
IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb
CREATE TABLE #tb (id INT,name VARCHAR(2),num INT,date DATETIME)
INSERT INTO #tb
SELECT 1,'aa',1,'2009-5-1' UNION ALL
SELECT 2,'bb',2,'2009-5-2' UNION ALL
SELECT 3,'cc',1,'2009-5-3' UNION ALL
SELECT 4,'aa',3,'2009-5-6' UNION ALL
SELECT 5,'aa',2,'2009-5-10' UNION ALL
SELECT 6,'bb',5,'2009-5-12' UNION ALL
SELECT 7,'cc',1,'2009-5-15' UNION ALL
SELECT 8,'cc',1,'2009-5-17' UNION ALL
SELECT 9,'cc',2,'2009-5-18' UNION ALL
SELECT 10,'aa',1,'2009-5-19' UNION ALL
SELECT 11,'bb',4,'2009-5-20' UNION ALL
SELECT 12,'bb',6,'2009-5-23' UNION ALL
SELECT 13,'aa',3,'2009-5-27'
--SQL查询如下:;WITH Liang AS
(
SELECT A.name,B.week,C.num,
rowweek = DENSE_RANK() OVER(ORDER BY B.week)
FROM (SELECT DISTINCT name FROM #tb) AS A
CROSS JOIN (SELECT DISTINCT DATEPART(week,date) AS week FROM #tb) AS B
LEFT JOIN #tb AS C
ON A.name = C.name AND B.week = DATEPART(week,C.date)
)
SELECT
name,
SUM(CASE WHEN rowweek = 1 THEN num ELSE 0 END) AS num1,
SUM(CASE WHEN rowweek = 2 THEN num ELSE 0 END) AS num2,
SUM(CASE WHEN rowweek = 3 THEN num ELSE 0 END) AS num3,
SUM(CASE WHEN rowweek = 4 THEN num ELSE 0 END) AS num4,
SUM(CASE WHEN rowweek = 5 THEN num ELSE 0 END) AS num5
FROM Liang
GROUP BY name/*
name num1 num2 num3 num4 num5
---- ----------- ----------- ----------- ----------- -----------
aa 1 3 2 1 3
bb 2 0 5 10 0
cc 0 1 1 3 0(3 row(s) affected)
*/
--> 生成测试数据: #tb
IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb
CREATE TABLE #tb (id INT,name VARCHAR(2),num INT,date DATETIME)
INSERT INTO #tb
SELECT 1,'aa',1,'2009-5-1' UNION ALL
SELECT 2,'bb',2,'2009-5-2' UNION ALL
SELECT 3,'cc',1,'2009-5-3' UNION ALL
SELECT 4,'aa',3,'2009-5-6' UNION ALL
SELECT 5,'aa',2,'2009-5-10' UNION ALL
SELECT 6,'bb',5,'2009-5-12' UNION ALL
SELECT 7,'cc',1,'2009-5-15' UNION ALL
SELECT 8,'cc',1,'2009-5-17' UNION ALL
SELECT 9,'cc',2,'2009-5-18' UNION ALL
SELECT 10,'aa',1,'2009-5-19' UNION ALL
SELECT 11,'bb',4,'2009-5-20' UNION ALL
SELECT 12,'bb',6,'2009-5-23' UNION ALL
SELECT 13,'aa',3,'2009-5-27'
--SQL查询如下:;WITH Liang AS
(
SELECT *,
rowweek = DENSE_RANK() OVER(ORDER BY DATEPART(week,date))
FROM #tb
)
SELECT
name,
SUM(CASE WHEN rowweek = 1 THEN num ELSE 0 END) AS num1,
SUM(CASE WHEN rowweek = 2 THEN num ELSE 0 END) AS num2,
SUM(CASE WHEN rowweek = 3 THEN num ELSE 0 END) AS num3,
SUM(CASE WHEN rowweek = 4 THEN num ELSE 0 END) AS num4,
SUM(CASE WHEN rowweek = 5 THEN num ELSE 0 END) AS num5
FROM Liang
GROUP BY name/*
name num1 num2 num3 num4 num5
---- ----------- ----------- ----------- ----------- -----------
aa 1 3 2 1 3
bb 2 0 5 10 0
cc 0 1 1 3 0(3 row(s) affected)
*/
create table tb(id int,name nvarchar(20),num decimal(14,2),dates varchar(10))
go
insert tb
select 1,'aa',1,'2009-5-1'
union all select 2,'bb',2 ,'2009-5-2'
union all select 3,'cc',1 ,'2009-5-3'
union all select 4,'aa',3 ,'2009-5-6'
union all select 5,'aa',2 ,'2009-5-10'
union all select 6,'bb',5 ,'2009-5-12'
union all select 7,'cc',1,'2009-5-15'
union all select 8,'cc',1 ,'2009-5-17'
union all select 9,'cc',2 ,'2009-5-18'
union all select 10,'aa',1,'2009-5-19'
union all select 11,'bb',4,'2009-5-20'
union all select 12,'bb',6,'2009-5-23'
union all select 13,'aa',3,'2009-5-27'select *,DATENAME(week,dates) as we into #a
from tb SELECT
name,
SUM(CASE WHEN rowweek = 1 THEN num ELSE 0 END) AS num1,
name,
SUM(CASE WHEN rowweek = 2 THEN num ELSE 0 END) AS num2,
name,
SUM(CASE WHEN rowweek = 3 THEN num ELSE 0 END) AS num3,
name,
SUM(CASE WHEN rowweek = 4 THEN num ELSE 0 END) AS num4,
name,
SUM(CASE WHEN rowweek = 5 THEN num ELSE 0 END) AS num5
FROM (
select *,rowweek=(select COUNT(distinct(we))+1 from #a where we<a.we)
from #a a
)a
group by a.name/*
name num1 name num2 name num3 name num4 name num5
aa 1.00 aa 3.00 aa 2.00 aa 1.00 aa 3.00
bb 2.00 bb 0.00 bb 5.00 bb 10.00 bb 0.00
cc 0.00 cc 1.00 cc 1.00 cc 3.00 cc 0.00
*/如果数据是2000是这种方法,否则用楼上的方法!