Java开发网 - Re:一个解决不了的数据库查询，请高手帮助

Java开发网

您没有登录

» Java开发网 » Database/JDBC/SQL/JDO/Hibernate » Sybase

打印话题 寄给朋友 订阅主题

作者

Re:一个解决不了的数据库查询，请高手帮助 [Re:jsmile]

jsmile

Life is good

发贴: 127
积分: 52

于 2005-01-20 02:25

Thanks floater for your input!

After many hours of researching and trying, I finally found the solution:
SELECT Cost_Center AS "Cost Center",
SUM(Jan) AS Jan,
SUM(Feb) AS Feb ,
SUM(Mar) AS Mar,
SUM(Apr) AS Apr
FROM (SELECT Cost_Center,
CASE WHEN Month = '01'
THEN Cost
ELSE 0
END AS Jan,
CASE WHEN Month = '02'
THEN Cost
ELSE 0
END AS Feb,
CASE WHEN Month = '03'
THEN Cost
ELSE 0
END AS Mar,
CASE WHEN Month = '04'
THEN Cost
ELSE 0
END AS Apr
FROM dbo.monthly_cost ) monthly_cost
GROUP BY Cost_Center

It uses case statement to retrieve the right value into the month and then aggregates the values using sum function combined with group by clause.

It seems odd from the database design point of view in terms of the assignment. But I did learn some powerful SQL statements from the it. I think it was well worth the effect. Maybe it's useful later on for doing some quick reports against the database.

Do I deserve some points? Smile

话题树型展开

人气	标题	作者	字数	发贴时间
13000	一个解决不了的数据库查询，请高手帮助	jsmile	601	2005-01-19 08:18
10982	Re:一个解决不了的数据库查询，请高手帮助	jsmile	10	2005-01-19 22:11
11037	Re:一个解决不了的数据库查询，请高手帮助	floater	381	2005-01-20 00:26
11405	Re:一个解决不了的数据库查询，请高手帮助	jsmile	2066	2005-01-20 02:25
10833	Re:一个解决不了的数据库查询，请高手帮助	floater	75	2005-01-20 03:13
11013	Re:一个解决不了的数据库查询，请高手帮助	runaway	322	2005-02-04 10:50
11050	Re:一个解决不了的数据库查询，请高手帮助	jsmile	338	2005-02-04 23:31
12446	Re:一个解决不了的数据库查询，请高手帮助	liuyxit	1042	2005-03-04 15:32

已读帖子

新的帖子

被删除的帖子