select a.parent_group_id ,b.group_name from dchngroupinfo a,dchngroupmsg b
where a.parent_group_id=b.group_id and a.denorm_level in(0,1,2)
and a.group_id ='1003227'
group by a.denorm_level,a.parent_group_id
order by a.denorm_level desc;
以上查询结果为三条记录,现在想对所查询的三条记录合并为一条记录,即一条包含六个字段的记录
如: a.parent_group_id aa,b.group_name aa1,a.parent_group_id bb,b.group_name bb1,a.parent_group_id cc,b.group_name cc1 这六个字段各位大侠,请问这样的语句怎么写??
where a.parent_group_id=b.group_id and a.denorm_level in(0,1,2)
and a.group_id ='1003227'
group by a.denorm_level,a.parent_group_id
order by a.denorm_level desc;
以上查询结果为三条记录,现在想对所查询的三条记录合并为一条记录,即一条包含六个字段的记录
如: a.parent_group_id aa,b.group_name aa1,a.parent_group_id bb,b.group_name bb1,a.parent_group_id cc,b.group_name cc1 这六个字段各位大侠,请问这样的语句怎么写??
select a.denorm_level ,a.group_id , a.parent_group_id aa1 ,b.group_name aa2,'' bb1,'' bb2,'' cc1,'' cc2 from dchngroupinfo a,dchngroupmsg b
where a.parent_group_id=b.group_id and a.denorm_level =1
union all
select a.denorm_level ,a.group_id ,'' aa1 ,'' aa2,a.parent_group_id bb1,b.group_name bb2,'' cc1,'' cc2 from dchngroupinfo a,dchngroupmsg b
where a.parent_group_id=b.group_id and a.denorm_level =2
union all
select a.denorm_level,a.group_id ,'' aa1 ,'' aa2,'' bb1,'' bb2,a.parent_group_id cc1,b.group_name cc2 from dchngroupinfo a,dchngroupmsg b
where a.parent_group_id=b.group_id and a.denorm_level =3
) a
where a.group_id ='1003227'
group by denorm_level
order by denorm_level desc不能执行,提供个思路楼主可以试一下
---------- ----------
1 a
1 b
1 cExecuted in 0.031 secondsSQL>
SQL> select a.id,a.name,b.id,b.name,c.id,c.name
2 from (
3 select rn,id,name
4 from (select rownum rn,id,name from test_tab) where rn =1
5 ) a,
6 (
7 select rn-1 rn,id,name
8 from (select rownum rn,id,name from test_tab) where rn =2
9 ) b,
10 (
11 select rn-2 rn,id,name
12 from (select rownum rn,id,name from test_tab) where rn =3
13 ) c
14 where a.rn = b.rn
15 and b.rn = c.rn
16 / ID NAME ID NAME ID NAME
---------- ---------- ---------- ---------- ---------- ----------
1 a 1 b 1 cExecuted in 0.031 seconds
(
SELECT ID AS ID1, NAME AS NAME1,
ID AS ID2, LEAD(NAME,1,NULL) OVER(ORDER BY ID) AS NAME2,
ID AS ID3, LAST_VALUE(NAME) OVER(ORDER BY ID) AS NAME3,
ROWNUM
FROM TESTAA
)
WHERE ROWNUM = 1;
但不知dragonBill的写法,testaa表应该替换成什么样的sql??
一般这种情况下原表必定不是大数据量,否则生成的列个数太多。
特殊情况下若原表为大数据量,而必须封装成一个对象不可,则考虑用动态sql返回。
思路:相当于将表中的每一个单元格拿出来作为新结果集的列。
因为你没有提供建表语句,我用数据库自带的tabs视图给你一个参考:SELECT (SELECT table_name
FROM (SELECT rownum rn, t.TABLE_NAME FROM tabs t)
WHERE rn = 1) c1,
(SELECT table_name
FROM (SELECT rownum rn, t.TABLE_NAME FROM tabs t)
WHERE rn = 2) c2,
(SELECT table_name
FROM (SELECT rownum rn, t.TABLE_NAME FROM tabs t)
WHERE rn = 3) c3,
(SELECT STATUS
FROM (SELECT rownum rn, t.STATUS FROM tabs t)
WHERE rn = 4) c4,
(SELECT STATUS
FROM (SELECT rownum rn, t.STATUS FROM tabs t)
WHERE rn = 5) c5,
(SELECT STATUS
FROM (SELECT rownum rn, t.STATUS FROM tabs t)
WHERE rn = 6) c6
FROM dual以上sql本机测试通过。