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

Topic: 一个解决不了的数据库查询，请高手帮助

1.一个解决不了的数据库查询，请高手帮助

Posted by: jsmile
Posted on: 2005-01-19 08:18

表:

Cost Center  Month   Cost
--------------------------------------------------
00001   01   90000.00
00001   02   80000.00
00001   03   30000.00
00001   04   90000.00
00002   01   10000.00
00002   02   20000.00
00002   03   30000.00
00002   04   10000.00

用SQL 语句达到以下结果:

Cost Center  Jan   Feb   Mar   Apr
-----------------------------------------------------------------
00001    90000  80000  30000  90000
00002    10000  20000  30000  10000

多谢多谢！

2.Re:一个解决不了的数据库查询，请高手帮助 [Re: jsmile]	Copy to clipboard
Posted by: jsmile Posted on: 2005-01-19 22:11 盼啊 ......

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

Copy to clipboard

Posted by: floater
Posted on: 2005-01-20 00:26

not sure whether you can do this. I am not, and have never been, an DBA or SQL expert. Here is my logic: SQL is a tuple(row) based language, you are trying to make rows to columns, it's not likely. The only chance you have is that particular db may hae some functions for this.

If you are working with java, you may get the first one in SQL, and do it in java to get the second.

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

Copy to clipboard

Posted by: jsmile
Posted on: 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

5.Re:一个解决不了的数据库查询，请高手帮助 [Re: jsmile]	Copy to clipboard
Posted by: floater Posted on: 2005-01-20 03:13 dirty trick!!! Well, it works well, who cares, right? Good work, man.

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

Copy to clipboard

Posted by: runaway
Posted on: 2005-02-04 10:50

在oracle里面可以这样解决：
select cost_center,max(decode(rn,1,month)) Jan,max(decode(rn,2,month)) Feb,max(decode(rn,3,month)) Mar, max(decode(rn,4.month)) Apr from (select cost_center,month, row_number() over (partition by cost_center order by month) rn from monthly_cost ) group by cost_center;

使用的是8。1。7之后添加的analytic function。

7.Re:一个解决不了的数据库查询，请高手帮助 [Re: jsmile]	Copy to clipboard
Posted by: jsmile Posted on: 2005-02-04 23:31 Thanks very much! I haven't used Oracle database since 8i. The problem I encountered does't happen very often in real life and you can always find a way around it, such as stored procedures or Java. It gets trickier when the data type is not numeric and could have null value. In that case, those column/analytic functions won't work.

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

Copy to clipboard

Posted by: liuyxit
Posted on: 2005-03-04 15:32

SQLserver try this:
/*
Cost Center Month Cost
--------------------------------------------------
00001 01 90000.00
00001 02 80000.00
00001 03 30000.00
00001 04 90000.00
00002 01 10000.00
00002 02 20000.00
00002 03 30000.00
00002 04 10000.00

auther: LiuYXit
*/
create table #t (CostConter char(5),Month char(2),Cost numeric(10,2) )

insert into #t select '00001', '01',90000.00 union all
select '00001', '02',80000.00 union all
select '00001', '03',30000.00 union all
select '00001', '04',90000.00 union all
select '00002', '01',10000.00 union all
select '00002', '02',20000.00 union all
select '00002', '03',30000.00 union all
select '00002', '04',10000.00

declare @sql varchar(1000)

SET @sql = ''

SELECT @sql = @sql + ',SUM(CASE Month when '''
+ Month + ''' THEN Cost END) AS [' + Month+']'
FROM #t group by Month

EXEC ('SELECT CostConter ' + @sql + '
FROM #t
GROUP BY CostConter ')

drop table #t