高手请进： 如何获取每月的统计数据?

Question

select dCreateDate,dTotalValue,ArchiveType from vDeal显示数据如下
================
dCreateDate         dTotalValue       ArchiveType 
2008-01-21 00:00:00 45303.00 Dead
2008-01-25 00:00:00 96462.00 Dead
2008-01-30 00:00:00 101555.00 Dead
2008-01-30 00:00:00 131039.00 Dead
2008-02-05 00:00:00 123308.00 Dead
2008-04-25 00:00:00 10000.00 Dead
2008-04-25 00:00:00 15000.00 Dead
2008-05-13 00:00:00 4500.00 Dead
2008-05-13 00:00:00 63500.00 Dead
2008-05-23 00:00:00 16200.00 Closed
2008-05-23 00:00:00 38340.00 Closed
2008-05-23 00:00:00 180299.00 Closed
2008-05-23 00:00:00 82248.00 Closed
2008-05-23 00:00:00 10600.00 Dead
2008-05-23 00:00:00 5900.00 Closed
2008-05-23 00:00:00 2400.00 Closed
2008-05-27 00:00:00 11016.00 Dead
2008-05-27 00:00:00 28350.00 Dead
2008-05-29 00:00:00 8745.00 Dead
2008-05-29 00:00:00 16563.00 Dead
2008-05-29 00:00:00 10600.00 Closed--- 
现在要统计出 如下的结果
即： 要一年每个月，三种类别的统计结果。 如果该月没有对应的数据，用0表示
三种类别饭别是：  Closed, Dead, reactivate月份     ArchiveType   总值
2009-01 reactivate       ***
2009-01 Closed      ***
2009-01 Dead      ***
2009-02 reactivate       ***
2009-02 Closed      ***
2009-02 Dead      ***
2009-03 reactivate       ***
2009-03 Closed      ***
2009-03 Dead      ***

Answer 1

建一个临时表（填充年月），然后左连接，最后group by月分，archivetype;求和sum(isnull(dTotalValue,0))

Answer 2

--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([dCreateDate] Datetime,[dTotalValue] decimal(18,2),[ArchiveType] nvarchar(6))
Insert [tb]
Select '2008-01-21',45303.00,N'Dead' union all
Select '2008-01-25',96462.00,N'Dead' union all
Select '2008-01-30',101555.00,N'Dead' union all
Select '2008-01-30',131039.00,N'Dead' union all
Select '2008-02-05',123308.00,N'Dead' union all
Select '2008-04-25',10000.00,N'Dead' union all
Select '2008-04-25',15000.00,N'Dead' union all
Select '2008-05-13',4500.00,N'Dead' union all
Select '2008-05-13',63500.00,N'Dead' union all
Select '2008-05-23',16200.00,N'Closed' union all
Select '2008-05-23',38340.00,N'Closed' union all
Select '2008-05-23',180299.00,N'Closed' union all
Select '2008-05-23',82248.00,N'Closed' union all
Select '2008-05-23',10600.00,N'Dead' union all
Select '2008-05-23',5900.00,N'Closed' union all
Select '2008-05-23',2400.00,N'Closed' union all
Select '2008-05-27',11016.00,N'Dead' union all
Select '2008-05-27',28350.00,N'Dead' union all
Select '2008-05-29',8745.00,N'Dead' union all
Select '2008-05-29',16563.00,N'Dead' union all
Select '2008-05-29',10600.00,N'Closed'
Go
--Select * from [tb]-->SQL查询如下:
SELECT CONVERT(CHAR(7),[dCreateDate],23) 月份,[ArchiveType],
总值=SUM([dTotalValue])
FROM dbo.tb
GROUP BY  CONVERT(CHAR(7),[dCreateDate],23),[ArchiveType]
ORDER BY 1
/*
月份      ArchiveType 总值
------- ----------- ---------------------------------------
2008-01 Dead        374359.00
2008-02 Dead        123308.00
2008-04 Dead        25000.00
2008-05 Closed      335987.00
2008-05 Dead        143274.00(5 行受影响)
*/

Answer 3

--> 生成测试数据表:tbIf not object_id('[tb]') is null
    Drop table [tb]
Go
Create table [tb]([dCreateDate] Datetime,[dTotalValue] decimal(18,2),[ArchiveType] nvarchar(6))
Insert [tb]
Select '2008-01-21',45303.00,N'Dead' union all
Select '2008-01-25',96462.00,N'Dead' union all
Select '2008-01-30',101555.00,N'Dead' union all
Select '2008-01-30',131039.00,N'Dead' union all
Select '2008-02-05',123308.00,N'Dead' union all
Select '2008-04-25',10000.00,N'Dead' union all
Select '2008-04-25',15000.00,N'Dead' union all
Select '2008-05-13',4500.00,N'Dead' union all
Select '2008-05-13',63500.00,N'Dead' union all
Select '2008-05-23',16200.00,N'Closed' union all
Select '2008-05-23',38340.00,N'Closed' union all
Select '2008-05-23',180299.00,N'Closed' union all
Select '2008-05-23',82248.00,N'Closed' union all
Select '2008-05-23',10600.00,N'Dead' union all
Select '2008-05-23',5900.00,N'Closed' union all
Select '2008-05-23',2400.00,N'Closed' union all
Select '2008-05-27',11016.00,N'Dead' union all
Select '2008-05-27',28350.00,N'Dead' union all
Select '2008-05-29',8745.00,N'Dead' union all
Select '2008-05-29',16563.00,N'Dead' union all
Select '2008-05-29',10600.00,N'Closed'
Go
--Select * from [tb]-->SQL查询如下:
SELECT a.[dCreateDate],a.[ArchiveType],
    总值=isnull(SUM([dTotalValue]),0)
    from 
