SQL统计问题，立即解决，立即给分

Question

INQTY    OUTQTY            ITEMID             TRANSDATE        enddate
1.34000 1.250000000000 18K 2009-11-04       2009-11-04 
1.04000 1.050000000000 18K 2009-11-05       2009-11-05 
1.14000 1.130000000000 18K 2009-12-04  2009-12-04     
1.14000 1.130000000000 18K 2009-12-05 2009-12-05
5.10000 5.080000000000 18K 2010-01-04  2010-01-04
6.02000 5.620000000000 18K 2010-02-05 2010-02-05
5.16000 5.160000000000 18K               2010-03-01 2010-01-01 
5.16000 5.160000000000 18K               2010-03-02 2010-01-02
5.16000 5.160000000000 18K               2010-03-03 2010-03-03
...      ...                ...              ...        ....
5.16000 5.160000000000 18K               2010-03-18 2010-03-18要得出效果是：transdate 小于当年（2010）年当月（03）之前的  都进行月统计inqty,同样enddate小于当年2010当月（03）之前进行月份统计outqty,当年当月的就不用统计效果图：
         INQTY    OUTQTY                 2009-11  2.38      2.30
2009-12  2.28      2.26
...      ...       ...
2010-2   6.02      5.62
2010-3-1 5.16      5.16
....     ...      ...
2010-3-18 5.16      5.16
  

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-03-18 13:50:42
-- Verstion:
--      Microsoft SQL Server  2000 - 8.00.2055 (Intel X86) 
-- Dec 16 2008 19:46:53 
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition 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]([INQTY] numeric(6,5),[OUTQTY] numeric(13,12),[ITEMID] varchar(3),[TRANSDATE] datetime,[enddate] datetime)
insert [tb]
select 1.34000,1.250000000000,'18K','2009-11-04','2009-11-04' union all
select 1.04000,1.050000000000,'18K','2009-11-05','2009-11-05' union all
select 1.14000,1.130000000000,'18K','2009-12-04','2009-12-04' union all
select 1.14000,1.130000000000,'18K','2009-12-05','2009-12-05' union all
select 5.10000,5.080000000000,'18K','2010-01-04','2010-01-04' union all
select 6.02000,5.620000000000,'18K','2010-02-05','2010-02-05' union all
select 5.16000,5.160000000000,'18K','2010-03-01','2010-01-01' union all
select 5.16000,5.160000000000,'18K','2010-03-02','2010-01-02' union all
select 5.16000,5.160000000000,'18K','2010-03-03','2010-03-03' union all
select 5.16000,5.160000000000,'18K','2010-03-18','2010-03-18'
--------------开始查询--------------------------
select
 convert(varchar(10),TRANSDATE,120),convert(varchar(10),enddate,120),
 sum(case when datediff(mm,transdate,getdate())>0 then inqty else 0 end) as inqty,
 sum(case when datediff(mm,enddate,getdate())>0 then outqty else 0 end) as outqty
from
 [tb]
group by
 convert(varchar(10),TRANSDATE,120),convert(varchar(10),enddate,120)
----------------结果----------------------------
/*                      inqty                                    outqty                                   
---------- ---------- ---------------------------------------- ---------------------------------------- 
2009-11-04 2009-11-04 1.34000                                  1.250000000000
2009-11-05 2009-11-05 1.04000                                  1.050000000000
2009-12-04 2009-12-04 1.14000                                  1.130000000000
2009-12-05 2009-12-05 1.14000                                  1.130000000000
2010-03-01 2010-01-01 .00000                                   5.160000000000
2010-03-02 2010-01-02 .00000                                   5.160000000000
2010-01-04 2010-01-04 5.10000                                  5.080000000000
2010-02-05 2010-02-05 6.02000                                  5.620000000000
2010-03-03 2010-03-03 .00000                                   .000000000000
2010-03-18 2010-03-18 .00000                                   .000000000000（所影响的行数为 10 行）
 
*/

Answer 2

SELECT TIME,inqty ,OUTQTY 
FROM 
(
SELECT 
CONVERT(VARCHAR(7),TRANSDATE ,120)AS TIME,
SUM(inqty)inqty 
FROM TB   
WHERE CONVERT(VARCHAR(7),TRANSDATE ,120)<CONVERT(VARCHAR(7),GETDATE(),120)
GROUP BY CONVERT(VARCHAR(7),TRANSDATE ,120)
)A
,(
SELECT 
CONVERT(VARCHAR(7),enddate,120)AS TIME,
SUM(OUTQTY )OUTQTY 
FROM TB   
WHERE CONVERT(VARCHAR(7),enddate,120)<CONVERT(VARCHAR(7),GETDATE(),120)
GROUP BY CONVERT(VARCHAR(7),enddate,120))B WHERE A.TIME=B.TIMEUNION ALLSELECT CONVERT(VARCHAR(7),enddate,120) TIME ,inqty ,OUTQTY  
FROM TB 
WHERE CONVERT(VARCHAR(7),enddate,120)>=CONVERT(VARCHAR(7),GETDATE(),120)

