mysql - 如何在一个查询中合并不同的多个选择？

Question

是否可以将所有表合并到一个或两个查询中？

我尝试使用联合，但我需要访问查询中的每一行。

对我来说最大的问题是使用 ORDER BY 对所有这些进行排序。

例如，我需要在一个 div 中打印第一个查询行，在另一个 div 中打印第二个查询行，依此类推。

(SELECT 
      link as RelLink, 
      date as RelDate,
      title as RelTitle 
   FROM torrent 
   WHERE moderate = 1 
   ORDER BY date DESC LIMIT 0,12),
(SELECT 
      torrent as TorLink1, 
      date as TorDate1, 
      title as TorTitle1 
   FROM torrents 
   WHERE moderate = 1 AND genre = 1 
   ORDER BY date DESC LIMIT 10),
(SELECT 
      date as TorDate2, 
      torrent as TorLink2, 
      title as TorTitle2 
   FROM torrents 
   WHERE moderate = 1 AND genre = 2 
   ORDER BY date DESC LIMIT 10),
(SELECT 
      link as NewsLink1, 
      date as NewsDate1, 
      title as NewsTitle1 
   FROM news 
   WHERE moderate = 1
   ORDER BY date DESC LIMIT 0,10),
(SELECT 
      link as NewsLink2, 
      date as NewsDate2,
      title as NewsTitle2 
   FROM news 
   WHERE moderate = 1 
   ORDER BY date DESC LIMIT 10,10);

Answer 1

是的，可以按照您的要求进行。您的查询之间需要 UNION ALL。您还需要对要UNION组合在一起的每个项目中的列使用相同的别名。

以下是适合您的工作。我没有重复你所有的子查询

SELECT Link, Date, Title 
FROM (
    SELECT link as Link, date as Date, title as Title 
      FROM torrent  WHERE moderate = 1 ORDER BY date DESC LIMIT 0,12
    UNION ALL
    SELECT torrent as Link, date as Date, title as Title 
      FROM torrents  WHERE moderate = 1 AND genre = 1 ORDER BY date DESC LIMIT 10
    UNION ALL
    SELECT torrent as Link, date as Date, title as Title
      FROM torrents WHERE moderate = 1 AND genre = 2 ORDER BY date DESC LIMIT 10 
    UNION ALL
   /* etc etc */
) AS query
ORDER BY Date DESC

请注意如何通过使用相同的别名使所有列彼此一致？

如果您希望对这些项目进行重复数据删除，请使用 UNION 而不是 UNION ALL。由于重复数据删除过程，速度会慢一些，但您只处理几十行，因此这可能不是问题。