SQL 语句，JOIN多张表联表查询问题

Question

subject 表，可以理解为分类表。
course 和 source_book 两个表分别关联了 subject

现在要以subject表作为主表，查询该分类下有多少个课程，多少本书。

• 查询语句1：查询每个分类下的课程数量

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

查询结果：

sid	course_count
s@_5a61cb6e8d76c11548vIrM	5
s@_5a65bd36d97902207DvomQ	10
s@_5a65bd36dc7ec2207LdZAM	52
s@_5a65bd36deea72207Flx2K	34
s@_5a65bd36e3cae22075HcSq	143
s@_5a65bd36f26dc2207Y9s5m	1200

• 查询语句2：查询每个分类下的图书数量

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语句联合起来，查询出这样的结果？

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

Answer 1

我理解,大致应该是这样的.

用 subject.sid 分别去course, book 表查询各自的数量.

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;

Answer 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
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

Answer 3

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`
    

Answer 4

非常感谢大神们的回答，三种方法都可用，这里做个总结：

---

方法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`;

• 执行计划：

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`

• 执行计划：

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`

• 执行计划：

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

Answer 5

--sqlserver亲测有效

--语句简单优雅却又不失功能

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

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