select [统计月份]=convert(char(7),[时间],120), [统计数量]=sum([数量]) from 表 group by convert(char(7),[时间],120)
CREATE TABLE #T1(时间 DATETIME,数量 INT) INSERT INTO #T1 SELECT '2005-01-02',100 INSERT INTO #T1 SELECT '2005-01-03',50 INSERT INTO #T1 SELECT '2005-01-04',50 INSERT INTO #T1 SELECT '2005-03-02',100 INSERT INTO #T1 SELECT '2005-03-03',50 INSERT INTO #T1 SELECT '2005-03-04',50
SELECT IDENTITY(INT,1,1) AS ID INTO #T FROM SYSOBJECTSSELECT 统计月份 = CONVERT(CHAR(7),A.[DATE],120), 统计数量 = ISNULL(SUM(B.数量),0) FROM (SELECT DATEADD(MM,ID,'1999-12-01') AS [DATE] FROM #T) A LEFT JOIN #T1 B ON DATEDIFF(MM,A.[DATE],B.时间) = 0 WHERE A.[DATE] BETWEEN '2005-01-01' AND '2005-03-31' GROUP BY CONVERT(CHAR(7),A.[DATE],120)
--生成测试数据 CREATE TABLE #T1(时间 DATETIME,数量 INT) INSERT INTO #T1 SELECT '2005-01-02',100 INSERT INTO #T1 SELECT '2005-01-03',50 INSERT INTO #T1 SELECT '2005-01-04',50 INSERT INTO #T1 SELECT '2005-03-02',100 INSERT INTO #T1 SELECT '2005-03-03',50 INSERT INTO #T1 SELECT '2005-03-04',50
--执行查询 SELECT IDENTITY(INT,1,1) AS ID INTO #T FROM SYSOBJECTSSELECT 统计月份 = CONVERT(CHAR(7),A.[DATE],120), 统计数量 = ISNULL(SUM(B.数量),0) FROM (SELECT DATEADD(MM,ID,'1999-12-01') AS [DATE] FROM #T) A LEFT JOIN #T1 B ON DATEDIFF(MM,A.[DATE],B.时间) = 0 WHERE A.[DATE] BETWEEN '2005-01-01' AND '2005-03-31' GROUP BY CONVERT(CHAR(7),A.[DATE],120) --输出结果 /* 统计月份 统计数量 -------- --------- 2005-01 200 2005-02 0 2005-03 200 */
libin_ftsafe(子陌红尘)的是正确的
create table #A(DT varchar(10), SM int) insert into #A select '2005-01-02',100 union all select '2005-01-03',50 union all select '2005-01-04',50 union all select '2005-03-02',100 union all select '2005-03-03',50 union all select '2005-03-04',50 select DT into #S from #A where 1 = 0declare @SDT varchar(10) declare @EDT varchar(10) select @SDT=min(DT),@EDT=max(DT) from #A --抓取通加年月范围 while @SDT<= @EDT begin insert into #S values (substring(@SDT,1,7)) --塞入临时表 set @SDT = convert(varchar(10),dateadd(mm,1,@SDT),120) endselect DT=substring(S.DT,1,7), [sum]=sum(isnull(A.SM,0))from #S S left join #A A on substring(A.DT,1,7) = substring(S.DT,1,7) group by substring(S.DT,1,7)---2表联合查询/* DT sum ---------- ----------- 2005-01 200 2005-02 0 2005-03 200(3 row(s) affected) */
select count(数量), convert(varchar(4),year(时间))+'/'+convert(varchar(2),month(时间)) as 统计月份 from table where 时间 between '2002/01/01' and '2002/12/31' group by convert(varchar(4),year(时间))+'/'+convert(varchar(2),month(时间))
MorningTea(一勺抹茶)的有个问题哦. 按照日期(年+月+日)来比较的,如果只加月的话,当最小月和最大月份相同时候,那么最后一个月日期有可能会小于最小月变化后的值 --如2005-01-03 加4个月后将大于2005-05-02,这样如果日期最晚一条记录是2005-05-02的时候就没有5月份的统计了,所以要将最小月的的日期置最小值(2005-01-01) -------- 偶修改后的:create table #A(DT varchar(20), SM int) insert into #A select '2005-05-02 10:01:21',100 union all select '2005-01-03 10:01:21',50 union all select '2005-01-04',50 union all select '2005-03-02 17:01:21',100 union all select '2005-03-03',50 union all select '2005-11-02 17:01:21',100 union all select '2005-03-04',50 select DT into #S from #A where 1 = 0declare @SDT varchar(20) declare @EDT varchar(20) select @SDT=min(DT),@EDT=max(DT) from #A --抓取通加年月范围 --select @SDT,@EDT --原来是按照日期(年+月+日)来比较的,如果只加月的话,当最小月和最大月份相同时候,那么最后一个月日期会小于最小月变化后的值 --如2005-01-03 加4个月后将大于2005-05-02,所以要将最小月的的日期置最小值(2005-01-01) set @SDT = convert(varchar(10),dateadd(dd,-(datepart(dd,@SDT)-1),@SDT),120) --SELECT @SDT while @SDT<= @EDT begin insert into #S values (substring(@SDT,1,7)) --塞入临时表 --select substring(@SDT,1,7) set @SDT = convert(varchar(20),dateadd(mm,1,@SDT),120) --select @sdt end --SELECT* FROM #S select DT=substring(S.DT,1,7), [sum]=sum(isnull(A.SM,0))from #S S left join #A A on substring(A.DT,1,7) = substring(S.DT,1,7) group by substring(S.DT,1,7)---2表联合查询drop table #s drop table #a ------------------------- libin_ftsafe(子陌红尘)的也有此问题哦.
[统计月份]=convert(char(7),[时间],120),
[统计数量]=sum([数量])
from
表
group by
convert(char(7),[时间],120)
INSERT INTO #T1 SELECT '2005-01-02',100
INSERT INTO #T1 SELECT '2005-01-03',50
INSERT INTO #T1 SELECT '2005-01-04',50
INSERT INTO #T1 SELECT '2005-03-02',100
INSERT INTO #T1 SELECT '2005-03-03',50
INSERT INTO #T1 SELECT '2005-03-04',50
SELECT IDENTITY(INT,1,1) AS ID INTO #T FROM SYSOBJECTSSELECT
统计月份 = CONVERT(CHAR(7),A.[DATE],120),
统计数量 = ISNULL(SUM(B.数量),0)
FROM
(SELECT DATEADD(MM,ID,'1999-12-01') AS [DATE] FROM #T) A
LEFT JOIN
#T1 B
ON
DATEDIFF(MM,A.[DATE],B.时间) = 0
WHERE
A.[DATE] BETWEEN '2005-01-01' AND '2005-03-31'
GROUP BY
CONVERT(CHAR(7),A.[DATE],120)
CREATE TABLE #T1(时间 DATETIME,数量 INT)
INSERT INTO #T1 SELECT '2005-01-02',100
INSERT INTO #T1 SELECT '2005-01-03',50
INSERT INTO #T1 SELECT '2005-01-04',50
INSERT INTO #T1 SELECT '2005-03-02',100
INSERT INTO #T1 SELECT '2005-03-03',50
INSERT INTO #T1 SELECT '2005-03-04',50
--执行查询
SELECT IDENTITY(INT,1,1) AS ID INTO #T FROM SYSOBJECTSSELECT
统计月份 = CONVERT(CHAR(7),A.[DATE],120),
统计数量 = ISNULL(SUM(B.数量),0)
FROM
(SELECT DATEADD(MM,ID,'1999-12-01') AS [DATE] FROM #T) A
LEFT JOIN
#T1 B
ON
DATEDIFF(MM,A.[DATE],B.时间) = 0
WHERE
A.[DATE] BETWEEN '2005-01-01' AND '2005-03-31'
GROUP BY
CONVERT(CHAR(7),A.[DATE],120)
--输出结果
/*
统计月份 统计数量
-------- ---------
2005-01 200
2005-02 0
2005-03 200
*/
insert into #A
select '2005-01-02',100 union all
select '2005-01-03',50 union all
select '2005-01-04',50 union all
select '2005-03-02',100 union all
select '2005-03-03',50 union all
select '2005-03-04',50 select DT into #S from #A where 1 = 0declare @SDT varchar(10)
declare @EDT varchar(10)
select @SDT=min(DT),@EDT=max(DT) from #A --抓取通加年月范围
while @SDT<= @EDT
begin
insert into #S values (substring(@SDT,1,7)) --塞入临时表
set @SDT = convert(varchar(10),dateadd(mm,1,@SDT),120)
endselect DT=substring(S.DT,1,7), [sum]=sum(isnull(A.SM,0))from #S S
left join #A A
on substring(A.DT,1,7) = substring(S.DT,1,7)
group by substring(S.DT,1,7)---2表联合查询/*
DT sum
---------- -----------
2005-01 200
2005-02 0
2005-03 200(3 row(s) affected)
*/
where 时间 between '2002/01/01' and '2002/12/31'
group by convert(varchar(4),year(时间))+'/'+convert(varchar(2),month(时间))
libin_ftsafe(子陌红尘)
MorningTea(一勺抹茶)
是對的
按照日期(年+月+日)来比较的,如果只加月的话,当最小月和最大月份相同时候,那么最后一个月日期有可能会小于最小月变化后的值
--如2005-01-03 加4个月后将大于2005-05-02,这样如果日期最晚一条记录是2005-05-02的时候就没有5月份的统计了,所以要将最小月的的日期置最小值(2005-01-01)
--------
偶修改后的:create table #A(DT varchar(20), SM int)
insert into #A
select '2005-05-02 10:01:21',100 union all
select '2005-01-03 10:01:21',50 union all
select '2005-01-04',50 union all
select '2005-03-02 17:01:21',100 union all
select '2005-03-03',50 union all
select '2005-11-02 17:01:21',100 union all
select '2005-03-04',50 select DT into #S from #A where 1 = 0declare @SDT varchar(20)
declare @EDT varchar(20)
select @SDT=min(DT),@EDT=max(DT) from #A --抓取通加年月范围
--select @SDT,@EDT
--原来是按照日期(年+月+日)来比较的,如果只加月的话,当最小月和最大月份相同时候,那么最后一个月日期会小于最小月变化后的值
--如2005-01-03 加4个月后将大于2005-05-02,所以要将最小月的的日期置最小值(2005-01-01)
set @SDT = convert(varchar(10),dateadd(dd,-(datepart(dd,@SDT)-1),@SDT),120)
--SELECT @SDT
while @SDT<= @EDT
begin
insert into #S values (substring(@SDT,1,7)) --塞入临时表
--select substring(@SDT,1,7)
set @SDT = convert(varchar(20),dateadd(mm,1,@SDT),120)
--select @sdt
end
--SELECT* FROM #S
select DT=substring(S.DT,1,7), [sum]=sum(isnull(A.SM,0))from #S S
left join #A A
on substring(A.DT,1,7) = substring(S.DT,1,7)
group by substring(S.DT,1,7)---2表联合查询drop table #s
drop table #a
-------------------------
libin_ftsafe(子陌红尘)的也有此问题哦.