我有一张桌子(书(，是这样的：
user_rent | book_rent | rent_from | rent_to
-----------------------------------------------
Alan Doe | Macbeth | 01.07.2018 | 15.07.2018
Alan Doe | Hamlet | 16.07.2018 | 01.08.2018
Alan Doe | Othello | 02.08.2018 | 31.08.2018
Alan Doe | King Lear | 01.09.2018 |
Alex Doe | Dracula | 01.07.2018 | 15.07.2018
Alex Doe | Hamlet | 16.07.2018 | 01.08.2018
Alex Doe | Hobbit | 02.08.2018 | 31.08.2018
Alex Doe | Inferno | 01.09.2018 |
Anna Doe | 1984 | 01.07.2018 | 15.07.2018
Anna Doe | King Lear | 16.07.2018 | 01.08.2018
Anna Doe | Hobbit | 02.08.2018 | 31.08.2018
Anna Doe | Dracula | 01.09.2018 |
Ella Doe | Macbeth | 01.07.2018 | 15.07.2018
Ella Doe | Beowulf | 16.07.2018 | 01.08.2018
Ella Doe | King Lear | 02.08.2018 | 31.08.2018
Ella Doe | Dracula | 01.09.2018 |
Emma Doe | Beowulf | 01.07.2018 | 15.07.2018
Emma Doe | Inferno | 16.07.2018 | 01.08.2018
Emma Doe | Macbeth | 02.08.2018 | 31.08.2018
Emma Doe | Lolita | 01.09.2018 |
Jack Doe | 1984 | 01.07.2018 | 15.07.2018
Jack Doe | Inferno | 16.07.2018 | 01.08.2018
Jack Doe | Othello | 02.08.2018 | 31.08.2018
Jack Doe | Dracula | 01.09.2018 |
Jade Doe | Lolita | 01.07.2018 | 15.07.2018
Jade Doe | Hobbit | 16.07.2018 | 01.08.2018
Jade Doe | Hamlet | 02.08.2018 | 31.08.2018
Jade Doe | Beowulf | 01.09.2018 |
Jane Doe | Dracula | 01.07.2018 | 15.07.2018
Jane Doe | Ulysses | 16.07.2018 | 01.08.2018
Jane Doe | Inferno | 02.08.2018 | 31.08.2018
Jane Doe | Pygmalion | 01.09.2018 |
John Doe | Macbeth | 01.07.2018 | 15.07.2018
John Doe | Hobbit | 16.07.2018 | 01.08.2018
John Doe | Ulysses | 02.08.2018 | 31.08.2018
John Doe | Dracula | 01.09.2018 |
Noah Doe | Pygmalion | 01.07.2018 | 15.07.2018
Noah Doe | Othello | 16.07.2018 | 01.08.2018
Noah Doe | Beowulf | 02.08.2018 | 31.08.2018
Noah Doe | 1984 | 01.09.2018 |
Nora Doe | Dracula | 01.07.2018 | 15.07.2018
Nora Doe | Pygmalion | 16.07.2018 | 01.08.2018
Nora Doe | Hamlet | 02.08.2018 | 31.08.2018
Nora Doe | Lolita | 01.09.2018 |
Sara Doe | Beowulf | 01.07.2018 | 15.07.2018
Sara Doe | Dracula | 16.07.2018 | 01.08.2018
Sara Doe | Ulysses | 02.08.2018 | 31.08.2018
Sara Doe | Lolita | 01.09.2018 |
Seth Doe | Macbeth | 01.07.2018 | 15.07.2018
Seth Doe | Hamlet | 16.07.2018 | 01.08.2018
Seth Doe | King Lear | 02.08.2018 | 31.08.2018
Seth Doe | Othello | 01.09.2018 |
我需要一些租借莎士比亚书籍的用户。
使用此查询：
SELECT USER_RENT,
COUNT(DISTINCT CASE WHEN BOOK_RENT = 'Hamlet' THEN USER_RENT END) HAMLET,
COUNT(DISTINCT CASE WHEN BOOK_RENT = 'Othello' THEN USER_RENT END) OTHELLO,
COUNT(DISTINCT CASE WHEN BOOK_RENT = 'Macbeth' THEN USER_RENT END) MACBETH,
COUNT(DISTINCT CASE WHEN BOOK_RENT = 'King Lear' THEN USER_RENT END) KING_LEAR
FROM BOOKS
GROUP BY USER_RENT;
我得到了这个临时表：
user_rent | hamlet | othello | macbeth | king_lear
--------------------------------------------------
Alan Doe | 1 | 1 | 1 | 1
Alex Doe | 1 | 0 | 0 | 0
Anna Doe | 0 | 0 | 0 | 1
Ella Doe | 0 | 0 | 1 | 1
Emma Doe | 0 | 0 | 1 | 0
Jack Doe | 0 | 1 | 0 | 0
Jade Doe | 1 | 0 | 0 | 0
Jane Doe | 0 | 0 | 0 | 0
John Doe | 0 | 0 | 1 | 0
Noah Doe | 0 | 1 | 0 | 0
Nora Doe | 1 | 0 | 0 | 0
Sara Doe | 0 | 0 | 0 | 0
Seth Doe | 1 | 1 | 1 | 1
我将其导出到 excel，然后通过透视和过滤器获得了我最终想要的结果(这是我需要的(：
book_rent | number
-----------------------------------
Hamlet | 5
Othello | 4
Macbeth | 5
King Lear | 4
Hamlet AND Othello | 2
Hamlet AND Macbeth | 2
Hamlet AND King Lear | 2
Othello AND Macbeth | 1
Othello AND King Lear | 2
Macbeth AND King Lear | 3
H AND O AND M | 2
H AND O AND K | 2
H AND M AND K | 2
O AND M AND K | 2
All four | 2
Hamlet OR Othello | 7
Hamlet OR Macbeth | 8
.
.
.
由于我有更大的表，那么这是为此示例制作的，有没有一种更优雅和简单的方法可以直接在 SQL 中执行此操作？
这能做到你想要的吗？
SELECT books, COUNT(*)
FROM (SELECT USER_RENT, LISTAGG(BOOK_RENT, ';') WITHIN GROUP (ORDER BY BOOK_RENT) as books
FROM BOOKS
GROUP BY USER_RENT
) bu
GROUP BY books;
如果有人两次租借同一本书，它会出现多次。 您可能需要：
SELECT books, COUNT(*)
FROM (SELECT USER_RENT, LISTAGG(BOOK_RENT, ';') WITHIN GROUP (ORDER BY BOOK_RENT) as books
FROM (SELECT DISTINCT USER_RENT, BOOK_RENT FROM BOOKS) ub
GROUP BY USER_RENT
) bu
GROUP BY books;
我的尝试：
/* -- sample data
with titles(book) as (
select 'Hamlet' from dual union all
select 'Othello' from dual union all
select 'Macbeth' from dual union all
select 'King Lear' from dual ),
books (usr, book) as (
select 'Alan', 'Hamlet' from dual union all
select 'Alan', 'Othello' from dual union all
select 'Alan', 'Macbeth' from dual union all
select 'Alan', 'King Lear' from dual union all
select 'Alan', 'Hamlet' from dual union all
select 'Alex', 'Hamlet' from dual union all
select 'Anna', 'King Lear' from dual union all
select 'Ella', 'Macbeth' from dual union all
select 'Ella', 'King Lear' from dual union all
select 'Emma', 'Macbeth' from dual union all
select 'Jack', 'Othello' from dual union all
select 'Jade', 'Hamlet' from dual union all
select 'John', 'Macbeth' from dual union all
select 'Noah', 'Othello' from dual union all
select 'Nora', 'Hamlet' from dual union all
select 'Seth', 'Hamlet' from dual union all
select 'Seth', 'Othello' from dual union all
select 'Seth', 'Macbeth' from dual union all
select 'Seth', 'King Lear' from dual ),
*/ -- end of sample data
with
tmp as (
select grp, sys_connect_by_path(book, '#') path, level cnt
from (select row_number() over (order by book) grp, book from titles)
connect by book > prior book),
groups as (
select grp, path, cnt, trim(column_value) book
from tmp, xmltable(('"'||replace(ltrim(path,'#'), '#', '","')||'"')))
select path, count(1) cnt, listagg(usr, ', ') within group (order by usr) users
from (
select usr, path, grp
from groups g join (select distinct usr, book from books) b on b.book = g.book
group by usr, path, cnt, grp
having cnt = count(1))
group by path
似乎你想要所有书籍组的组合。我使用了分层查询，然后将函数sys_connect_by_path()的结果拆分为行来实现这一点。
下一步是使用表books加入这些组，计算每个用户的书籍数量，如果此数字低于组中的书籍数量 - 将其从结果中删除。
最后，我只对用户进行分组以计算它们并以列表形式呈现。结果：
PATH CNT USERS
---------------------------------- ---------- -------------------------------
#Hamlet 5 Alan, Alex, Jade, Nora, Seth
#Hamlet#King Lear 2 Alan, Seth
#Hamlet#King Lear#Macbeth 2 Alan, Seth
#Hamlet#King Lear#Macbeth#Othello 2 Alan, Seth
#Hamlet#King Lear#Othello 2 Alan, Seth
#Hamlet#Macbeth 2 Alan, Seth
#Hamlet#Macbeth#Othello 2 Alan, Seth
#Hamlet#Othello 2 Alan, Seth
#King Lear 4 Alan, Anna, Ella, Seth
#King Lear#Macbeth 3 Alan, Ella, Seth
#King Lear#Macbeth#Othello 2 Alan, Seth
#King Lear#Othello 2 Alan, Seth
#Macbeth 5 Alan, Ella, Emma, John, Seth
#Macbeth#Othello 2 Alan, Seth
#Othello 4 Alan, Jack, Noah, Seth