(
select distinct CONVERT(varchar(7),[dCreateDate],120) as [dCreateDate],b.ArchiveType from tb a
cross join
  (select distinct [ArchiveType] from tb)b
)a
left join tb b
on a.[dCreateDate]=CONVERT(varchar(7),b.[dCreateDate],120) and a.ArchiveType=b.ArchiveType
GROUP BY  a.[dCreateDate],a.[ArchiveType]
ORDER BY 1
/*
dCreateDate ArchiveType 总值
2008-01 Closed 0.00
2008-01 Dead 374359.00
2008-02 Closed 0.00
2008-02 Dead 123308.00
2008-04 Closed 0.00
2008-04 Dead 25000.00
2008-05 Closed 335987.00
2008-05 Dead 143274.00
*
/借4楼数据

Answer 4

月份    ArchiveType  总值 
2009-01 reactivate      *** 
2009-01 Closed    *** 
2009-01 Dead    *** 
2009-02 reactivate      *** 
2009-02 Closed    *** 
2009-02 Dead    *** 
2009-03 reactivate      *** 
2009-03 Closed    *** 
2009-03 Dead    ***楼主给的数据中并没有reactivate   
   

Answer 5

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-07 12:41:14
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([dCreateDate] datetime,[dTotalValue] numeric(8,2),[ArchiveType] varchar(6))
insert [tb]
select '2008-01-21 00:00:00',45303.00,'Dead' union all
select '2008-01-25 00:00:00',96462.00,'Dead' union all
select '2008-01-30 00:00:00',101555.00,'Dead' union all
select '2008-01-30 00:00:00',131039.00,'Dead' union all
select '2008-02-05 00:00:00',123308.00,'Dead' union all
select '2008-04-25 00:00:00',10000.00,'Dead' union all
select '2008-04-25 00:00:00',15000.00,'Dead' union all
select '2008-05-13 00:00:00',4500.00,'Dead' union all
select '2008-05-13 00:00:00',63500.00,'Dead' union all
select '2008-05-23 00:00:00',16200.00,'Closed' union all
select '2008-05-23 00:00:00',38340.00,'Closed' union all
select '2008-05-23 00:00:00',180299.00,'Closed' union all
select '2008-05-23 00:00:00',82248.00,'Closed' union all
select '2008-05-23 00:00:00',10600.00,'Dead' union all
select '2008-05-23 00:00:00',5900.00,'Closed' union all
select '2008-05-23 00:00:00',2400.00,'Closed' union all
select '2008-05-27 00:00:00',11016.00,'Dead' union all
select '2008-05-27 00:00:00',28350.00,'Dead' union all
select '2008-05-29 00:00:00',8745.00,'Dead' union all
select '2008-05-29 00:00:00',16563.00,'Dead' union all
select '2008-05-29 00:00:00',10600.00,'Closed'
--------------开始查询--------------------------
SELECT 
    CONVERT(VARCHAR(7),[dCreateDate],120) 月份,[ArchiveType],总值=SUM([dTotalValue])
FROM 
    tb
GROUP BY  
    CONVERT(VARCHAR(7),[dCreateDate],120),[ArchiveType]
ORDER BY 
    CONVERT(VARCHAR(7),[dCreateDate],120)
----------------结果----------------------------
/*月份      ArchiveType 总值
------- ----------- ---------------------------------------
2008-01 Dead        374359.00
2008-02 Dead        123308.00
2008-04 Dead        25000.00
2008-05 Closed      335987.00
2008-05 Dead        143274.00(5 行受影响)
 
*/

Answer 6

SELECT CONVERT(VARCHAR(7),dCreateDate,120) AS '月份'
      ,ArchiveType
      ,SUM(dTotalValue) AS '总值'
FROM vDeal 
GROUP BY CONVERT(VARCHAR(7),dCreateDate,120),ArchiveType
ORDER BY '月份'

Answer 7

