Select 帐号ZH,
sum(case when 交易代码JYDM = '9999' then 交易金额JYJE else 0 end) as 9999交易总金额,
sum(case when 交易代码JYDM = '9998' then 交易金额JYJE else 0 end) as 9998交易总金额,
sum(case when 交易代码JYDM = '9995' then 交易金额JYJE else 0 end) as 9995交易总金额,
sum(case when 交易代码JYDM = '9990' then 交易金额JYJE else 0 end) as 9990交易总金额,
sum(case when 交易代码JYDM = '9997' then 交易金额JYJE else 0 end) as 9997交易总金额
from yourTable group by 帐号ZH
sum(case when 交易代码JYDM = '9999' then 交易金额JYJE else 0 end) as 9999交易总金额,
sum(case when 交易代码JYDM = '9998' then 交易金额JYJE else 0 end) as 9998交易总金额,
sum(case when 交易代码JYDM = '9995' then 交易金额JYJE else 0 end) as 9995交易总金额,
sum(case when 交易代码JYDM = '9990' then 交易金额JYJE else 0 end) as 9990交易总金额,
sum(case when 交易代码JYDM = '9997' then 交易金额JYJE else 0 end) as 9997交易总金额
from yourTable group by 帐号ZH
set @sql = 'select ZH,'select @sql = @sql + 'sum(case JYDM when '''+JYDM +'''
then JYJE else 0 end) as ['+JYDM +'],'
from (select distinct JYDM from 库表) aselect @sql = left(@sql,len(@sql)-1) + ' from 库表 group by ZH'exec(@sql)
go
sum(case when JYDM = '9999' then JYJE else 0 end) as 9999交易总金额,
sum(case when JYDM = '9998' then JYJE else 0 end) as 9998交易总金额,
sum(case when JYDM = '9995' then JYJE else 0 end) as 9995交易总金额
from yourTable group by ZH
OR:declare @sql varchar(8000)
set @sql = 'select ZH,'
select @sql = @sql + 'sum(case JYDM when '''+JYDM+'''
then JYJE else 0 end) '+JYDM+'交易总金额,'
from (select distinct JYDM from 你的表) as aselect @sql = left(@sql,len(@sql)-1) + ' from 你的表'exec(@sql)
SELECT table.ZH
FROM table
GROUP BY table.ZH
PIVOT table.JYDM;不知道 SQL有否此功能?
1、建立交易代码表如:select distinct JYDM into 交易代码表 from 表2、
declare @sql varchar(8000)
set @sql = 'select ZH'select @sql = @sql + ',sum(case JYDM when '''+JYDM +''' then JYJE else 0 end) ['+JYDM +']' from 交易代码表exec(@sql+ ' from 表 group by ZH')
go
我试用了如下格式:
Select ZH,
sum(case when JYDM = '9999' then JYJE else 0 end) as 9999交易总金额,
from yourTable group by ZH查询设计器不支持 CASE SQL 构造。
请问怎么回事?
是什么意思?
',sum(case JYDM when ', ''' then JYJE else 0 end) [' 是字符串
''+JYDM +'' 是什么意思?
例如:用GUEST的ID登陆上来的用户只能看到帐号0001的资料信息,其他的都不显示,
我在登陆的时已经记录下用GUEST登陆的标识标为1,在数据库中增加一拦标识,帐号0001的标识就为“1”,
显示的时候双方碰头,当都为1的就显示出来,
这样做,怎么解决,有没有其他的好的办法!