SQL语句问题 ( 积分: 100 )

已知表格A<br>&nbsp;&nbsp;&nbsp;字段:&nbsp;A1&nbsp;A2&nbsp;B1&nbsp;B2&nbsp;B3<br>&nbsp;&nbsp;&nbsp;内容：<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;2&nbsp;&nbsp;3&nbsp;&nbsp;4&nbsp;&nbsp;4<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;3&nbsp;&nbsp;3&nbsp;&nbsp;2&nbsp;&nbsp;1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;2&nbsp;&nbsp;3&nbsp;&nbsp;4&nbsp;&nbsp;1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5&nbsp;&nbsp;3&nbsp;&nbsp;4&nbsp;&nbsp;4&nbsp;&nbsp;4<br>&nbsp;&nbsp;希望得到如下统计表B<br>&nbsp;&nbsp;&nbsp;&nbsp;字段:&nbsp;1&nbsp;&nbsp;2&nbsp;&nbsp;3&nbsp;&nbsp;4&nbsp;&nbsp;5<br>&nbsp;&nbsp;&nbsp;&nbsp;内容：<br>&nbsp;A1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;1&nbsp;&nbsp;1&nbsp;&nbsp;0&nbsp;&nbsp;1<br>&nbsp;A2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;2&nbsp;&nbsp;2&nbsp;&nbsp;0&nbsp;&nbsp;0<br>&nbsp;B1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;0&nbsp;&nbsp;3&nbsp;&nbsp;1&nbsp;&nbsp;0<br>&nbsp;B2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;1&nbsp;&nbsp;0&nbsp;&nbsp;3&nbsp;&nbsp;0<br>&nbsp;B3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;0&nbsp;&nbsp;0&nbsp;&nbsp;2&nbsp;&nbsp;0<br><br>环境:SQL&nbsp;SERVER&nbsp;2000

按照什么规律得到B数据？

看明白了，<br>是统计A1&nbsp;A2&nbsp;B1&nbsp;B2&nbsp;B3<br>各出现1&nbsp;&nbsp;2&nbsp;&nbsp;3&nbsp;&nbsp;4&nbsp;&nbsp;5的次数

