I'm a little lost as how to run two mysql queries as one(return one result set to be sorted etc).
我有点迷失了，如何将两个mysql查询作为一个查询(返回一个结果集进行排序等)。
I understand how to do multiple JOINS but need to combine the below which is more than just a multiple join - it would include a multiple where etc.
我知道如何进行多个连接，但需要合并以下内容，这不仅仅是一个多个连接——它将包含多个where等等。
1st query
1号查询
sql = "SELECT s.id, s.song_name
FROM `songs` as s
INNER JOIN `artists` as a ON s.artist_id = a.id
WHERE ((`a`.id = #{search}))"
2nd query
2号查询
sql = "SELECT s.id, s.song_name
FROM `songs` as s
INNER JOIN `similarments` as si ON s.artist_id = si.artist_id
WHERE ((`si`.similar_id = #{search}))"
And then run both queries at once so I can ORDER them etc. Or combine them as one big query (maybe put an OR somewhere)?
然后同时运行这两个查询，以便我可以对它们进行排序等等。或者将它们合并为一个大查询(可能是一个或某个地方)?
Thanks!
谢谢!
1 个解决方案
#1
4
The simple way to run two queries and combine the results is to use UNION (or UNION ALL if you don't wish to remove duplicates). In your case it would look like this:
运行两个查询并合并结果的简单方法是使用UNION(或UNION ALL，如果不希望删除重复的查询)。你的情况是这样的:
(
SELECT s.id, s.song_name
FROM `songs` as s
INNER JOIN `artists` as a ON s.artist_id = a.id
WHERE ((`a`.id = #{search}))
)
UNION
(
SELECT s.id, s.song_name
FROM `songs` as s
INNER JOIN `similarments` as si ON s.artist_id = si.artist_id
WHERE ((`si`.similar_id = #{search}))
)
ORDER BY ....
#1
4
The simple way to run two queries and combine the results is to use UNION (or UNION ALL if you don't wish to remove duplicates). In your case it would look like this:
运行两个查询并合并结果的简单方法是使用UNION(或UNION ALL，如果不希望删除重复的查询)。你的情况是这样的:
(
SELECT s.id, s.song_name
FROM `songs` as s
INNER JOIN `artists` as a ON s.artist_id = a.id
WHERE ((`a`.id = #{search}))
)
UNION
(
SELECT s.id, s.song_name
FROM `songs` as s
INNER JOIN `similarments` as si ON s.artist_id = si.artist_id
WHERE ((`si`.similar_id = #{search}))
)
ORDER BY ....