mysql - 如何在MySQL中获取此表？

我有以下架构：



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

例：

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（*）...

最佳答案

尝试这个：

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);

关于mysql - 如何在MySQL中获取此表？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21269401/