鍍金池/ 問答/數(shù)據(jù)庫/ SQL 語句，JOIN多張表聯(lián)表查詢問題

SQL 語句，JOIN多張表聯(lián)表查詢問題

subject 表，可以理解為分類表。
course 和 source_book 兩個(gè)表分別關(guān)聯(lián)了 subject

現(xiàn)在要以subject表作為主表，查詢該分類下有多少個(gè)課程，多少本書。

  • 查詢語句1：查詢每個(gè)分類下的課程數(shù)量
SELECT
    `subject`.sid,
    COUNT( course.id ) AS course_count
FROM
    `subject`
    LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid` 
GROUP BY
    `subject`.`sid`

查詢結(jié)果：

sid course_count
s@_5a61cb6e8d76c11548vIrM 5
s@_5a65bd36d97902207DvomQ 10
s@_5a65bd36dc7ec2207LdZAM 52
s@_5a65bd36deea72207Flx2K 34
s@_5a65bd36e3cae22075HcSq 143
s@_5a65bd36f26dc2207Y9s5m 1200
  • 查詢語句2：查詢每個(gè)分類下的圖書數(shù)量
SELECT
    `subject`.sid,
    COUNT( book.id ) AS book_count
FROM
    `subject`
    LEFT JOIN `book` ON `subject`.`sid` = `book`.`sid` 
GROUP BY
    `subject`.`sid`
sid book_count
s@_5a61cb6e8d76c11548vIrM 4
s@_5a65bd36d97902207DvomQ 0
s@_5a65bd36dc7ec2207LdZAM 0
s@_5a65bd36deea72207Flx2K 0
s@_5a65bd36e3cae22075HcSq 2
s@_5a65bd36f26dc2207Y9s5m 1176

那么，如何將這兩條sql語句聯(lián)合起來，查詢出這樣的結(jié)果？

sid course_count book_count
s@_5a61cb6e8d76c11548vIrM 5 4
s@_5a65bd36d97902207DvomQ 10 0
s@_5a65bd36dc7ec2207LdZAM 52 0
s@_5a65bd36deea72207Flx2K 34 0
s@_5a65bd36e3cae22075HcSq 143 2
s@_5a65bd36f26dc2207Y9s5m 1200 1176
回答
編輯回答
萌吟

--sqlserver親測有效

--語句簡單優(yōu)雅卻又不失功能

--就是性能上可能有些不足


SELECT [subject].Id,COUNT([course].Id) AS course_count,

                    COUNT([book].Id)   AS book_count

FROM [subject],[course],[book]

WHERE [course].Uid=[subject].Id AND [book].Uid=[subject].Id

GROUP BY [subject].Id
2017年4月13日 00:00
編輯回答
蝶戀花

SELECT

`c`.sid,
COUNT( c.id ) AS course_count,COUNT( book.book_count ) AS book_count

FROM

(SELECT
    `subject`.sid,
    COUNT( course.id ) AS course_count
FROM
    `subject`
    LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid` 
GROUP BY
    `subject`.`sid`) c

LEFT JOIN book ON c.sid = book.sid
GROUP BY

`subject`.`sid`
2017年1月7日 07:58
編輯回答
幼梔

我理解,大致應(yīng)該是這樣的.

用 subject.sid 分別去course, book 表查詢各自的數(shù)量.

SELECT subject.sid, 
    (SELECT COUNT(course.id) AS course_count FROM course WHERE course.sid = subject.sid),
    (SELECT COUNT(book.id) as book_count FROM book WHERE book.sid = subject.sid) 
FROM subject;
2017年3月7日 22:51
編輯回答
伐木累

非常感謝大神們的回答，三種方法都可用，這里做個(gè)總結(jié)：

方法1：

SELECT
    `subject`.`sid`,
    ( SELECT COUNT( `course`.`id` ) FROM `course` WHERE `course`.`sid` = `subject`.`sid` ) AS `course_count`,
    ( SELECT COUNT( `book`.`id` ) FROM `book` WHERE `book`.`sid` = `subject`.`sid` ) AS `book_count` 
FROM
    `subject`;
  • 執(zhí)行計(jì)劃：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY subject index sid 75 14 Using index
3 DEPENDENT SUBQUERY book ref idx_sid idx_sid 75 subject.sid 106 Using index
2 DEPENDENT SUBQUERY course ref idx_sid idx_sid 75 subject.sid 88 Using index

方法2

SELECT
    `sid`,
    SUM( `course_count` ) AS `course_count`,
    SUM( `book_count` ) AS `book_count` 
FROM
    (
    SELECT
        `subject`.`sid`,
        COUNT( `course`.`id` ) AS `course_count`,
        0 AS `book_count`,
        0 AS `article_count` 
    FROM
        `subject`
        LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid` 
    GROUP BY
        `subject`.`sid` UNION ALL
    SELECT
        `subject`.sid,
        0 AS `course_count`,
        COUNT( `book`.`id` ) AS `book_count`,
        0 AS `article_count` 
    FROM
        `subject`
        LEFT JOIN `book` ON `subject`.`sid` = `book`.`sid` 
    GROUP BY
        `subject`.`sid` 
    ) `A` 
GROUP BY
    `sid`
  • 執(zhí)行計(jì)劃：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL 2716 Using temporary; Using filesort
2 DERIVED subject index PRIMARY 75 14 Using index
2 DERIVED course ref idx_sid idx_sid 75 subject.sid 88 Using index
3 UNION subject index PRIMARY 75 14 Using index
3 UNION book ref idx_sid idx_sid 75 subject.sid 106 Using index
UNION RESULT <union2,3> ALL

方法3

SELECT
    `c`.`sid`,
    `c`.`course_count` AS `course_count`,
    COUNT( `book`.`sid` ) AS `book_count` 
FROM
    (
    SELECT
        `subject`.`sid`,
        COUNT( `course`.`id` ) AS `course_count` 
    FROM
        `subject`
        LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid` 
    GROUP BY
        `subject`.`sid` 
    ) `c`
    LEFT JOIN `book` ON `c`.`sid` = `book`.`sid` 
GROUP BY
    `c`.`sid`
  • 執(zhí)行計(jì)劃：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL 1232 Using temporary; Using filesort
1 PRIMARY book ref idx_sid idx_sid 75 c.sid 106 Using index
2 DERIVED subject index PRIMARY 75 14 Using index
2 DERIVED course ref idx_sid idx_sid 75 subject.sid 88 Using index
2017年4月17日 19:18
編輯回答
檸檬藍(lán)
SELECT sid, SUM(course_count) AS course_count, SUM(book_count) AS book_count FROM 
 (
SELECT
    `subject`.sid,
    COUNT( course.id ) AS course_count,
    0 AS book_count
FROM
    `subject`
    LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid` 
GROUP BY
    `subject`.`sid`
UNION ALL
SELECT
    `subject`.sid,
    0 AS course_count,
    COUNT( book.id ) AS book_count
FROM
    `subject`
    LEFT JOIN `book` ON `subject`.`sid` = `book`.`sid` 
GROUP BY
    `subject`.`sid`) A GROUP BY sid
2017年4月27日 19:20