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

[no_com]

求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
________________________________________________________________________

[maxiaoyan1]

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

[lihonggen0]

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

[lihonggen0]

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

[lihonggen0]

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

[lxcc]

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

[victorycyz]

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

[lingfeng_314]

最麻烦，也是思路最简单的，就是先查询，然后统计。
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
这样行吗？看上去有点烦琐。

[wangbm]

同意 lingfeng_314(凌风)

[cwj88888]

思路：把日期字段取出来转换成有关的年，月，日字段后，再组合成该表

[no_com]

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

[阿建像熊猫]

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

[mycls]

表：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'