[求助]求助Select

风雨中发布于 2006-12-13 20:18， 1052 次点击

有这样的一个表:
name object sorce
a ch 98
a en 34
a ph 34
b ch 88
b en 67
b ph 89

输出结果:
name ch en ph
a 98 34 34
b 88 67 89
写出实现的SQL语句.

14 回复

就这样自信2006-12-13 20:24

declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)

你看下是这样不！

风雨中2006-12-13 20:26

呵呵.有点看不明白,可以给一些解释吗?

weihongkun2006-12-13 23:43

select a.name,sum(a.ch) as [ch],sum(a.en) as [en],sum(a.ph) as [ph]
from(
select name,score as [ch],'' as [en],'' as [ph] from table where object='ch'
union
select name,'',score as [en],'' from table where object='en'
union
select name,'','',score as [ch] as [ph] from table where object='ph'
) a
group by a.name

棉花糖ONE2006-12-14 10:23

select name,sum(case object when ch then isnull(score,0) else o end) as ch,sum(case object when eh then isnull(score,0) else o end) as eh,sum(case object when ph then isnull(score,0) else o end) as ph from table group by name

weihongkun2006-12-14 11:46

还是你这个简单

weihongkun2006-12-17 18:10

不过我试了,你的好象运行结果不正确哦

棉花糖ONE2006-12-17 18:37

o改成0

weihongkun2006-12-18 18:16

呵呵,我今天写了一个报表就用到你的这个方式了,学到东西啦

#10

棉花糖ONE2006-12-18 18:22

呵呵,我是LouisXIV那边学的

#11

weihongkun2006-12-24 19:23

我有难题目啊,帮我解决

#12

棉花糖ONE2006-12-24 19:51

什么题目啊,看看我会不会,不会我也没办法

#13

getfzw2006-12-27 13:41

我试过上面的几种方法都不行的呀？？

#14

雨蒙珍珍2006-12-27 13:54

这个帖子很好学到很有用的知识！

#15

ninggang2006-12-29 10:52

优秀啊