SQL> select t.* from test_row_cell t; ID NAME ---------- -------------------- 1 A 2 B 3 C 4 D 5 E 6 F 1 B 1 C 4 A 4 B 4 C11 rows selectedSQL> SQL> SELECT id, str 2 FROM (SELECT id, 3 row_number() over(PARTITION BY id ORDER BY name) AS rn, 4 name || lead(',' || name, 1) over(PARTITION BY id ORDER BY name) || lead(',' || name, 2) over(PARTITION BY id ORDER BY name) || lead(',' || name, 3) over(PARTITION BY id ORDER BY name) AS str 5 FROM test_row_cell) 6 WHERE rn = 1 7 ORDER BY 1; ID STR ---------- -------------------------------------------------------------------------------- 1 A,B,C 2 B 3 C 4 A,B,C,D 5 E 6 F6 rows selected
谢谢大家的的回复!! 虽然现在暂时只看懂了3楼的语句. 不过啊... 嗯... 7楼的虽然还没看名白但是... 这个确实也可以在MySQL下执行么? 3楼的应该是只能在Oracle下执行吧 (也没关系, 先运行起来再说). 嗯... 反正现在就是查到了3个名字, 比如: "select NAME from EMP where SALARY = 1000"; 那么如果只针对这句话的话, 除了用Oracle专用的函数外, 要怎么修改才可以实现把3个名字组成一个字符串呢? 谢谢!!
网上的例子很多!
http://cosio.itpub.net!
select wmsys.wm_concat(name) from yourtable
---------- --------------------
1 A
2 B
3 C
4 D
5 E
6 F
1 B
1 C
4 A
4 B
4 C11 rows selectedSQL>
SQL> SELECT id, str
2 FROM (SELECT id,
3 row_number() over(PARTITION BY id ORDER BY name) AS rn,
4 name || lead(',' || name, 1) over(PARTITION BY id ORDER BY name) || lead(',' || name, 2) over(PARTITION BY id ORDER BY name) || lead(',' || name, 3) over(PARTITION BY id ORDER BY name) AS str
5 FROM test_row_cell)
6 WHERE rn = 1
7 ORDER BY 1; ID STR
---------- --------------------------------------------------------------------------------
1 A,B,C
2 B
3 C
4 A,B,C,D
5 E
6 F6 rows selected
单ORACLE有比较简单的方法如三楼的