select result as id form tablename.<<<<不知道對不對
解决方案 »
- 请教一个触发器问题
- 100分求一个比较难的SQl 语句. (最好能在VF中实现)
- 请教链接服务器中的表为什么不能生成视图?
- 求一句SQL统计语句!
- 要找出这样的记录,怎么写呢?
- 如何查看SQLServer操作记录
- 字符串参数存储过程问题求解
- 数据库访问问题:一个网内三台机器,互相找不到数据库源
- 如何配置SQL Server 2000使得其支持JDBC开发!!!
- 为什么我的PROCEDURE BUILDER连不了数据库?
- [[心急]]请教一个基础问题
- Could not allocate space for object 'purls' in datebase 'mis' because the 'PRIMARY' filegroup is full 是什么错误
Create Table # (ID varchar(10),RESULT varchar(20))
insert into # select 4,'AB'
union all select 5,'CD'--动态SQL
declare @s varchar(2000)
set @s=''
select @s=@s+',['+ID+']=max(case when ID='''+ID+''' then RESULT else '''' end) '
from #
GROUP BY ID
set @s='SELECT '+stuff(@s,1,1,'')+' From # '
print @s
exec(@s)
--结果
4 5
-------------------- --------------------
AB CD--删除测试环境
DROP TABLE #
select sum(case id when 4 then result else 0 end) as [4],
sum(case id when 5 then result else 0 end) as [5]
from table1
group by h但是现在RESULT不是数字,所以不知道该怎么写了
To:zlp321002(飘过)
4,5 是不固定的。能用交叉数据报表的形式写吗?我需要那样写的语句。谢谢
4,5 是不固定的。能用交叉数据报表的形式写吗?我需要那样写的语句。谢谢
-----------------------------------------------------------------------
--我用的动态SQL,就是交叉表,4,5我没有固定,你测试下??
insert into #tmp(MatchID,HostTeam) select MatchID,HostTeam from MatchScene where WheelID = 1
declare @sql varchar(8000)
set @sql='select UserName'
select @sql=@sql
+',sum(case MatchID when '''+cast(MatchID as varchar(10))+''' then Result else 0 end) [Result'+ cast(ID as varchar(5))+']'
from (select ID,MatchID,HostTeam from #tmp) ab
select @sql=@sql+' from (select a.UserID as UserID,UserName,MatchID,Guess,Result,replace(Guess,Result,''<font color=red>''+Result+''</font>'') as showResult from UserMoneySevenDetail a
left outer join Users b on a.UserID = b.UserID
where WheelID = 1 ) as ttt group by UserName'
print(@sql)
exec(@sql)
drop table #tmp
+',max(case when MatchID = '''+ MatchID + ''' then ShowResult else '''' end) ['+ cast(HostTeam as varchar(50))+']'
--你最好print @sql 拼SQL,或者你参考我给你写的例子!!!
1 4 AB
1 5 CD
2 4 EF
2 5 gh获得结果:
UserID 4 5
1 AB CD
2 EF GH语句怎么实现?
http://community.csdn.net/Expert/topic/4345/4345763.xml?temp=.9756586
UserID ID RESULT
1 4 AB
1 5 CD
2 4 EF
2 5 gh结果:
UserID 4 5
1 AB CD
2 EF GH----测试环境
Create Table # (UserID varchar(10),ID varchar(10),RESULT varchar(20))
insert into # select 1,4,'AB'
union all select 1,5,'CD'
union all select 2,4,'EF'
union all select 2,5,'gh'
--动态SQL
declare @s varchar(2000)
set @s='SELECT UserID'
select @s=@s+',['+ID+']=max(case when ID='''+ID+''' then RESULT else '''' end) '
from #
GROUP BY ID
set @s=@s+' From # GROUP BY UserID'
exec(@s)
--结果
UserID 4 5
---------- -------------------- --------------------
1 AB CD
2 EF gh--删除测试环境
Drop Table #