如何在SQL中在另一个select语句中编写select语句

Question

有人能告诉我这个查询有什么问题吗？它在第二个select附近给出了语法错误。

SELECT b.mc_boxes_idmc_boxes,
          t.idtitles, 
          t.title,
          t.languages_idlanguages,
          MAX(h.idtitle_history),
          MAX(h.edition)
          (SELECT h.preview, h.file WHERE h.idtitle_history = MAX(h.idtitle_history))
                        FROM mc_boxes_has_titles b
                        LEFT JOIN titles t ON b.titles_idtitles = t.idtitles
                        LEFT JOIN title_history h ON h.titles_idtitles = t.idtitles
                        WHERE b.mc_boxes_idmc_boxes = 12
                        AND h.edition IS NOT NULL
                        GROUP BY b.mc_boxes_idmc_boxes, idtitles
                        ORDER BY b.sortorder;

- Faryal Khan

Answer 1

看起来你在 MAX(h.edition) 后面漏了一个逗号。

SELECT b.mc_boxes_idmc_boxes,
          t.idtitles, 
          t.title,
          t.languages_idlanguages,
          MAX(h.idtitle_history),
          MAX(h.edition), 
          (SELECT h.preview, h.file WHERE h.idtitle_history = MAX(h.idtitle_history))
FROM mc_boxes_has_titles b
LEFT JOIN titles t ON b.titles_idtitles = t.idtitles
LEFT JOIN title_history h ON h.titles_idtitles = t.idtitles
WHERE b.mc_boxes_idmc_boxes = 12
     AND h.edition IS NOT NULL
GROUP BY b.mc_boxes_idmc_boxes, idtitles
ORDER BY b.sortorder;

除了逗号之外，您在子查询中选择了两个字段。

SELECT b.mc_boxes_idmc_boxes,
          t.idtitles, 
          t.title,
          t.languages_idlanguages,
          MAX(h.idtitle_history),
          MAX(h.edition), 
          (SELECT preview FROM title_history WHERE idtitle_history = MAX(h.idtitle_history)),
          (SELECT [file] FROM title_history WHERE idtitle_history = MAX(h.idtitle_history))
FROM mc_boxes_has_titles b
LEFT JOIN titles t ON b.titles_idtitles = t.idtitles
LEFT JOIN title_history h ON h.titles_idtitles = t.idtitles
WHERE b.mc_boxes_idmc_boxes = 12
     AND h.edition IS NOT NULL
GROUP BY b.mc_boxes_idmc_boxes, idtitles
ORDER BY b.sortorder;

- Taryn

---

您的SQL语法存在错误，请检查与您的MySQL服务器版本相对应的手册，以了解在第7行使用的正确语法。 - Faryal Khan

我对上面的子查询进行了轻微的更改，以包括一个FROM yourtable。 - Taryn

实际上，我想根据idtitle_history获取预览和文件列。如果idtitle_history为219，则应该为该ID提供预览和文件，但是单个选择无法提供。 - Faryal Khan

啊哈哈...它起作用了，但我从文件中删除了括号...非常感谢您的帮助 :) - Faryal Khan

Answer 2

将其别名为虚拟表。类似这样更改

SELECT b.mc_boxes_idmc_boxes,
          t.idtitles, 
          t.title,
          t.languages_idlanguages,
          MAX(h.idtitle_history),
          MAX(h.edition)
          (SELECT h.preview, h.file WHERE h.idtitle_history = MAX(h.idtitle_history))
                        FROM mc_boxes_has_titles b
                        LEFT JOIN titles t ON b.titles_idtitles = t.idtitles
                        LEFT JOIN title_history h ON h.titles_idtitles = t.idtitles
                        WHERE b.mc_boxes_idmc_boxes = 12
                        AND h.edition IS NOT NULL
                        GROUP BY b.mc_boxes_idmc_boxes, idtitles
                        ORDER BY b.sortorder) as virtual_column_alias;

看看这个。希望它能够正常工作。

- Hamza Waqas

Answer 3

补充bluefeet的回答，您可能需要检查保留字。例如，在SQL Server中，“File”是一个保留字。

- Otávio Décio