目的是：最后统计出每个月的 Dead,closed,reactivate 的总和数据。如果该月没有数据，就显示
2009-X月份 Dead 0
2009-X月份 Closed 0
2009-X月份 reactivate 0这是原数据，
2008-01-21 00:00:00 45303.00 Dead
2008-01-25 00:00:00 96462.00 Dead
2008-01-30 00:00:00 101555.00 Dead
2008-01-30 00:00:00 131039.00 Dead
2008-02-05 00:00:00 123308.00 Dead
2008-04-25 00:00:00 10000.00 Dead
2008-04-25 00:00:00 15000.00 Dead
2008-05-13 00:00:00 4500.00 Dead
2008-05-13 00:00:00 63500.00 Dead
2008-05-23 00:00:00 16200.00 Closed
2008-05-23 00:00:00 38340.00 Closed
2008-05-23 00:00:00 180299.00 Closed
2008-05-23 00:00:00 82248.00 Closed
2008-05-23 00:00:00 10600.00 Dead
2008-05-23 00:00:00 5900.00 Closed
2008-05-23 00:00:00 2400.00 Closed
2008-05-27 00:00:00 11016.00 Dead
2008-05-27 00:00:00 28350.00 Dead
2008-05-29 00:00:00 8745.00 Dead
2008-05-29 00:00:00 16563.00 Dead
2008-05-29 00:00:00 10600.00 Closed
2008-06-03 00:00:00 9400.00 Dead
2008-06-03 00:00:00 52800.00 Dead
2008-06-03 00:00:00 29848.00 Dead
2008-06-03 00:00:00 57025.00 Dead
2008-06-03 00:00:00 9700.00 Dead
2008-06-03 00:00:00 6250.00 Dead
2008-06-03 00:00:00 9500.00 Closed
2008-06-10 00:00:00 40000.00 Dead
2008-06-10 00:00:00 44000.00 Dead
2008-06-10 00:00:00 30000.00 Closed
2008-06-10 00:00:00 35000.00 Dead
2008-06-12 00:00:00 6625.00 Dead
2008-06-12 00:00:00 7632.00 Closed
2008-06-12 00:00:00 77210.00 Dead
2008-06-12 00:00:00 81288.00 Dead
2008-06-12 00:00:00 38854.00 Dead
2008-06-12 00:00:00 8639.00 Closed
2008-06-12 00:00:00 80194.00 Closed
2008-06-27 00:00:00 540000.00 Dead
2008-06-27 00:00:00 550000.00 Dead
2008-07-01 00:00:00 2400.00 Dead
2008-07-01 00:00:00 24282.00 Dead
2008-07-01 00:00:00 5371.00 Dead
2008-07-01 00:00:00 10840.00 Dead
2008-07-07 00:00:00 121325.00 Dead
2008-07-07 00:00:00 7600.00 Dead
2008-07-07 00:00:00 10600.00 Dead
2008-07-29 00:00:00 2216.00 Dead
2008-07-29 00:00:00 6140.00 Dead
2008-07-29 00:00:00 7200.00 Dead
2008-07-29 00:00:00 66402.00 Dead
2008-07-29 00:00:00 44518.00 Dead
2008-07-29 00:00:00 63230.00 Dead
2008-07-29 00:00:00 7200.00 Dead
2008-07-29 00:00:00 18750.00 Dead
2008-07-29 00:00:00 11100.00 Dead
2008-07-29 00:00:00 10300.00 Dead
2008-07-29 00:00:00 32760.00 Dead
2008-07-29 00:00:00 13000.00 Dead
2008-07-29 00:00:00 2500.00 Dead
2008-07-29 00:00:00 226678.00 Dead
2008-07-29 00:00:00 244977.00 Closed
2008-07-29 00:00:00 249570.00 Closed
2008-07-29 00:00:00 385305.00 Dead
2008-07-29 00:00:00 238030.00 Closed
2008-07-29 00:00:00 542778.00 Dead
2008-07-29 00:00:00 36056.00 Dead
2008-07-29 00:00:00 184512.00 Closed
2008-07-29 00:00:00 68020.00 Closed
2008-07-29 00:00:00 10000.00 Dead
2008-07-29 00:00:00 117690.00 Closed
2008-07-29 00:00:00 50373.00 Closed
2008-07-29 00:00:00 22300.00 Closed
2008-07-29 00:00:00 8100.00 Closed
2008-07-29 00:00:00 4000.00 Closed
2008-07-29 00:00:00 47000.00 Closed
2008-07-29 00:00:00 70000.00 Closed
2008-07-29 00:00:00 48000.00 Closed
2008-08-04 00:00:00 17000.00 reactivate
2008-08-04 00:00:00 51000.00 reactivate
2008-08-06 00:00:00 2600.00 Closed
2008-08-06 00:00:00 3600.00 Closed
2008-08-07 00:00:00 88000.00 Closed
2008-08-07 00:00:00 12000.00 Closed
2008-08-07 00:00:00 185000.00 Closed
2008-08-11 00:00:00 50251.00 Dead
2008-08-11 00:00:00 9203.00 Dead
2008-08-11 00:00:00 129884.00 Dead
2008-08-12 00:00:00 100000.00 Dead
2008-08-12 00:00:00 10000.00 Dead
2008-08-12 00:00:00 15000.00 Dead
2008-08-12 00:00:00 250000.00 Dead
2008-08-12 00:00:00 147885.00 Dead
2008-08-12 00:00:00 70000.00 Dead
2008-08-12 00:00:00 157100.00 Dead
2008-08-12 00:00:00 300000.00 Closed
2008-08-12 00:00:00 11000.00 Dead
2008-08-13 00:00:00 95000.00 Dead
2008-08-15 00:00:00 41165.00 Dead
2008-08-19 00:00:00 33500.00 Closed
2008-08-20 00:00:00 10000.00 Closed
2008-08-21 00:00:00 60000.00 Closed
2008-08-21 00:00:00 16000.00 Closed
2008-08-21 00:00:00 17000.00 Closed
2008-08-28 00:00:00 18500.00 Dead
2008-08-29 00:00:00 21000.00 Dead
2008-09-01 00:00:00 20000.00 Dead
2008-09-01 00:00:00 10000.00 Closed
2008-09-04 00:00:00 1590.00 Closed
2008-09-04 00:00:00 31189.00 Dead
2008-09-19 00:00:00 20750.00 Dead
2008-09-19 00:00:00 28250.00 Dead
2008-09-19 00:00:00 1000.00 Closed
2008-09-19 00:00:00 46887.00 Dead
2008-09-19 00:00:00 46887.00 Dead
2008-09-19 00:00:00 8360.00 Dead
2008-09-19 00:00:00 8360.00 Dead
2008-09-19 00:00:00 29023.00 Dead
2008-09-19 00:00:00 29023.00 Dead
2008-09-19 00:00:00 17500.00 Dead
2008-09-19 00:00:00 47000.00 Dead
2008-09-19 00:00:00 55000.00 Closed
2008-09-23 00:00:00 14125.00 Dead
2008-09-26 00:00:00 34989.00 Dead
2008-09-26 00:00:00 24485.00 Closed
2008-09-26 00:00:00 134754.00 Closed
2008-09-26 00:00:00 168157.00 Closed
2008-10-08 00:00:00 20000.00 Closed
2008-10-08 00:00:00 115000.00 Closed
2008-10-08 00:00:00 50000.00 Closed
2008-10-20 00:00:00 40816.00 Closed
2008-10-20 00:00:00 24600.00 Closed
2008-10-21 00:00:00 9050.00 Closed
2008-10-21 00:00:00 2500.00 Closed
2008-10-24 00:00:00 22000.00 Closed
2008-10-27 00:00:00 31206.00 Closed
2008-10-27 00:00:00 36000.00 Closed
2008-10-27 00:00:00 10000.00 Dead
2008-10-27 00:00:00 119874.00 Dead
2008-10-27 00:00:00 27163.00 Dead
2008-10-27 00:00:00 202669.00 Dead
2008-10-27 00:00:00 161121.00 Dead
2008-10-27 00:00:00 3050.00 Closed
2008-10-27 00:00:00 14850.00 Closed
2008-10-27 00:00:00 5466.00 Closed
2008-10-29 00:00:00 4000.00 Dead
2008-10-29 00:00:00 10000.00 Dead
2008-10-31 00:00:00 9350.00 Dead
2008-11-03 00:00:00 36672.00 Closed
2008-11-06 00:00:00 10000.00 Closed
2008-11-06 00:00:00 60000.00 Closed
2008-11-07 00:00:00 3200.00 Dead
2008-11-13 00:00:00 45000.00 Closed
2008-11-13 00:00:00 50310.00 Closed
2008-11-13 00:00:00 2850.00 Dead
2008-11-14 00:00:00 200000.00 Dead
2008-11-19 00:00:00 8000.00 Dead
2008-11-19 00:00:00 43000.00 Dead
2008-11-24 00:00:00 32571.00 Dead
2008-11-24 00:00:00 268158.00 Dead
2008-11-24 00:00:00 28391.00 Dead
2008-11-24 00:00:00 19136.00 Dead
2008-11-24 00:00:00 19136.00 Dead
2008-11-24 00:00:00 26972.00 Dead
2008-11-24 00:00:00 71472.00 Dead
2008-11-24 00:00:00 27040.00 Dead
2008-11-24 00:00:00 25781.00 Closed
2008-11-24 00:00:00 62276.00 Closed
2008-11-24 00:00:00 31423.00 Closed
2008-11-24 00:00:00 80346.00 Closed
2008-11-24 00:00:00 17953.00 Closed
2008-11-24 00:00:00 21223.00 Closed
2009-01-14 00:00:00 69000.00 Dead
2009-01-14 00:00:00 55000.00 Closed
2009-01-14 00:00:00 75000.00 Dead
2009-01-14 00:00:00 56000.00 Dead
2009-01-14 00:00:00 56000.00 Dead
2009-01-14 00:00:00 82000.00 Dead
2009-01-14 00:00:00 63000.00 Dead
2009-01-14 00:00:00 43351.00 Closed
2009-01-14 00:00:00 36494.00 Dead
2009-01-14 00:00:00 20185.00 Dead
2009-01-14 00:00:00 21288.00 Dead
2009-01-20 00:00:00 27800.00 Closed
2009-02-01 00:00:00 11956.00 Dead
2009-02-01 00:00:00 33261.00 Dead
2009-02-02 00:00:00 38000.00 Dead
2009-02-02 00:00:00 55000.00 Closed
2009-02-02 00:00:00 53000.00 Closed
2009-02-02 00:00:00 34000.00 reactivate
2009-02-02 00:00:00 18000.00 reactivate
2009-02-02 00:00:00 132000.00 reactivate
2009-02-02 00:00:00 142000.00 reactivate
2009-02-02 00:00:00 290000.00 reactivate
2009-02-02 00:00:00 293000.00 reactivate
2009-02-02 00:00:00 10000.00 Closed
2009-03-02 00:00:00 106382.00 Dead
2009-03-11 00:00:00 80000.00 Closed
2009-03-11 00:00:00 12500.00 Closed
2009-03-11 00:00:00 1700.00 Closed
2009-03-11 00:00:00 120000.00 Dead
2009-03-11 00:00:00 40000.00 Closed
2009-03-11 00:00:00 23104.00 Closed
2009-03-16 00:00:00 82000.00 Closed
2009-03-16 00:00:00 20000.00 reactivate
2009-03-18 00:00:00 3540.00 Closed
2009-03-18 00:00:00 11483.00 Closed
2009-03-18 00:00:00 17681.00 Dead
2009-03-25 00:00:00 142835.00 Dead
2009-03-25 00:00:00 45348.00 Dead
2009-03-25 00:00:00 43499.00 Dead
2009-03-25 00:00:00 30091.00 Dead
2009-03-25 00:00:00 68315.00 Dead
2009-03-27 00:00:00 45000.00 Dead
2009-03-27 00:00:00 181000.00 Dead
2009-03-27 00:00:00 80000.00 Dead
2009-04-15 00:00:00 28814.00 Dead
2009-04-15 00:00:00 2800.00 Dead
2009-05-22 00:00:00 25000.00 Closed
2009-05-22 00:00:00 108000.00 reactivate
2009-05-22 00:00:00 62700.00 reactivate
2009-05-22 00:00:00 50000.00 Dead
2009-05-22 00:00:00 45000.00 Dead
2009-05-22 00:00:00 4500.00 Closed
2009-05-26 00:00:00 4120.00 Dead
2009-05-26 00:00:00 33360.00 Closed
2009-05-26 00:00:00 54600.00 Closed
2009-06-08 00:00:00 10000.00 reactivate
2009-06-15 00:00:00 17500.00 Dead
2009-06-15 00:00:00 24625.00 Dead
2009-06-15 00:00:00 3600.00 Dead
2009-06-15 00:00:00 38160.00 Dead
2009-06-29 00:00:00 3715.00 Dead
2009-06-29 00:00:00 1813.00 Closed
2009-06-29 00:00:00 5226.00 Dead
2009-06-29 00:00:00 32480.00 Dead
2009-06-29 00:00:00 41513.00 Dead
2009-07-06 00:00:00 48000.00 Closed
2009-07-06 00:00:00 76000.00 reactivate
2009-07-10 00:00:00 6700.00 Dead
2009-07-10 00:00:00 11740.00 Dead
2009-07-16 00:00:00 36240.00 reactivate
2009-08-07 00:00:00 102887.00 reactivate
2009-08-07 00:00:00 196319.00 reactivate
2009-08-07 00:00:00 35260.00 reactivate
2009-08-11 00:00:00 19952.00 reactivate
2009-08-11 00:00:00 45832.00 reactivate
2009-08-17 00:00:00 21358.00 reactivate
2009-08-19 00:00:00 19559.00 reactivate
2009-08-20 00:00:00 3021.00 reactivate
2009-08-20 00:00:00 9434.00 reactivate
2009-08-20 00:00:00 11483.00 reactivate
2009-08-26 00:00:00 3021.00 Closed
2009-08-27 00:00:00 11368.00 reactivate
2009-09-01 00:00:00 29445.00 Dead
2009-09-01 00:00:00 24600.00 Closed
2009-09-01 00:00:00 19068.00 Dead