select&nbsp;<br> [1]=sum(case&nbsp;a1&nbsp;when&nbsp;1&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [2]=sum(case&nbsp;a1&nbsp;when&nbsp;2&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [3]=sum(case&nbsp;a1&nbsp;when&nbsp;3&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [4]=sum(case&nbsp;a1&nbsp;when&nbsp;4&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [5]=sum(case&nbsp;a1&nbsp;when&nbsp;5&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end)<br>from&nbsp;tb<br>union&nbsp;all<br>select&nbsp;<br> [1]=sum(case&nbsp;a2&nbsp;when&nbsp;1&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [2]=sum(case&nbsp;a2&nbsp;when&nbsp;2&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [3]=sum(case&nbsp;a2&nbsp;when&nbsp;3&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [4]=sum(case&nbsp;a2&nbsp;when&nbsp;4&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [5]=sum(case&nbsp;a2&nbsp;when&nbsp;5&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end)<br>from&nbsp;tb<br>union&nbsp;all<br>select&nbsp;<br> [1]=sum(case&nbsp;b1&nbsp;when&nbsp;1&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [2]=sum(case&nbsp;b1&nbsp;when&nbsp;2&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [3]=sum(case&nbsp;b1&nbsp;when&nbsp;3&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [4]=sum(case&nbsp;b1&nbsp;when&nbsp;4&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [5]=sum(case&nbsp;b1&nbsp;when&nbsp;5&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end)<br>from&nbsp;tb<br>union&nbsp;all<br>select&nbsp;<br> [1]=sum(case&nbsp;b2&nbsp;when&nbsp;1&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [2]=sum(case&nbsp;b2&nbsp;when&nbsp;2&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [3]=sum(case&nbsp;b2&nbsp;when&nbsp;3&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [4]=sum(case&nbsp;b2&nbsp;when&nbsp;4&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [5]=sum(case&nbsp;b2&nbsp;when&nbsp;5&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end)<br>from&nbsp;tb<br>union&nbsp;all<br>select&nbsp;<br> [1]=sum(case&nbsp;b3&nbsp;when&nbsp;1&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [2]=sum(case&nbsp;b3&nbsp;when&nbsp;2&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [3]=sum(case&nbsp;b3&nbsp;when&nbsp;3&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [4]=sum(case&nbsp;b3&nbsp;when&nbsp;4&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end),<br> [5]=sum(case&nbsp;b3&nbsp;when&nbsp;5&nbsp;then&nbsp;1&nbsp;else&nbsp;0&nbsp;end)<br>from&nbsp;tb

DECLARE&nbsp;@SQLTXT&nbsp;VARCHAR(2000),&nbsp;@TEMSQL&nbsp;VARCHAR(8000)<br>SET&nbsp;@SQLTXT&nbsp;=&nbsp;'SELECT&nbsp;'<br>SELECT&nbsp;@SQLTXT&nbsp;=&nbsp;@SQLTXT&nbsp;+&nbsp;'&nbsp;SUM(CASE&nbsp;WHEN&nbsp;FD='&nbsp;+&nbsp;RTRIM(LTRIM(CONVERT(VARCHAR,A1)))&nbsp;+<br>'&nbsp;THEN&nbsp;VALUE&nbsp;ELSE&nbsp;0&nbsp;END)&nbsp;AS&nbsp;['&nbsp;+&nbsp;RTRIM(LTRIM(CONVERT(VARCHAR,A1)))&nbsp;+&nbsp;'],'&nbsp;FROM&nbsp;(<br>select&nbsp;DISTINCT&nbsp;a1&nbsp;from&nbsp;(<br>SELECT&nbsp;A1&nbsp;FROM&nbsp;A<br>UNION&nbsp;<br>SELECT&nbsp;A2&nbsp;FROM&nbsp;A<br>UNION&nbsp;<br>SELECT&nbsp;B1&nbsp;FROM&nbsp;A<br>UNION&nbsp;<br>SELECT&nbsp;B2&nbsp;FROM&nbsp;A<br>UNION&nbsp;<br>SELECT&nbsp;B3&nbsp;FROM&nbsp;A)&nbsp;as&nbsp;a)&nbsp;AS&nbsp;B<br>SET&nbsp;@SQLTXT&nbsp;=&nbsp;LEFT(@SQLTXT,LEN(@SQLTXT)-1)<br>SET&nbsp;@TEMSQL&nbsp;=&nbsp;@SQLTXT&nbsp;+&nbsp;'&nbsp;FROM&nbsp;(SELECT&nbsp;A1&nbsp;AS&nbsp;FD,&nbsp;COUNT(*)&nbsp;AS&nbsp;VALUE&nbsp;&nbsp;FROM&nbsp;A&nbsp;GROUP&nbsp;BY&nbsp;A1)&nbsp;AS&nbsp;T1'<br>SET&nbsp;@TEMSQL&nbsp;=&nbsp;@TEMSQL&nbsp;+&nbsp;'&nbsp;UNION&nbsp;ALL&nbsp;'&nbsp;+&nbsp;@SQLTXT&nbsp;+&nbsp;'&nbsp;FROM&nbsp;(SELECT&nbsp;A2&nbsp;AS&nbsp;FD,&nbsp;COUNT(*)&nbsp;AS&nbsp;VALUE&nbsp;&nbsp;FROM&nbsp;A&nbsp;GROUP&nbsp;BY&nbsp;A2)&nbsp;AS&nbsp;T2'<br>SET&nbsp;@TEMSQL&nbsp;=&nbsp;@TEMSQL&nbsp;+&nbsp;'&nbsp;UNION&nbsp;ALL&nbsp;'&nbsp;+&nbsp;@SQLTXT&nbsp;+&nbsp;'&nbsp;FROM&nbsp;(SELECT&nbsp;B1&nbsp;AS&nbsp;FD,&nbsp;COUNT(*)&nbsp;AS&nbsp;VALUE&nbsp;&nbsp;FROM&nbsp;A&nbsp;GROUP&nbsp;BY&nbsp;B1)&nbsp;AS&nbsp;T3'<br>SET&nbsp;@TEMSQL&nbsp;=&nbsp;@TEMSQL&nbsp;+&nbsp;'&nbsp;UNION&nbsp;ALL&nbsp;'&nbsp;+&nbsp;@SQLTXT&nbsp;+&nbsp;'&nbsp;FROM&nbsp;(SELECT&nbsp;B2&nbsp;AS&nbsp;FD,&nbsp;COUNT(*)&nbsp;AS&nbsp;VALUE&nbsp;&nbsp;FROM&nbsp;A&nbsp;GROUP&nbsp;BY&nbsp;B2)&nbsp;AS&nbsp;T4'<br>SET&nbsp;@TEMSQL&nbsp;=&nbsp;@TEMSQL&nbsp;+&nbsp;'&nbsp;UNION&nbsp;ALL&nbsp;'&nbsp;+&nbsp;@SQLTXT&nbsp;+&nbsp;'&nbsp;FROM&nbsp;(SELECT&nbsp;B3&nbsp;AS&nbsp;FD,&nbsp;COUNT(*)&nbsp;AS&nbsp;VALUE&nbsp;&nbsp;FROM&nbsp;A&nbsp;GROUP&nbsp;BY&nbsp;B3)&nbsp;AS&nbsp;T5'<br>EXEC&nbsp;SP_EXECUTESQL&nbsp;&nbsp;@TEMSQL<br><br><br>已经测试过

正如雪狼2008所言，是统计A1&nbsp;A2&nbsp;B1&nbsp;B2&nbsp;B3....<br>各出现1&nbsp;&nbsp;2&nbsp;&nbsp;3&nbsp;&nbsp;4&nbsp;&nbsp;5的次数<br><br>我的表A中字段A1,A2,B1,B2....有50对个字段，上述方法是否可取？

先找出表中所有的值，再对每一个字段分组，你可以更改一下测试看性能如何，慢的话可以想办法去优化。祝你好运

表建的不合理!!

多人接受答案了。