mysql - 用mysql连接两个表并按日期排序

Question

我已经研究了这个问题的许多假设的解决方案，但我仍然无法让它发挥作用。

SELECT Year FROM (SELECT DISTINCT YEAR(detDate1) AS Year 
FROM growl_details WHERE publish = 'y' AND pubNews = 'y' 
UNION 
SELECT DISTINCT YEAR(detDate1) AS Year FROM growl_pl_details
WHERE publish = 'y' AND pubNews = 'y') AS t ORDER BY 'Year' ASC

我有两个表，growl_details和growl_pl_details，我试图让它们只显示信息存在的年份，然后这将用于创建一个表，其中包含与该年份相关的数据页面的链接。

显然，我希望年份按顺序显示，但上面的代码似乎是单独对它们进行排序，并且它们相互遵循......

有什么想法吗？

Answer 1

您的查询(已格式化以便我可以阅读):

SELECT Year
FROM (SELECT DISTINCT YEAR(detDate1) AS Year
      FROM growl_details
      WHERE publish = 'y' AND pubNews = 'y'
      UNION
      SELECT DISTINCT YEAR(detDate1) AS Year
      FROM growl_pl_details
      WHERE publish = 'y' AND pubNews = 'y'
     ) AS t
ORDER BY 'Year' ASC;

该问题是由 order by 子句引起的。尝试将单引号更改为反引号:

ORDER BY `Year` ASC;

单引号用于字符串常量。反引号包围事物的名称。因此，您的原始查询是按常量排序的——这会产生任意排序。