Answer 3

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([INQTY] numeric(6,5),[OUTQTY] numeric(13,12),[ITEMID] varchar(3),[TRANSDATE] datetime,[enddate] datetime)
insert [tb]
select 1.34000,1.250000000000,'18K','2009-11-04','2009-11-04' union all
select 1.04000,1.050000000000,'18K','2009-11-05','2009-11-05' union all
select 1.14000,1.130000000000,'18K','2009-12-04','2009-12-04' union all
select 1.14000,1.130000000000,'18K','2009-12-05','2009-12-05' union all
select 5.10000,5.080000000000,'18K','2010-01-04','2010-01-04' union all
select 6.02000,5.620000000000,'18K','2010-02-05','2010-02-05' union all
select 5.16000,5.160000000000,'18K','2010-03-01','2010-01-01' union all
select 5.16000,5.160000000000,'18K','2010-03-02','2010-01-02' union all
select 5.16000,5.160000000000,'18K','2010-03-03','2010-03-03' union all
select 5.16000,5.160000000000,'18K','2010-03-18','2010-03-18'SELECT A.TIME,inqty ,OUTQTY 
FROM 
(
SELECT 
CONVERT(VARCHAR(7),TRANSDATE ,120)AS TIME,
SUM(inqty)inqty 
FROM TB   
WHERE CONVERT(VARCHAR(7),TRANSDATE ,120)<CONVERT(VARCHAR(7),GETDATE(),120)
GROUP BY CONVERT(VARCHAR(7),TRANSDATE ,120)
)A
,(
SELECT 
CONVERT(VARCHAR(7),enddate,120)AS TIME,
SUM(OUTQTY )OUTQTY 
FROM TB   
WHERE CONVERT(VARCHAR(7),enddate,120)<CONVERT(VARCHAR(7),GETDATE(),120)
GROUP BY CONVERT(VARCHAR(7),enddate,120))B WHERE A.TIME=B.TIMEUNION ALLSELECT CONVERT(VARCHAR(7),enddate,120) TIME ,inqty ,OUTQTY  
FROM TB 
WHERE CONVERT(VARCHAR(7),enddate,120)>=CONVERT(VARCHAR(7),GETDATE(),120)
（所影响的行数为 10 行）TIME    inqty                                    OUTQTY                                   
------- ---------------------------------------- ---------------------------------------- 
2009-11 2.38000                                  2.300000000000
2009-12 2.28000                                  2.260000000000
2010-01 5.10000                                  15.400000000000
2010-02 6.02000                                  5.620000000000
2010-03 5.16000                                  5.160000000000
2010-03 5.16000                                  5.160000000000（所影响的行数为 6 行）

