mysql - MySQL中如何获取这张表？

Question

我有以下架构:

我想生成一个包含 3 列的表格 - 标签名称、线程名称、答案计数。

示例:

Foo bar 5
Foo something 6
Foo2 somethingElse 3

此外，还有一个表“priority_threads”(与线程1:1)。我只想在此表中显示那些优先级线程。

我该怎么做？我完全不知道如何开始。我唯一做的就是:

SELECT tag.name, thread.title, COUNT(answer.id_answer)
FROM tag, thread, answer
WHERE thread.id_tag = tag.id_tag 
AND answer.id_thread = thread.id_thread
AND thread.id_thread = priority_threads.id_thread
GROUP BY tag.name, thread.title
ORDER BY tag.name;

但是，使用此查询，答案计数列中的所有值都是相同的 - 来自答案表的 count(*)...

Answer 1

试试这个:

SELECT tag.name, thread.title, COUNT(answer.id_answer)
FROM tag
JOIN thread ON
tag.id_tag=thread.id_tag
JOIN answer ON
    thread.id_thread = answer.id_thread
JOIN priority_threads
ON thread.id_thread = priority_threads.id_thread
GROUP BY tag.name, thread.title
ORDER BY tag.name;

这是一个SQLfiddle链接到查询以对其进行测试；

示例代码:

 CREATE TABLE Tag
    (
 id_tag int auto_increment primary key, 
 name varchar(20)
 );

INSERT INTO Tag
(name)
VALUES
('Foo'), 
('Foo2');

CREATE TABLE Thread
    (
 id_thread int auto_increment primary key, 
 id_tag int, 
 title varchar(20)
 );

INSERT INTO Thread
(id_tag, title)
VALUES
(1,'Bar'), 
(1,'Something'),
(2,'SomethingElse');

CREATE TABLE Answer
    (
 id_answer int auto_increment primary key, 
 id_thread int,
 text varchar(200)
 );

INSERT INTO Answer
(id_thread, text)
VALUES
(1,'jlkjalkjl'), 
(1,'ioioixhakjjkj'),
(1, 'jjalkjijkajk'),
(1, 'jjalkjijkajk'),
(1, 'jjalkjijkajk'),
(2, 'jjalkjijkajk'),
(2, 'jjalkjijkajk'),
(2, 'jjalkjijkajk'),
(2, 'qqweeweraata'),
(2, 'jjalkjijkajk'),
(2, 'jjalkjijkajk'),
(3, 'popoapopop'),
(3, 'zkkasjkljz'),
(3, 'jjalkjijkajk')
;

CREATE TABLE priority_threads
    (
 id_priority_threads int auto_increment primary key, 
 id_thread int, 
 priority int
 );

INSERT INTO priority_threads
(id_thread, priority)
VALUES
(1,1), 
(3,2);