Answer 8

目的是：最后统计出每个月的 Dead,closed,reactivate 的总和数据。 如果该月没有数据，就显示 
2009-X月份 Dead 0 
2009-X月份 Closed 0 
2009-X月份 reactivate 0 

Answer 9

if object_id('[a]') is not null drop table [a]
go 
create table [a](dCreateDate datetime, dTotalValue  decimal(10,2),ArchiveType varchar(10))
insert [a] select
'2008-01-21 00:00:00', 45303.00 ,'Dead' union all select 
'2008-01-25 00:00:00', 96462.00 ,'Dead'  union all select 
'2008-01-30 00:00:00', 101555.00, 'Dead'  union all select 
'2008-01-30 00:00:00', 131039.00, 'Dead' union all select 
'2008-02-05 00:00:00', 123308.00, 'Dead'  union all select 
'2008-04-25 00:00:00', 10000.00 ,'Dead'  union all select 
'2008-04-25 00:00:00', 15000.00 ,'Dead'  union all select 
'2008-05-13 00:00:00', 4500.00 ,'Dead'  union all select 
'2008-05-13 00:00:00', 63500.00, 'Dead'  union all select 
'2008-05-23 00:00:00', 16200.00, 'Closed'  union all select 
'2008-05-23 00:00:00', 38340.00, 'Closed'
declare @date varchar(4)
set @date='2008'select [月份]=convert(varchar(7),mon,120),t.ArchiveType,sum(isnull(dTotalValue,0))as 总值 
from (
select [mon]=convert(datetime, @date+'-'+right('0'+ltrim(number),2)+'-01'),ArchiveType
from master..spt_values
cross join (select 'Closed' as ArchiveType union all select 'Dead' union all select 'reactivate')b
where type='p'
  and number between 1 and 12
)t
left join a
on a.ArchiveType=t.ArchiveType and convert(varchar(7),mon,120)=convert(varchar(7),dCreateDate,120)
group by convert(varchar(7),mon,120),t.ArchiveType 
order by convert(varchar(7),mon,120),t.ArchiveType 月份      ArchiveType 总值
------- ----------- ---------------------------------------
2008-01 Closed      0.00
2008-01 Dead        374359.00
2008-01 reactivate  0.00
2008-02 Closed      0.00
2008-02 Dead        123308.00
2008-02 reactivate  0.00
2008-03 Closed      0.00
2008-03 Dead        0.00
2008-03 reactivate  0.00
2008-04 Closed      0.00
2008-04 Dead        25000.00
2008-04 reactivate  0.00
2008-05 Closed      54540.00
2008-05 Dead        68000.00
2008-05 reactivate  0.00
2008-06 Closed      0.00
2008-06 Dead        0.00
2008-06 reactivate  0.00
2008-07 Closed      0.00
2008-07 Dead        0.00
2008-07 reactivate  0.00
2008-08 Closed      0.00
2008-08 Dead        0.00
2008-08 reactivate  0.00
2008-09 Closed      0.00
2008-09 Dead        0.00
2008-09 reactivate  0.00
2008-10 Closed      0.00
2008-10 Dead        0.00
2008-10 reactivate  0.00
2008-11 Closed      0.00
2008-11 Dead        0.00
2008-11 reactivate  0.00
2008-12 Closed      0.00
2008-12 Dead        0.00
2008-12 reactivate  0.00(36 行受影响)

