Select Case When MOD(rownm, 2) = 0 then ' '
Else to_char((rownm + 1) / 2) End as rownm,
name, total, column1
From
(
select (rownm * 2 - 1) rownm,name, to_char(total) total ,column1 from t
union
SELECT (rownm * 2) rownm,'XXX' name, 'XXX' total, 'The row act .... ' column1 FROM t
) Q
Order by Q.rownm;
因为您已经对数据进行了分组,所以使用起来可能更容易 GROUPING SETS 而不是 UNION . 通过分组集,可以按多组列进行分组,包括同一组两次以复制行。然后 GROUP_ID 函数可用于确定何时应使用假值。此代码将比 UNION 方法,并且应该更快,因为它不需要多次引用表。
select
case when group_id() = 0 then name else '' end name,
case when group_id() = 0 then sum(some_value) else null end total,
case when group_id() = 1 then 'this rows...' else '' end column1
from
(
select 'jack' name, 22 some_value from dual union all
select 'jack' name, 1 some_value from dual union all
select 'john' name, 44 some_value from dual union all
select 'john' name, 1 some_value from dual union all
select 'harry' name, 1 some_value from dual union all
select 'harry' name, 1 some_value from dual
) raw_data
group by grouping sets (name, name)
order by raw_data.name, group_id();
SQL> SELECT CASE WHEN L.LVL = 1 THEN T.ROWNM END AS ROWNM,
2 CASE WHEN L.LVL = 1 THEN T.NAME
3 ELSE 'XXX' END AS NAME,
4 CASE WHEN L.LVL = 1 THEN TO_CHAR(T.TOTAL)
5 ELSE 'XXX' END AS TOTAL,
6 CASE WHEN L.LVL = 1 THEN T.COLUMN1
7 ELSE 'This row act as separator..' END AS COLUMN1
8 FROM T CROSS JOIN (
9 SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <= 2
10 ) L ORDER BY T.ROWNM, L.LVL;
ROWNM NAME TOTAL COLUMN1
---------- ---------- ----- ---------------------------
1 Jack 23
XXX XXX This row act as separator..
2 John 45
XXX XXX This row act as separator..
3 harry 2
XXX XXX This row act as separator..
4 roy 45
XXX XXX This row act as separator..
5 Jacob 26
XXX XXX This row act as separator..
10 rows selected.
SQL>
3条答案
按热度按时间ergxz8rk1#
正如其他人所建议的,最好在前端进行。
但是,如果您有一个烧录需要作为一个查询来完成,下面是如何实现的。在这里,我没有像您之前那样使用rownum函数。我假设,您的数据是通过查询返回的,您可以用查询替换我的表。我做了更多的假设,因为你有数据中的行号[我不知道你说的“不pl/sql”是什么意思]
这是小提琴
wswtfjt72#
因为您已经对数据进行了分组,所以使用起来可能更容易
GROUPING SETS
而不是UNION
.通过分组集,可以按多组列进行分组,包括同一组两次以复制行。然后
GROUP_ID
函数可用于确定何时应使用假值。此代码将比UNION
方法,并且应该更快,因为它不需要多次引用表。cunj1qz13#
您可以使用行生成器技术(使用
CONNECT BY
)然后使用CASE..WHEN
具体如下: