再求SQL语句(2003/07/25) ，很急。

[no_com]

再求SQL语句(2003/07/25) ，很急。
表：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（lihonggen0提供）：
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（beckhambobo(beckham)提供）:
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'
====================================================================
表：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
2003/05/08 150 BBB
2002/07/02 135 BBB
2002/06/08 165 BBB
+++++++++++++++++++++++
问：如何通过查询生成下表（就是把AAA/BBB按照时间[2002/07/02]汇总）？
Col_name col_DD(日) col_MM（当月） col_YY（当年）
AAA 30 70 150
BBB 135 135 300


如何把一个表中的所有商品按照品种分出各个品种当日、当月、当年的数量（提供思路）？
备注：
“当日”的情况下查询条件为A;“ 当月” 的情况下查询条件为B; “ 当年” 的情况下查询条件为C;且A<>B<>C.
我的想法：
还不如用三个查询语句分别取，但程序只能用一个sql查询语句，没有想法。
/

[nboys]

提供sqlserver参考:

select col_name,sum(case when datediff(d,col_date,getdate())=0 then col_int else 0 end) as [col_DD(日)],sum(case when datediff(m,col_date,getdate())=0 then col_int else 0 end) as [col_MM(当月)],sum(case when datediff(yy,col_date,getdate())=0 then col_int else 0 end) as [col_YY(当年)] from Tbl_1 group by col_name

[beckhambobo]

不是给了你这三个函数吗，比一个条复杂语句可读性更好:
http://expert.csdn.net/Expert/topic/2053/2053357.xml?temp=.2703668

楼主先把那三个函数执行一编，看SQL执行结果怎样。

[beckhambobo]

不是给了你这三个函数吗，比一个条复杂语句可读性更好:
http://expert.csdn.net/Expert/topic/2053/2053357.xml?temp=.2703668

楼主先把那三个函数执行一编，看SQL执行结果怎样。