SQL 查询情况 ( 积分: 100 )

有一表，内容如下：<br>code&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pay_days<br>0001&nbsp;&nbsp;&nbsp;&nbsp;2007/9/01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;50<br>0002&nbsp;&nbsp;&nbsp;&nbsp;2007/9/01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;60<br>0001&nbsp;&nbsp;&nbsp;&nbsp;2007/9/02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;55.5<br>0002&nbsp;&nbsp;&nbsp;&nbsp;2007/9/02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;30<br>0001&nbsp;&nbsp;&nbsp;&nbsp;2007/9/03&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10<br>0002&nbsp;&nbsp;&nbsp;&nbsp;2007/9/03&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;60<br>........................&nbsp;&nbsp;&nbsp;<br>怎样查询成这种效果<br>code&nbsp;&nbsp;&nbsp;days1,days2,days3,.......days30<br>0001&nbsp;&nbsp;&nbsp;&nbsp;50&nbsp;&nbsp;&nbsp;&nbsp;55.5&nbsp;&nbsp;10&nbsp;&nbsp;..............<br>0002&nbsp;&nbsp;&nbsp;&nbsp;60&nbsp;&nbsp;&nbsp;&nbsp;30&nbsp;&nbsp;&nbsp;&nbsp;60&nbsp;&nbsp;..............<br>......................................<br>自己用很笨得办法是更新的形式到临时表，但觉得效率太差了，一个月的记录就用时差不多两分钟

用交叉列的方法查询就可以了,你参考我下面的这段代码(MS&nbsp;SQL中执行),然后再进行一下修改就可以用在你的表上了<br>Declare&nbsp;@s&nbsp;nvarchar(4000)<br>Select&nbsp;@s=''<br>Select&nbsp;@s=@s+','+QuoteName(xtype)<br>&nbsp;+'=IsNull(Sum(Case&nbsp;xtype&nbsp;when&nbsp;'+QuoteName(xtype,'''')<br>&nbsp;+'&nbsp;then&nbsp;1&nbsp;end),0)'<br>from&nbsp;sysobjects<br>group&nbsp;&nbsp;&nbsp;by&nbsp;&nbsp;&nbsp;xtype<br><br>Exec('Select&nbsp;Status'+@s+'&nbsp;from&nbsp;sysobjects&nbsp;where&nbsp;name&nbsp;like&nbsp;''sys%''&nbsp;group&nbsp;by&nbsp;status')

以下的测试通过<br>create&nbsp;table&nbsp;#temp&nbsp;(code&nbsp;varchar(20)&nbsp;,&nbsp;days&nbsp;datetime&nbsp;,&nbsp;pay_days&nbsp;float)<br>insert&nbsp;#temp(code,days,pay_days)&nbsp;values&nbsp;('0001','2007/9/01',50)<br>insert&nbsp;#temp(code,days,pay_days)&nbsp;values&nbsp;('0002','2007/9/01',60)<br>insert&nbsp;#temp(code,days,pay_days)&nbsp;values&nbsp;('0001','2007/9/02',55.5)<br>insert&nbsp;#temp(code,days,pay_days)&nbsp;values&nbsp;('0002','2007/9/02',30)<br>insert&nbsp;#temp(code,days,pay_days)&nbsp;values&nbsp;('0001','2007/9/03',10)<br>insert&nbsp;#temp(code,days,pay_days)&nbsp;values&nbsp;('0002','2007/9/03',60)<br><br>select&nbsp;*,day(days),'days'+convert(varchar(20),day(days))&nbsp;from&nbsp;#temp<br><br><br>DECLARE&nbsp;@SQL&nbsp;VARCHAR(8000)&nbsp;<br>&nbsp;set&nbsp;@sql&nbsp;=&nbsp;'select&nbsp;code'&nbsp;<br>&nbsp;select&nbsp;@sql&nbsp;=&nbsp;@sql&nbsp;+&nbsp;',sum(case&nbsp;''days''+convert(varchar(20),day(days))&nbsp;when&nbsp;'''+'days'<br>+convert(varchar(20),day(days))+'''&nbsp;then&nbsp;pay_days&nbsp;end)&nbsp;['+'days'+convert(varchar(20),day(days))+']'&nbsp;<br>&nbsp;from&nbsp;(select&nbsp;distinct&nbsp;days&nbsp;from&nbsp;#temp)&nbsp;as&nbsp;a&nbsp;<br>&nbsp;select&nbsp;@sql&nbsp;=&nbsp;@sql+'&nbsp;from&nbsp;#temp&nbsp;group&nbsp;by&nbsp;code'&nbsp;<br>exec(@sql)&nbsp;<br>drop&nbsp;table&nbsp;#temp

问题解决，感谢俩位富翁的参与，难得了，现在DFW都挺冷的。