如何在每个分组结果之后合并硬编码行

bqjvbblv  于 2021-08-01  发布在  Java
关注(0)|答案(3)|浏览(312)


在每个组/行之后,我想插入一个硬编码的虚拟行,其中包含一堆“x”作为分隔符。
我想用oraclesql来做这个查询。我可以使用循环执行它,但我不想使用plsql。

ergxz8rk

ergxz8rk1#

正如其他人所建议的,最好在前端进行。
但是,如果您有一个烧录需要作为一个查询来完成,下面是如何实现的。在这里,我没有像您之前那样使用rownum函数。我假设,您的数据是通过查询返回的,您可以用查询替换我的表。我做了更多的假设,因为你有数据中的行号[我不知道你说的“不pl/sql”是什么意思]

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;

这是小提琴

wswtfjt7

wswtfjt72#

因为您已经对数据进行了分组,所以使用起来可能更容易 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();
cunj1qz1

cunj1qz13#

您可以使用行生成器技术(使用 CONNECT BY )然后使用 CASE..WHEN 具体如下:

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>

相关问题