sql - 如何根据列选择行数

Question

所以我在 Oracle 数据库中有两列:

Name / count

我想将名字打印 x 次，x 是计数。

E.g. Paul / 5 would mean Paul being printed 5 times.
     Sam / 6 would mean Sam being printed 6 times

尝试过 row_number 但不确定它是如何工作的？

Answer 1

您可以通过以下方式使用连接:

SQL> WITH YOUR_TABLE AS
  2  (SELECT 'paul' as NAME, 5 AS COUNT FROM DUAL UNION ALL
  3  SELECT 'sam' as NAME, 6 AS COUNT FROM DUAL
  4  ) -- YOUR ACTUAL QUERY STARTS FROM LINE#5
  5  Select t.name, m.lvl
  6  from your_table t
  7  join
  8  (Select level as lvl
  9  from
 10    (Select max(count) as maxcount
 11     from your_table)
 12  Connect by level <= maxcount) m
 13  On (t.count >= m.lvl)
 14  ORDER BY 1,2;

NAME        LVL
---- ----------
paul          1
paul          2
paul          3
paul          4
paul          5
sam           1
sam           2
sam           3
sam           4
sam           5
sam           6

11 rows selected.

SQL>

干杯!!