Answer 10

-------------------------------------------
--  Author : liangCK 小梁
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-07 13:04:32
-------------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (dCreateDate DATETIME,dTotalValue NUMERIC(7,2),ArchiveType VARCHAR(10))
INSERT INTO @tb
SELECT '2008-01-21 00:00:00',45303.00,'Dead' UNION ALL
SELECT '2009-01-25 00:00:00',96462.00,'Closed'--SQL查询如下:SELECT DATEADD(month,B.number,A.date) AS date,
    C.ArchiveType
INTO #tmp
FROM (SELECT DISTINCT CONVERT(varchar(4),dCreateDate,120) + '-01-01' AS date
      FROM @tb) AS A
    JOIN master.dbo.spt_values AS B
ON B.type = 'p' AND B.number < 12
    CROSS JOIN (SELECT DISTINCT ArchiveType FROM @tb) AS CSELECT CONVERT(varchar(7),A.date,120) AS dCreateDate,A.ArchiveType,
    SUM(ISNULL(B.dTotalValue,0)) AS dTotalValue
FROM #tmp AS A
    LEFT JOIN @tb AS B
ON CONVERT(varchar(6),A.date,112) = CONVERT(varchar(6),B.dCreateDate,112)
    AND A.ArchiveType = B.ArchiveType
