新年到来了,现在是一月份,对表中的数据进行每月统计,表格如下:
id time num1 num2
1 2009-01-01 25 89
2 2009-01-02 25 58
3 2009-01-03 23 34
4 2009-02-03 23 134
5 2009-02-04 21 324
6 2009-03-03 21 334
7 2009-03-05 21 314 time num1 num2
2009-01 73 181
2009-12 。
。
但是一共只有三行,(因为只有三个月份),现在想对统计结果用GridView绑定,绑定后的表应该有十二个月份的,没有数据的应该显示为0,如何搞?
就是说现在只有三行,如何搞成12行,多谢。
id time num1 num2
1 2009-01-01 25 89
2 2009-01-02 25 58
3 2009-01-03 23 34
4 2009-02-03 23 134
5 2009-02-04 21 324
6 2009-03-03 21 334
7 2009-03-05 21 314 time num1 num2
2009-01 73 181
2009-12 。
。
但是一共只有三行,(因为只有三个月份),现在想对统计结果用GridView绑定,绑定后的表应该有十二个月份的,没有数据的应该显示为0,如何搞?
就是说现在只有三行,如何搞成12行,多谢。
select m.time ,
isnull((select sum(num1) from tb n where convert(varchar(7),n.time,120) = m.time),0) [num1],
isnull((select sum(num2) from tb n where convert(varchar(7),n.time,120) = m.time),0) [num2]
from
(
select '2009-01' time union all
select '2009-02' time union all
select '2009-03' time union all
select '2009-04' time union all
select '2009-05' time union all
select '2009-06' time union all
select '2009-07' time union all
select '2009-08' time union all
select '2009-09' time union all
select '2009-10' time union all
select '2009-11' time union all
select '2009-12' time
) m
(
id int identity(1,1) primary key,
[time] datetime,
num1 int,
num2 int
)
insert into #TT select '2009-01-01',25,89
insert into #TT select '2009-01-02',25,58
insert into #TT select '2009-01-03',23,34
insert into #TT select '2009-02-03',23,134
insert into #TT select '2009-02-04',21,324
insert into #TT select '2009-03-03',21,334
insert into #TT select '2009-03-05',21,314select convert(nvarchar(7),t1.[time],120) as [time],
sum(case when convert(nvarchar(7),t1.[time],120)=t2.time then num1 else 0 end) '2009-01',
sum(case when convert(nvarchar(7),t1.[time],120)=t2.time then num2 else 0 end) '2009-02'
from #TT t1 join
(
select '2009-01' time union all
select '2009-02' time union all
select '2009-03' time union all
select '2009-04' time union all
select '2009-05' time union all
select '2009-06' time union all
select '2009-07' time union all
select '2009-08' time union all
select '2009-09' time union all
select '2009-10' time union all
select '2009-11' time union all
select '2009-12' time
) t2
on convert(nvarchar(7),t1.[time],120)=t2.time
group by convert(nvarchar(7),t1.[time],120)
time 2009-01 2009-02
------- ----------- -----------
2009-01 73 181
2009-02 44 458
2009-03 42 648
sum(case when convert(nvarchar(7),t1.[time],120)=t2.time then num1 else 0 end) '2009-01',
sum(case when convert(nvarchar(7),t1.[time],120)=t2.time then num2 else 0 end) '2009-02'
from #TT t1 left join
(
select '2009-01' time union all
select '2009-02' time union all
select '2009-03' time union all
select '2009-04' time union all
select '2009-05' time union all
select '2009-06' time union all
select '2009-07' time union all
select '2009-08' time union all
select '2009-09' time union all
select '2009-10' time union all
select '2009-11' time union all
select '2009-12' time
) t2
on convert(nvarchar(7),t1.[time],120)=t2.time
group by convert(nvarchar(7),t1.[time],120)
create table tb
(
id int identity(1,1) primary key,
[time] datetime,
num1 int,
num2 int
)
insert into tb select '2009-01-01',25,89
insert into tb select '2009-01-02',25,58
insert into tb select '2009-01-03',23,34
insert into tb select '2009-02-03',23,134
insert into tb select '2009-02-04',21,324
insert into tb select '2009-03-03',21,334
insert into tb select '2009-03-05',21,314
goselect m.time ,
isnull((select sum(num1) from tb n where convert(varchar(7),n.time,120) = m.time),0) [num1],
isnull((select sum(num2) from tb n where convert(varchar(7),n.time,120) = m.time),0) [num2]
from
(
select '2009-01' time union all
select '2009-02' time union all
select '2009-03' time union all
select '2009-04' time union all
select '2009-05' time union all
select '2009-06' time union all
select '2009-07' time union all
select '2009-08' time union all
select '2009-09' time union all
select '2009-10' time union all
select '2009-11' time union all
select '2009-12' time
) mdrop table tb/*
time num1 num2
------- ----------- -----------
2009-01 73 181
2009-02 44 458
2009-03 42 648
2009-04 0 0
2009-05 0 0
2009-06 0 0
2009-07 0 0
2009-08 0 0
2009-09 0 0
2009-10 0 0
2009-11 0 0
2009-12 0 0(所影响的行数为 12 行)*/
LEFT(CONVERT(VARCHAR(10),DATEADD(MM,NUMBER,@TIME),120),7)AS TIME
FROM
MASTER..SPT_VALUES
WHERE TYPE='P' AND DATEADD(MM,NUMBER,@TIME)<='2009-12-01'TIME
----------
2009-01
2009-02
2009-03
2009-04
2009-05
2009-06
2009-07
2009-08
2009-09
2009-10
2009-11
2009-12(所影响的行数为 12 行)缺失日期
-- Author :SQL77(只为思齐老)
-- Date :2010-01-07 09:23:01
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([id] int,[time] datetime,[num1] int,[num2] int)
insert [TB]
select 1,'2009-01-01',25,89 union all
select 2,'2009-01-02',25,58 union all
select 3,'2009-01-03',23,34 union all
select 4,'2009-02-03',23,134 union all
select 5,'2009-02-04',21,324 union all
select 6,'2009-03-03',21,334 union all
select 7,'2009-03-05',21,314
--------------开始查询--------------------------
DECLARE @TIME DATETIMESET @TIME='2009-01-01'
SELECT A.TIME,ISNULL(SUM(NUM1),0)NUM1,ISNULL(SUM(NUM2),0)NUM2
FROM
(
SELECT
CONVERT(VARCHAR(7),DATEADD(MM,NUMBER,@TIME),120)AS TIME
FROM
MASTER..SPT_VALUES
WHERE TYPE='P' AND DATEADD(MM,NUMBER,@TIME)<='2009-12-01')A LEFT JOIN TB
ON CONVERT(VARCHAR(7),TB.TIME,120)=A.TIME GROUP BY A.TIME
--select * from [TB]
----------------结果----------------------------
/* (所影响的行数为 7 行)TIME NUM1 NUM2
------- ----------- -----------
2009-01 73 181
2009-02 44 458
2009-03 42 648
2009-04 0 0
2009-05 0 0
2009-06 0 0
2009-07 0 0
2009-08 0 0
2009-09 0 0
2009-10 0 0
2009-11 0 0
2009-12 0 0(所影响的行数为 12 行)警告: 聚合或其它 SET 操作消除了空值。*/
--> 测试数据:@tb
declare @tb table([id] int,[time] datetime,[num1] int,[num2] int)
insert @tb
select 1,'2009-01-01',25,89 union all
select 2,'2009-01-02',25,58 union all
select 3,'2009-01-03',23,34 union all
select 4,'2009-02-03',23,134 union all
select 5,'2009-02-04',21,324 union all
select 6,'2009-03-03',21,334 union all
select 7,'2009-03-05',21,314select b.number,
b.date,
sum(isnull(a.num1,0)) as num1,
sum(isnull(a.num2,0))as num2 from @tb a
right join
(
select '2009-' + right('00'+cast(number as varchar(2)),2)+'-01' as date,number from master..spt_values where type ='p' and number between 1 and 12
) b
on convert(varchar(7),a.[time],120)=left(b.date,7)
group by b.number,b.date
/*
number date num1 num2
----------- ------------ ----------- -----------
1 2009-01-01 73 181
2 2009-02-01 44 458
3 2009-03-01 42 648
4 2009-04-01 0 0
5 2009-05-01 0 0
6 2009-06-01 0 0
7 2009-07-01 0 0
8 2009-08-01 0 0
9 2009-09-01 0 0
10 2009-10-01 0 0
11 2009-11-01 0 0
12 2009-12-01 0 0
*/--写个语句还被主管看见了,说我不干活,被批了一顿