一条超难 sql查询语句,请高手进入(200分)

表TABLE<br>A &nbsp; &nbsp;B &nbsp; &nbsp; C <br>1 &nbsp; &nbsp;a &nbsp; &nbsp; 121 <br>1 &nbsp; &nbsp;a &nbsp; &nbsp; 123<br>2 &nbsp; &nbsp;b &nbsp; &nbsp; 3112 <br>2 &nbsp; &nbsp;b &nbsp; &nbsp; 32342<br>3 &nbsp; &nbsp;c &nbsp; &nbsp; 443 <br>3 &nbsp; &nbsp;c &nbsp; &nbsp; ew231<br>3 &nbsp; &nbsp;c &nbsp; &nbsp; 1121<br>--------------------------------<br>转化为:<br>A &nbsp; &nbsp;B &nbsp; &nbsp; &nbsp;C &nbsp; &nbsp; &nbsp;D &nbsp; &nbsp; &nbsp; E &nbsp; &nbsp;F <br>1 &nbsp; &nbsp;a &nbsp; &nbsp; 121 &nbsp; &nbsp; 123<br>2 &nbsp; &nbsp;b &nbsp; &nbsp; 3112 &nbsp; &nbsp;32342<br>3 &nbsp; &nbsp;c &nbsp; &nbsp; 443 &nbsp; &nbsp; ew231 &nbsp; 1121<br>------------------------------------------

哪个兄弟给点提示啊?

case when then else

case when then else &nbsp;条件选择,它是有已知值了嘛<br>还有么好方法呢?<br>当然,如果你觉得可以,希望你能写写,请指教,谢谢

转换成表,我不太清楚,不过可以做成你转化之后的形式.<br>首先:select distinct(A) from Table 查询出A,并且过滤掉重复的,此时返加在的记录集,称为record_1,插入.然后 select distinct(B) from Table Where A=record_1 查询B ,并且过滤掉重复的,记录集为record_2,插入.最后,select distinct(C) from Table Where A=record_1 这样也返回个记录集,插入!

SQL 2005 下测试通过：<br>select A,B,<br>&nbsp; max(case n when 1 then C else '' end) C,<br>&nbsp; max(case n when 2 then C else '' end) D,<br>&nbsp; max(case n when 3 then C else '' end) E,<br>&nbsp; max(case n when 4 then C else '' end) F<br>from (select &nbsp;n=row_number() over(partition by A,B order by A,B),* from TableName) t<br>group by A,B

..U &nbsp;P..

同意kaida。先在表增加一列，就是B列的分类排序，再找到最大的序号，动态用case when then else &nbsp;生成

问题是C的值是不确定的啊<br>可能是一个可能是两个也可能是三四个啊

row_number &nbsp;在sql2000中怎么识别不了????

orace中有ROW——NUMBER，SQLSREVER没有。

提供一个思路：<br>1、创建一个临时表，表结构为：A B F1 F2 F3 ..，其中A、B字段取自原始表，F开头的字段为临时字段，字段的数量是取决于group by(A,B)后，分组统计出的C字段的最大数量，对应你上面给出的例子就是3<br>2、对前面的group by(A,B)结果进行游标处理，每次查出对应的Ｃ字段值，分别填充临时表对应的字段F1、F2....，有多少填多少<br>3、查询临时表，就是你要的结果了。

xujunjie兄,能不能帮忙写个例子,对临时表操作不熟悉

很久不来大富翁了，代码也不常写了，今天到大富翁上来逛逛，就随便回了一下。我觉得问题的难度不在于技术有多难，而是思路问题，想清楚了动手查查资料就能搞定，google就不错，我的很多问题都是靠它解决的（有广告嫌疑:-D），等别人做好例子不如自己动手，收获更大。

row_number 是 SQL 2005 的新功能。 <br>SQL 2000 实现同样功能要两次用到临时表：<br>if object_id('tb') is not null<br>&nbsp; drop table tb<br>go<br>create table tb(A int, B char(1), C char(10))<br>insert into tb<br>select 1, 'a', '121' union all<br>select 1, 'a', '123' union all<br>select 2, 'b', '3112' union all <br>select 2, 'b', '32342' union all<br>select 3, 'c', '443' union all <br>select 3, 'c', 'ew231' union all<br>select 3, 'c', '1121'<br><br>if object_id('tempdb.dbo.#t') is not null <br>&nbsp; drop table #t<br><br>create table #t(ID int identity (1,1),A int, B char(1), C char(10))<br>go<br>insert into #t(A,B,C)<br>select * from tb<br>order by A,B<br>go<br><br>go<br>if object_id('tempdb.dbo.#t2') is not null <br>&nbsp; drop table #t2<br>go<br>create table #t2(ID int,A int, B char(1), C char(10))<br>insert into #t2(ID,A,B,C)<br>select <br>&nbsp; (select count(A) from #t t2 where t2.A=t1.A)-<br>&nbsp; (select count(A) from #t t2 where t2.A=t1.A and t2.ID&gt;t1.ID),<br>&nbsp; A,B,C from #t t1<br>order by A,B<br><br>select A,B,<br>&nbsp; max(case ID when 1 then C else '' end) C,<br>&nbsp; max(case ID when 2 then C else '' end) D,<br>&nbsp; max(case ID when 3 then C else '' end) E,<br>&nbsp; max(case ID when 4 then C else '' end) F<br>from #t2<br>group by A,B<br><br>drop table tb<br>drop table #t<br>drop table #t2

太感谢各位啦!

接受答案了.