Answer 4

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([INQTY] numeric(6,5),[OUTQTY] numeric(13,12),[ITEMID] varchar(3),[TRANSDATE] datetime,[enddate] datetime)
insert [tb]
select 1.34000,1.250000000000,'18K','2009-11-04','2009-11-04' union all
select 1.04000,1.050000000000,'18K','2009-11-05','2009-11-05' union all
select 1.14000,1.130000000000,'18K','2009-12-04','2009-12-04' union all
select 1.14000,1.130000000000,'18K','2009-12-05','2009-12-05' union all
select 5.10000,5.080000000000,'18K','2010-01-04','2010-01-04' union all
select 6.02000,5.620000000000,'18K','2010-02-05','2010-02-05' union all
select 5.16000,5.160000000000,'18K','2010-03-01','2010-01-01' union all
select 5.16000,5.160000000000,'18K','2010-03-02','2010-01-02' union all
select 5.16000,5.160000000000,'18K','2010-03-03','2010-03-03' union all
select 5.16000,5.160000000000,'18K','2010-03-18','2010-03-18'SELECT A.TIME,inqty ,OUTQTY 
FROM 
(
SELECT 
CONVERT(VARCHAR(7),TRANSDATE ,120)AS TIME,
SUM(inqty)inqty 
FROM TB   
WHERE CONVERT(VARCHAR(7),TRANSDATE ,120)<CONVERT(VARCHAR(7),GETDATE(),120)
GROUP BY CONVERT(VARCHAR(7),TRANSDATE ,120)
)A
,(
SELECT 
CONVERT(VARCHAR(7),enddate,120)AS TIME,
SUM(OUTQTY )OUTQTY 
FROM TB   
WHERE CONVERT(VARCHAR(7),enddate,120)<CONVERT(VARCHAR(7),GETDATE(),120)
GROUP BY CONVERT(VARCHAR(7),enddate,120))B WHERE A.TIME=B.TIMEUNION ALLSELECT CONVERT(VARCHAR(10),enddate,120) TIME ,inqty ,OUTQTY  
FROM TB 
WHERE CONVERT(VARCHAR(7),enddate,120)>=CONVERT(VARCHAR(7),GETDATE(),120)
（所影响的行数为 10 行）TIME       inqty                                    OUTQTY                                   
---------- ---------------------------------------- ---------------------------------------- 
2009-11    2.38000                                  2.300000000000
2009-12    2.28000                                  2.260000000000
2010-01    5.10000                                  15.400000000000
2010-02    6.02000                                  5.620000000000
2010-03-03 5.16000                                  5.160000000000
2010-03-18 5.16000                                  5.160000000000（所影响的行数为 6 行）

Answer 5

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([INQTY] numeric(6,5),[OUTQTY] numeric(13,12),[ITEMID] varchar(3),[TRANSDATE] datetime,[enddate] datetime)
insert [tb]
select 1.34000,1.250000000000,'18K','2009-11-04','2009-11-04' union all
select 1.04000,1.050000000000,'18K','2009-11-05','2009-11-05' union all
select 1.14000,1.130000000000,'18K','2009-12-04','2009-12-04' union all
select 1.14000,1.130000000000,'18K','2009-12-05','2009-12-05' union all
select 5.10000,5.080000000000,'18K','2010-01-04','2010-01-04' union all
select 6.02000,5.620000000000,'18K','2010-02-05','2010-02-05' union all
select 5.16000,5.160000000000,'18K','2010-03-01','2010-01-01' union all
select 5.16000,5.160000000000,'18K','2010-03-02','2010-01-02' union all
select 5.16000,5.160000000000,'18K','2010-03-03','2010-03-03' union all
select 5.16000,5.160000000000,'18K','2010-03-18','2010-03-18'SELECT A.TIME,inqty ,OUTQTY 
FROM 
(
SELECT 
CONVERT(VARCHAR(7),TRANSDATE ,120)AS TIME,
SUM(inqty)inqty 
FROM TB   
WHERE CONVERT(VARCHAR(7),TRANSDATE ,120)<CONVERT(VARCHAR(7),GETDATE(),120)
GROUP BY CONVERT(VARCHAR(7),TRANSDATE ,120)
)A
,(
SELECT 
CONVERT(VARCHAR(7),enddate,120)AS TIME,
SUM(OUTQTY )OUTQTY 
FROM TB   
WHERE CONVERT(VARCHAR(7),enddate,120)<CONVERT(VARCHAR(7),GETDATE(),120)
GROUP BY CONVERT(VARCHAR(7),enddate,120))B WHERE A.TIME=B.TIMEUNION ALL
SELECT CONVERT(VARCHAR(10),TRANSDATE,120) TIME ,inqty ,OUTQTY  
FROM TB 
WHERE CONVERT(VARCHAR(7),TRANSDATE,120)>=CONVERT(VARCHAR(7),GETDATE(),120)
UNION ALL
SELECT CONVERT(VARCHAR(10),enddate,120) TIME ,inqty ,OUTQTY  
FROM TB 
WHERE CONVERT(VARCHAR(7),enddate,120)>=CONVERT(VARCHAR(7),GETDATE(),120)
（所影响的行数为 10 行）TIME       inqty                                    OUTQTY                                   
---------- ---------------------------------------- ---------------------------------------- 
2009-11    2.38000                                  2.300000000000
2009-12    2.28000                                  2.260000000000
2010-01    5.10000                                  15.400000000000
2010-02    6.02000                                  5.620000000000
2010-03-01 5.16000                                  5.160000000000
2010-03-02 5.16000                                  5.160000000000
2010-03-03 5.16000                                  5.160000000000
2010-03-18 5.16000                                  5.160000000000
2010-03-03 5.16000                                  5.160000000000
2010-03-18 5.16000                                  5.160000000000（所影响的行数为 10 行）