请教一个SQL查询的问题

[windyloft]

有下面这样一个表
NO ID NAME COMMENT
1 001 XX AAA
2 001 XX BBBB
3 001 XX CCC
4 002 YY DDD
5 003 ZZ EEE
6 003 ZZ FFF

根据上面得到下面的VIEW
ID NAME COMMENT COMMENT2
001 XX AAA BBB
002 YY CCC
003 ZZ EEE FFF
也就是说在结果中，每一个ID对应一条记录，
并且要把每一个ID对应的记录中的COMMENT取出来（只取前两个）
例如ID“001”在原始表中有3条数据，在VIEW需要变成一条记录，
并且把前两条数据的COMMENT放到这结果中。

[shiyiwan]

第一行数据，第四列数值怎么少了一个； 第二行数据，为什么得到的是CCC.

WITH T AS (
SELECT '001' AS ID, 'XX' AS NAME, 'AAA' AS COM FROM DUAL UNION ALL 
SELECT '001' AS ID, 'XX' AS NAME, 'BBB' AS COM FROM DUAL UNION ALL 
SELECT '001' AS ID, 'XX' AS NAME, 'CCC' AS COM FROM DUAL UNION ALL 
SELECT '002' AS ID, 'YY' AS NAME, 'DDD' AS COM FROM DUAL UNION ALL 
SELECT '003' AS ID, 'ZZ' AS NAME, 'EEE' AS COM FROM DUAL UNION ALL 
SELECT '003' AS ID, 'ZZ' AS NAME, 'FFF' AS COM FROM DUAL)
SELECT DISTINCT ID, NAME, 
(SELECT COM FROM (SELECT ID, NAME,RANK() OVER(PARTITION BY ID ORDER BY COM) RN, COM FROM T) WHERE RN = 1 AND ID = T.ID),
(SELECT COM FROM (SELECT ID, NAME,RANK() OVER(PARTITION BY ID ORDER BY COM) RN, COM FROM T) WHERE RN = 2 AND ID = T.ID)
FROM T;

[NIan_jun]

引用

with t as ( select '001' id ,'XX' a,'AAA' b from dual union all select '001' id ,'XX' a,'BBBB' b from dual union all select '001' id ,'XX' a,'CCC' b from dual union all select '002' id , 'YY' a,'CCC' b from dual union all select '003' id , 'ZZ' a,'EEE' b from dual union all select '003' id , 'ZZ' a,'FFF' b from dual ), s as ( select id,a,WMSYS.WM_CONCAT(b)||',' b from t group by id,a ) select id,a,substr(b,1,instr(b,',',1)-1) COMMENT1 , substr(b,instr(b,',',1)+1,instr(b,',',2)-1) COMMENT2 from s

笨方法 COMMENT 是关键字不能作为字段名称吧

[NIan_jun]

with t as (
select '001' id  ,'XX'  a,'AAA' b from dual union all
select '001' id  ,'XX'  a,'BBBB' b from dual union all
select '001' id  ,'XX'  a,'CCC' b from dual union all
select '002' id , 'YY'  a,'CCC' b from dual union all
select '003' id , 'ZZ'  a,'EEE' b from dual union all
select '003' id , 'ZZ'  a,'FFF' b from dual 
),
s as (
select id,a,WMSYS.WM_CONCAT(b)||',' b from t group by id,a
)


select id,a,substr(b,1,instr(b,',',1)-1) COMMENT1 ,
substr(b,instr(b,',',1)+1,instr(b,',',2)-1) COMMENT2
from s

[bawgiitx]

--create table
CREATE GLOBAL TEMPORARY TABLE TEST_TABLE
(
    "NO"VARCHAR2(25),
    "ID"VARCHAR2(25),
    "NAME"VARCHAR2(25),
    "COMMENT"VARCHAR2(25)
)on commit delete rows;


--INSERT
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('1','001','XX','AAA');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('2','001','XX','BBBB');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('3','001','XX','CCC');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('4','002','YY','DDD');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('5','003','ZZ','EEE');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('6','003','ZZ','FFF');

--select
select t.id,t.name
,decode(
  sign((select count(*)from test_table ct1 where t.id=ct1.id)+1-1)
  ,1
  ,(select ttl."COMMENT" from TEST_TABLE ttl where ttl."ID"=t."ID"
  and ttl."NO" not in(select tt2."NO" from TEST_TABLE tt2 where tt2."ID"=t."ID" and rownum<1)
  and rownum=1)
)C1
,decode(
  sign((select count(*)from test_table ct1 where t.id=ct1.id)+1-2)
  ,1
  ,(select ttl."COMMENT" from TEST_TABLE ttl where ttl."ID"=t."ID"
  and ttl."NO" not in(select tt2."NO" from TEST_TABLE tt2 where tt2."ID"=t."ID" and rownum<2)
  and rownum=1)
)C2
/*
IF count(id)> 3
  select id 第一个
    && 除去结果的前 3-1 条记录
*/
,decode(
  sign((select count(*)from test_table ct1 where t.id=ct1.id)+1-3)
  ,1
  ,(select ttl."COMMENT" from TEST_TABLE ttl where ttl."ID"=t."ID"
  and ttl."NO" not in(select tt2."NO" from TEST_TABLE tt2 where tt2."ID"=t."ID" and rownum<3)
  and rownum=1)
)C3
from test_table t
group by t."ID",t."NAME"
ORDER BY t."ID";
/*
row
1	001	XX	AAA	BBBB	CCC
2	002	YY	DDD		
3	003	ZZ	EEE	FFF	
*/

[善若止水]

依据楼主的表结构，我建立了一张表test_zxc，数据就和楼主所说的一样。 因为comment是关键字，所以我将comment修改为了comment1，其他的字段和楼主的一样。sql如下所示： select d.id, d.name, MAX(case when d.rn = 1 then d.comment_1 else null end) COMMENT1, MAX(case when d.rn = 2 then d.comment_1 else null end) COMMENT2 FROM (select t.id, t.name, t.comment_1, row_number() over(partition by t.id, t.name order by t.comment_1) rn from test_zxc t) d GROUP BY d.id, d.name ORDER BY d.id

[sych888]

with tt as( select id,name,"COMMENT" cc,row_number() over(partition by id order by "COMMENT") rn from test) select id,name,wm_concat(decode(rn,1,cc,null)) cc1,wm_concat(decode(rn,2,cc,null)) cc2 from tt where tt.rn<3 group by id,name;