结合多个高级mysql选择查询的最佳方法 [英] Best way to combine multiple advanced mysql select queries

问题描述

我在同一数据库的不同表中有多个select语句.我当时使用多个单独的查询，然后加载到我的数组中并进行排序(同样，在查询中排序之后).

我想合并为一条语句以加快结果并使其更容易加载更多"(请参阅​​底部).

每个查询使用的SELECT，LEFT JOIN，WHERE和ORDER BY命令对于每个表都不相同.

我可能不需要在每个语句中使用by，但是我希望最终结果最终由表示时间的字段(不一定在所有表中都使用相同的字段名称)进行排序.

我想将总查询结果限制为一个数字，以我的情况为100.

然后，我使用遍历结果的循环，并针对每一行测试是否设置了OBJECTNAME_ID(即comment_id，event_id，upload_id)，然后测试LOAD_WHATEVER_OBJECT来获取该行并将数据推入数组.

我以后不必对数组进行排序，因为它是通过mysql按顺序加载的.

稍后在应用程序中，我将跳过前100、200或任何页面* 100，然后通过相同的查询再次限制100，从而加载更多".

数据库的最终结果将看起来像"this":

结果-从表中选择的字段-要排序的字段最大 结果-从可能不同的表中选择的字段-要排序的字段次之 结果-从可能不同的表表中选择的字段-要排序的字段排第三 等，等等

我看到了很多更简单的组合语句，但是没有什么比这更复杂的了.

任何帮助将不胜感激.

解决方案

最简单的方法可能是在此处使用UNION(

Each query uses SELECT, LEFT JOIN, WHERE and ORDER BY commands which are not the same for each table.

I may not need order by in each statement, but I want the end result, ultimately, to be ordered by a field representing a time (not necessarily the same field name across all tables).

I would want to limit total query results to a number, in my case 100.

I then use a loop through results and for each row I test if OBJECTNAME_ID (ie; comment_id, event_id, upload_id) isset then LOAD_WHATEVER_OBJECT which takes the row and pushes data into an array.

I won't have to sort the array afterwards because it was loaded in order via mysql.

Later in the app, I will "load more" by skipping the first 100, 200 or whatever page*100 is and limit by 100 again with the same query.

The end result from the database would pref look like "this":

RESULT - selected fields from a table - field to sort on is greatest RESULT - selected fields from a possibly different table - field to sort on is next greatest RESULT - selected fields from a possibly different table table - field to sort on is third greatest etc, etc

I see a lot of simpler combined statements, but nothing quite like this.

Any help would be GREATLY appreciated.

解决方案

easiest way might be a UNION here ( http://dev.mysql.com/doc/refman/5.0/en/union.html ):

(SELECT a,b,c FROM t1)
UNION
(SELECT d AS a, e AS b, f AS c FROM t2)
ORDER BY a DESC

这篇关于结合多个高级mysql选择查询的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！