我有兩個表叫做Books和Co-author。我想加入他們以使表格顯示在“所需的輸出”下。我是 SQL 的新手，我正在努力加入我所做的兩個查詢......
圖書：
| 國際標準書號 | 標題 | 作者 |
|---|---|---|
| 1111 | 第一冊 | 作者 1 |
| 2222 | 書2 | 作者2 |
| 3333 | 第三冊 | |
| 4444 | 第 4 冊 | 作者3 |
合著者：
| ID | 作者 | 書（isbn） |
|---|---|---|
| 1 | 作者_x | 4444 |
| 2 | 作者_y | 1111 |
| 3 | 作者_z | 2222 |
| 4 | 作者_w | 4444 |
期望的輸出：
| 標題 | has_author | count_co_author |
|---|---|---|
| 第一冊 | 1 | 1 |
| 書2 | 1 | 1 |
| 第三冊 | 0 | 0 |
| 第 4 冊 | 1 | 2 |
我有以下疑問：
SELECT b.title, count(c.book)
FROM Books b
LEFT JOIN Coauthor c ON b.isbn = c.book
GROUP BY b.title
回傳列count_co-author。
以及對該列的另一個查詢has_author：
SELECT
b.title,
CASE
WHEN b.author IS NULL
THEN 0
ELSE 1
END AS 'Has author'
FROM Books b
我如何組合它們？
uj5u.com熱心網友回復：
選擇書籍并加入合著者計數：
select
b.title,
case when b.author is null then 0 else 1 end as has_author,
coalesce(c.cnt, 0) as count_co_author
from books b
left outer join
(
select book, count(*) as cnt
from co_author
group by book
) c on c.book = b.isbn
order by b.title;
或者從書籍中選擇并在子查詢中獲取共同作者數：
select
b.title,
case when b.author is null then 0 else 1 end as has_author,
(
select count(*)
from co_author c
where c.book = b.isbn
) as count_co_author
from books b
order by b.title;
uj5u.com熱心網友回復：
select title, has_auther, count_co-author,
from
(
(
SELECT
b.title,
count(c.book) as count_co-author
FROM Books b
LEFT JOIN Coauthor c ON b.isbn = c.book
GROUP BY b.title
) as t1,
(
SELECT
b.title,
CASE WHEN b.author IS NULL
THEN 0
ELSE 1
END AS has_auther
FROM Books b
) as t2
)
試試這個
uj5u.com熱心網友回復：
SELECT
b.title,
CASE
WHEN b.author IS NULL
THEN 0
ELSE 1
END AS has_author,
count(c.book) as count-coauthor
FROM
Books b
LEFT JOIN
Coauthor c
ON
b.isbn = c.book
GROUP BY
b.title
應該作業IG
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/366506.html