MySQL的问题，纠结了一天了。麻烦那个高手大哥解决下。

Question

比如说我的表的结构
日期 金额
2010-04-04 50
2010-05-05 20 
2010-05-06 50
2010-05-04 100
2010-05-20 -50
2010-03-02 580
2010-02-01 60
2010-01-08 20查询后我要的结果是：
自然周 总额
1       0
2       0 
3       0
4       0
5       0
6       0
7       0
8       0
9       10 //比如说9周有总额是10 那就显示10 没有数据就显示0
10      50
实在不知道怎么写 麻烦给个解决的SQL语句
高手解决的！！ 在此小弟感谢万分

Answer 1

还有mysql的问题建议发到mysql版去...

Answer 2

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-09 09:18:01
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([日期] [datetime],[金额] [int])
INSERT INTO [tb]
SELECT '2010-04-04','50' UNION ALL
SELECT '2010-05-05','20' UNION ALL
SELECT '2010-05-06','50' UNION ALL
SELECT '2010-05-04','100' UNION ALL
SELECT '2010-05-20','-50' UNION ALL
SELECT '2010-03-02','580' UNION ALL
SELECT '2010-02-01','60' UNION ALL
SELECT '2010-01-08','20'--SELECT * FROM [tb]-->SQL查询如下:
select datepart(wk,日期) 自然周,
sum(case when datepart(w,日期+@@datefirst-1) between 1 and 5 then 金额 else 0 end) 金额 
from tb
group by datepart(wk,日期)
/*
自然周         金额
----------- -----------
2           20
6           60
10          580
15          0
19          170
21          -50(6 行受影响)
*/--还是要下面这样：
;with wk as 
(
select top(datediff(wk,'20100101','20101231')+1) 
wk=row_number()over(ORDER by getdate())
from sysobjects
)
select a.wk ,isnull(b.金额,0) 金额
from wk as a
outer apply(
select sum(case when datepart(w,日期+@@datefirst-1) between 1 and 5 then 金额 else 0 end) 金额
from tb b 
where a.wk=datepart(wk,b.日期)
) as b
/*
wk                   金额
-------------------- -----------
1                    0
2                    20
3                    0
4                    0
5                    0
6                    60
7                    0
8                    0
9                    0
10                   580
11                   0
12                   0
13                   0
14                   0
15                   0
16                   0
17                   0
18                   0
19                   170
20                   0
21                   -50
22                   0
23                   0
24                   0
25                   0
26                   0
27                   0
28                   0
29                   0
30                   0
31                   0
32                   0
33                   0
34                   0
35                   0
36                   0
37                   0
38                   0
39                   0
40                   0
41                   0
42                   0
43                   0
44                   0
45                   0
46                   0
47                   0
48                   0
49                   0
50                   0
51                   0
52                   0
53                   0(53 行受影响)
*/

Answer 3

如果结果没错，MySQL的建议你到相关版块转换一下代码