GROUP BY CONVERT(varchar(7),A.date,120),A.ArchiveType
ORDER BY 1;--删除测试
DROP TABLE #tmp;/*
dCreateDate ArchiveType dTotalValue
----------- ----------- ---------------------------------------
2008-01     Closed      0.00
2008-01     Dead        45303.00
2008-02     Closed      0.00
2008-02     Dead        0.00
2008-03     Closed      0.00
2008-03     Dead        0.00
2008-04     Closed      0.00
2008-04     Dead        0.00
2008-05     Closed      0.00
2008-05     Dead        0.00
2008-06     Closed      0.00
2008-06     Dead        0.00
2008-07     Closed      0.00
2008-07     Dead        0.00
2008-08     Closed      0.00
2008-08     Dead        0.00
2008-09     Closed      0.00
2008-09     Dead        0.00
2008-10     Closed      0.00
2008-10     Dead        0.00
2008-11     Closed      0.00
2008-11     Dead        0.00
2008-12     Closed      0.00
2008-12     Dead        0.00
2009-01     Closed      96462.00
2009-01     Dead        0.00
2009-02     Closed      0.00
2009-02     Dead        0.00
2009-03     Closed      0.00
2009-03     Dead        0.00
2009-04     Closed      0.00
2009-04     Dead        0.00
2009-05     Closed      0.00
2009-05     Dead        0.00
2009-06     Closed      0.00
2009-06     Dead        0.00
2009-07     Closed      0.00
2009-07     Dead        0.00
2009-08     Closed      0.00
2009-08     Dead        0.00
2009-09     Closed      0.00
2009-09     Dead        0.00
2009-10     Closed      0.00
2009-10     Dead        0.00
2009-11     Closed      0.00
2009-11     Dead        0.00
2009-12     Closed      0.00
2009-12     Dead        0.00(48 row(s) affected)
*/

