求SQL语句(提供思路也可以)

Question

求SQL语句
________________________________________________________________________
表：Tbl_1
date        col_int   col_name
2002/06/01  80        AAA
2002/07/01  40        AAA
2002/07/02  20        AAA
2002/07/02  10        AAA
+++++++++++++++++++++++
问：如何通过查询生成下表（就是把AAA按照时间[2002/07/02]汇总）？
Col_name     col_DD(日)     col_MM（当月）    col_YY（当年）
AAA          30             70                150
________________________________________________________________________

Answer 1

select distinct col_name,
(select sum(Col_int) from t1 where dates='2002/07/02') col_dd,
(select sum(Col_int) from t1 where substr(dates,1,7)='2002/07') col_mm,
(select sum(Col_int) from t1 where substr(dates,1,4)='2002') col_yy
from t1

Answer 2

SELECT DISTINCT col_name,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE DAY(date) = DAY('2002/07/02')) AS col_dd,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE MONTH(date) = MONTH('2002/07/02')) AS col_mm,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE YEAR(date) = YEAR('2002/07/02')) AS col_yy
FROM Tbl_1

Answer 3

SELECT DISTINCT col_name,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE DAY(date) = DAY('2002/07/02')) AS col_dd,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE MONTH(date) = MONTH('2002/07/02')) AS col_mm,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE YEAR(date) = YEAR('2002/07/02')) AS col_yy
FROM Tbl_1

Answer 4

SELECT DISTINCT col_name,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE DAY(date) = DAY('2002/07/02')) AS col_dd,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE MONTH(date) = MONTH('2002/07/02')) AS col_mm,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE YEAR(date) = YEAR('2002/07/02')) AS col_yy
FROM Tbl_1

Answer 5

SELECT DISTINCT col_name,
          (SELECT SUM(Col_int)
         FROM t1
         WHERE dates= '2003/07/02') AS col_dd,
          (SELECT SUM(Col_int)
         FROM t1
         WHERE year(dates) = 2003 AND month(dates) = 7) AS col_mm,
          (SELECT SUM(Col_int)
         FROM t1
         WHERE year(dates) = 2003) AS col_yy
FROM t1

Answer 6

select col_name ,sum(iif([date]=#2002/7/2#,col_int,0)) as col_DD,
sum(iif(datepart("m",[date])=7 ,iif(datepart("yyyy",[date])=2002,col_int,0),0)) as col_MM,
sum(iif(datepart("yyyy",[date])=2002,col_int,0) as col_yy 
from tbl_1 group by col_name

Answer 7

最麻烦，也是思路最简单的，就是先查询，然后统计。
Select Col_name,sum(Col_DD) as Col_DD,Sum(Col_MM) as Col_MM,Sum(Col_YY) as Col_YY
From 
(Select Col_name,sum(Col_Int) as Col_DD,0 as Col_MM,0 as Col_YY 
From TB1_1 Where Convert(Char(10),Date,121)='日期' Group By Col_Name
Union
Select Col_name,0 as Col_DD,Sum(Col_Int) as Col_MM,0 as Col_YY 
From TB1_1 Where Convert(Char(7),Date,121)='日期' Group By Col_Name
Union
Select Col_name,0 as Col_DD,0 as Col_MM,Sum(Col_Int) as Col_YY 
From TB1_1 Where Convert(Char(4),Date,121)='日期' Group By Col_Name
)
as TempTable
Group By Col_Name
这样行吗？看上去有点烦琐。

Answer 8

我用的数据库是oracle的,如何更改上述sql语句适得能运行呢?

Answer 9

不用更改了呀。把日期字段全部读出来，然后用union的把年，月，日连起来。但字段的类型一定要一致，否则报错。

Answer 10

表：Tbl_1
col_date        col_int   col_name
2002/06/01  80        AAA
2002/07/01  40        AAA
2002/07/02  20        AAA
2002/07/02  10        AAA
+++++++++++++++++++++++
问：如何通过查询生成下表（就是把AAA按照时间[2002/07/02]汇总）？
Col_name     col_DD(日)     col_MM（当月）    col_YY（当年）
AAA         30             70                 150
已经解决。
SQL-Server：
SELECT DISTINCT col_name,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE DAY(col_date) = DAY('2002/07/02')) AS col_dd,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE MONTH(col_date) = MONTH('2002/07/02')) AS col_mm,
          (SELECT SUM(Col_int)
         FROM Tbl_1
         WHERE YEAR(col_date) = YEAR('2002/07/02')) AS col_yy
FROM Tbl_1
ORACLE:
SELECT * FROM (select distinct col_date,Col_name,sum(col_int) over(partition by to_char(col_date,'yyyymmdd')) col_DD,
sum(col_int) over(partition by to_char(col_date,'yyyymm')) col_MM,
sum(col_int) over(partition by to_char(col_date,'yyyy')) col_yy
from c_test) where col_date='2002/07/02'