如何向这个庞大的 SQL 联合语句添加联接？

Question

我有一个声明，根据共享标签的数量和基于链接表的发言者的数量对项目进行排名，但我希望它也能够从主 tbl_talks 中提取 talk_title 和 talk_brief 信息，而该信息现在实际上不在查询中（仅使用链接表）。 基本上链接表有talk_id和tag_id或talk_id和speaker_id。

我尝试了几种尝试 LEFT JOIN 的方法，或者在应用联合的配对 SELECT 中添加 tbl_talks 但没有运气。我需要将 tbl_linking_talk_tag.talk_id 和 tbl_linking_speakers_talk 与 tbl_talks.talk_id 关联起来，然后从 tbl_talks 中获取 talk_title 和 talk_brief

SELECT talk_id, count(*) AS common_term_count FROM 
      (
      SELECT talk_id FROM tbl_linking_talk_tag WHERE tag_id IN 
         (SELECT tag_id FROM tbl_linking_talk_tag WHERE talk_id = 1)
      UNION ALL 
      SELECT talk_id FROM tbl_linking_speakers_talk WHERE speaker_id IN
         (SELECT speaker_id FROM tbl_linking_speakers_talk WHERE talk_id = 1)
       ) AS accumulator_table

 WHERE talk_id != 1 

 GROUP BY talk_id

 ORDER BY common_term_count DESC

Answer 1

不要认为在这种情况下你需要工会。如果您需要的只是计数那么为什么不尝试类似..

SELECT talk_id,
(SELECT count(*) FROM tbl_linking_talk_tag sub WHERE tag_id IN
(从 tbl_linking_talk_tag 中选择 tag_id，其中 talk_id = 1) 和 maintalk.talk_id=sub.talk_id) +
(SELECT count(*) FROM tbl_linking_speakers_talk sub1 WHERE slider_id IN
（从tbl_linking_speakers_talk中选择speaker_id，其中talk_id = 1）和maintalk.talk_id = sub1.talk_id
),talk_title,talk_brief
来自主讲
其中 talk_id != 1
group by talk_id

语法可能需要一些修复，但方法是

从 maintable 中选择列

Answer 2

我用这段代码让它工作：

 SELECT a.talk_id, a.talk_image, a.talk_title, a.talk_brief, count(b.talk_id)  AS common_term_count 
        FROM tbl_talks a LEFT OUTER JOIN
             (
             SELECT talk_id FROM tbl_linking_talk_tag WHERE tag_id IN 
                (SELECT tag_id FROM tbl_linking_talk_tag WHERE talk_id = 1)
             UNION ALL 
             SELECT talk_id FROM tbl_linking_speakers_talk WHERE speaker_id IN
                (SELECT speaker_id FROM tbl_linking_speakers_talk WHERE talk_id = 1) 
             ) AS b
             ON a.talk_id = b.talk_id       
        WHERE a.talk_id != 1        
        GROUP BY b.talk_id      
        ORDER BY common_term_count DESC