Answer 11

DECLARE @BeginYear      INT
DECLARE @EndYear        INT
SELECT @BeginYear = 2008
      ,@EndYear   = 2009;WITH CET
AS
(
        SELECT CONVERT(VARCHAR(7),DATEADD(MONTH,number,LEFT(@BeginYear,4)+'-01-01'),120) AS '月份'
              ,B.ArchiveType
          FROM master.dbo.spt_values A,
                (SELECT ArchiveType = 'reactivate'
                  UNION ALL
                 SELECT 'closed'
                  UNION ALL
                 SELECT 'Dead') B
         WHERE type = 'P'
           AND number < (@EndYear + 1 - @BeginYear) * 12
)SELECT A.[月份]
      ,A.[ArchiveType]
      ,ISNULL(B.[总值],0) AS [总值]
FROM CET A
LEFT JOIN (SELECT CONVERT(VARCHAR(7),dCreateDate,120) AS '月份'
                 ,ArchiveType
                 ,SUM(dTotalValue) AS '总值'
             FROM vDeal 
            GROUP BY CONVERT(VARCHAR(7),dCreateDate,120),ArchiveType
           ) B ON A.[月份] = B.[月份] AND A.ArchiveType = B.ArchiveType
ORDER BY A.[月份],A.[ArchiveType]

Answer 12

--> 生成测试数据表:tbIf not object_id('[tb]') is null
    Drop table [tb]
Go
Create table [tb]([dCreateDate] Datetime,[dTotalValue] decimal(18,2),[ArchiveType] nvarchar(10))
Insert [tb]
Select '2008-01-21',45303.00,N'Dead' union all
Select '2008-01-25',96462.00,N'Dead' union all
Select '2008-01-30',101555.00,N'Dead' union all
Select '2008-01-30',131039.00,N'Dead' union all
Select '2008-02-05',123308.00,N'Dead' union all
Select '2008-04-25',10000.00,N'Dead' union all
Select '2008-04-25',15000.00,N'Dead' union all
Select '2008-05-13',4500.00,N'Dead' union all
Select '2008-05-13',63500.00,N'Dead' union all
Select '2008-05-23',16200.00,N'Closed' union all
Select '2008-05-23',38340.00,N'Closed' union all
Select '2008-05-23',180299.00,N'Closed' union all
Select '2008-05-23',82248.00,N'Closed' union all
Select '2008-05-23',10600.00,N'Dead' union all
Select '2008-05-23',5900.00,N'Closed' union all
Select '2008-05-23',2400.00,N'Closed' union all
Select '2008-05-27',11016.00,N'Dead' union all
Select '2008-05-27',28350.00,N'Dead' union all
Select '2008-05-29',8745.00,N'Dead' union all
Select '2008-05-29',16563.00,N'Dead' union all
Select '2008-05-29',10600.00,N'Closed'
union all select '2009-02-02 00:00:00', 34000.00, 'reactivate' 
union all select '2009-02-02 00:00:00', 18000.00, 'reactivate' 
union all select '2009-02-02 00:00:00', 132000.00, 'reactivate' 
union all select '2009-02-02 00:00:00', 142000.00, 'reactivate '
union all select '2009-02-02 00:00:00', 290000.00, 'reactivate '
union all select '2009-02-02 00:00:00', 293000.00, 'reactivate 'Go
--Select * from [tb]-->SQL查询如下:
SELECT a.[dCreateDate],a.[ArchiveType],总值=isnull(SUM([dTotalValue]),0)
    from 
       (
          select distinct CONVERT(varchar(7),[dCreateDate],120) as [dCreateDate],b.ArchiveType 
             from tb a  
              cross join
              (select distinct [ArchiveType] from tb)b
        )a
    left join tb b
       on a.[dCreateDate]=CONVERT(varchar(7),b.[dCreateDate],120) and a.ArchiveType=b.ArchiveType
       GROUP BY  a.[dCreateDate],a.[ArchiveType]
       ORDER BY 1  --按第一个字段排序
/*
说明--
1.从日期字段中取出年月数据！
2、从类型字段中取出类型并去重复处理！
3、将不重复的年月数据与类型做交叉连接
*/
/*
dCreateDate ArchiveType 总值
2008-01 Closed 0.00
2008-01 Dead 374359.00
2008-01 reactivate 0.00
2008-02 Closed 0.00
2008-02 Dead 123308.00
2008-02 reactivate 0.00
2008-04 Closed 0.00
2008-04 Dead 25000.00
2008-04 reactivate 0.00
2008-05 Closed 335987.00
2008-05 Dead 143274.00
2008-05 reactivate 0.00
2009-02 Closed 0.00
2009-02 Dead 0.00
2009-02 reactivate 909000.00
*
/

Answer 13

写得很好！declare @date varchar(4)
set @date='2009'select [月份]=convert(varchar(7),mon,120),t.ArchiveType,sum(isnull(dTotalValue,0))as 总值 
from (
    select [mon]=convert(datetime, @date+'-'+right('0'+ltrim(number),2)+'-01'),ArchiveType
    from master..spt_values
    cross join (select 'Closed' as ArchiveType union all select 'Dead' union all select 'reactivate') b
    where type='p'
          and number between 1 and 12
) t left join vdeal a
on a.ArchiveType=t.ArchiveType and convert(varchar(7),mon,120)=convert(varchar(7),dCreateDate,120)
group by convert(varchar(7),mon,120),t.ArchiveType 
order by convert(varchar(7),mon,120),t.ArchiveType -----------
就是不太明白 ，为什么用 master..spt_values表， 里面是什么意思呢。

Answer 14

create table Mon
(
ID int identity(1,1),
Mon varchar(6)
)
GODECLARE 
@BeginMonth varchar(6),
@EndMonth varchar(6)
SELECT
@BeginMonth='200801',
@EndMonth='200906'INSERT Mon(Mon)
SELECT
CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112)
FROM 
master..spt_values 
WHERE 
type='P' 
and 
DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01'
SELECT * FROM MonDROP TABLE Mon自己看看，呵呵

Answer 15

--先看这个子查询，就明白了
--利用系统表 master..spt_values 得到这样一个模板表，然后进行连接declare @date varchar(4)
set @date='2008'
    select [mon]=convert(datetime, @date+'-'+right('0'+ltrim(number),2)+'-01'),ArchiveType
    from master..spt_values
    cross join (select 'Closed' as ArchiveType union all select 'Dead' union all select 'reactivate')b
    where type='p'
          and number between 1 and 12mon                     ArchiveType
----------------------- -----------
2008-01-01 00:00:00.000 Closed
2008-01-01 00:00:00.000 Dead
2008-01-01 00:00:00.000 reactivate
2008-02-01 00:00:00.000 Closed
2008-02-01 00:00:00.000 Dead
2008-02-01 00:00:00.000 reactivate
2008-03-01 00:00:00.000 Closed
2008-03-01 00:00:00.000 Dead
2008-03-01 00:00:00.000 reactivate
2008-04-01 00:00:00.000 Closed
2008-04-01 00:00:00.000 Dead
2008-04-01 00:00:00.000 reactivate
2008-05-01 00:00:00.000 Closed
2008-05-01 00:00:00.000 Dead
2008-05-01 00:00:00.000 reactivate
2008-06-01 00:00:00.000 Closed
2008-06-01 00:00:00.000 Dead
2008-06-01 00:00:00.000 reactivate
2008-07-01 00:00:00.000 Closed
2008-07-01 00:00:00.000 Dead
2008-07-01 00:00:00.000 reactivate
2008-08-01 00:00:00.000 Closed
2008-08-01 00:00:00.000 Dead
2008-08-01 00:00:00.000 reactivate
2008-09-01 00:00:00.000 Closed
2008-09-01 00:00:00.000 Dead
2008-09-01 00:00:00.000 reactivate
2008-10-01 00:00:00.000 Closed
2008-10-01 00:00:00.000 Dead
2008-10-01 00:00:00.000 reactivate
2008-11-01 00:00:00.000 Closed
2008-11-01 00:00:00.000 Dead
2008-11-01 00:00:00.000 reactivate
2008-12-01 00:00:00.000 Closed
2008-12-01 00:00:00.000 Dead
2008-12-01 00:00:00.000 reactivate

Answer 16

请问你 怎么理解 
cross join 是笛卡儿乘积就是一张表的行数乘以另一张表的行数为什么会出来，你这个结果呢。

Answer 17

  m和n组成映射(关系)就有m*n条记录(因为每一个m都有n种情况,或者每一个n都有m种情况)

Answer 18

select ArchiveType,
  sum(case month(date) when 1 then amount else 0 end) [month1],
  sum(case month(date) when 2 then amount else 0 end) [month2]
from tb
group by ArchiveType这样应该可以满足你的要求，

Answer 19

select concat(year(dCreateDate),'-',month(dCreateDate)) 月份,ArchiveType,sum(dTotalValue)  总值 from vDeal group by year(dCreateDate),month(dCreateDate),ArchiveType

Answer 20

建一张全量数据的代码表，含3个字段，月份，ArchiveType，dTotalValue 其中dTotalValue全部为0，叫B表
然后用这张代码表左关联事实表，事实表select substr(B.dCreateDate,1,7),B.ArchiveType,SUM(A.dTotalValue) FROM B LEFT JOIN A WHERE substr(B.dCreateDate,1,7)=substr(B.dCreateDate,1,7) AND B.ArchiveType=A.B.ArchiveType
GROUP BY substr(B.dCreateDate,1,7),B.ArchiveType 就行了

Answer 21

真实数据中，如果没有该月数据时，
那个月的数据就不显示了。请问，我如何保证我做 “年统计”时，出来的是每个月的数据，没有的数据
打个比方说，2008年2月没有记录，就显示
2008-02    Closed    0.00
2008-02    Dead       0.00
2008-02    reactivate    0.00请问将如何改善呢，请指点一下。