MySQL-将两个select语句有效地组合成一个带有LIMIT的结果 [英] MySQL - Combining two select statements into one result with LIMIT efficiently
问题描述
对于约会应用程序，我需要几个表来查询单个输出，并且两个查询的合并限制为10.尽管分开查询不是问题，但目前看来这很困难，但是LIMIT 10不能工作，因为数字不准确(例如LIMIT 5和LIMIT 5，一个查询可能返回0行) ，而其他10个，视情况而定.)
For a dating application, I have a few tables that I need to query for a single output with a LIMIT 10 of both queries combined. It seems difficult to do at the moment, even though it's not an issue to query them separately, but the LIMIT 10 won't work as the numbers are not exact (ex. not LIMIT 5 and LIMIT 5, one query may return 0 rows, while the other 10, depending on the scenario).
members table
member_id | member_name
------------------------
1 Herb
2 Karen
3 Megan
dating_requests
request_id | member1 | member2 | request_time
----------------------------------------------------
1 1 2 2012-12-21 12:51:45
dating_alerts
alert_id | alerter_id | alertee_id | type | alert_time
-------------------------------------------------------
5 3 2 platonic 2012-12-21 10:25:32
dating_alerts_status
status_id | alert_id | alertee_id | viewed | viewed_time
-----------------------------------------------------------
4 5 2 0 0000-00-00 00:00:00
假设您是Karen，并且刚刚登录，您应该看到以下两项:
Imagine you are Karen and just logged in, you should see these 2 items:
1. Herb requested a date with you.
2. Megan wants a platonic relationship with you.
在一个限制为10的查询中.这是两个需要组合的查询:
In one query with a LIMIT of 10. Instead here are two queries that need to be combined:
1. Herb requested a date with you.
-> query = "SELECT dr.request_id, dr.member1, dr.member2, m.member_name
FROM dating_requests dr
JOIN members m ON dr.member1=m.member_id
WHERE dr.member2=:loggedin_id
ORDER BY dr.request_time LIMIT 5";
2. Megan wants a platonic relationship with you.
-> query = "SELECT da.alert_id, da.alerter_id, da.alertee_id, da.type,
da.alert_time, m.member_name
FROM dating_alerts da
JOIN dating_alerts_status das ON da.alert_id=das.alert_id
AND da.alertee_id=das.alertee_id
JOIN members m ON da.alerter_id=m.member_id
WHERE da.alertee_id=:loggedin_id AND da.type='platonic'
AND das.viewed='0' AND das.viewed_time<da.alert_time
ORDER BY da.alert_time LIMIT 5";
同样，有时两个表都可能是空的，或者1个表可能是空的，或者两个表都已满(其中LIMIT 10插入)并按时间排序.关于如何获取查询以有效执行此任务的任何想法?欢迎提出想法，建议，提示音和优化.
Again, sometimes both tables may be empty, or 1 table may be empty, or both full (where LIMIT 10 kicks in) and ordered by time. Any ideas on how to get a query to perform this task efficiently? Thoughts, advice, chimes, optimizations are welcome.
推荐答案
您可以将多个查询与 UNION ，但前提是查询的列数相同.理想情况下，列是相同的，不仅在数据类型上，而且在语义上也一样.但是，MySQL不在乎语义，而是通过转换为更通用的东西来处理不同的数据类型-因此，如有必要，您可以可以重载各列以使每个表具有不同的含义，然后确定什么含义适用于您的高级代码(尽管我不建议您这样做).
You can combine multiple queries with UNION, but only if the queries have the same number of columns. Ideally the columns are the same, not only in data type, but also in their semantic meaning; however, MySQL doesn't care about the semantics and will handle differing datatypes by casting up to something more generic - so if necessary you could overload the columns to have different meanings from each table, then determine what meaning is appropriate in your higher level code (although I don't recommend doing it this way).
当列数不同时，或者要从两个查询中获得更好/更少的数据重载对齐方式时，可以在SELECT语句中插入虚拟文字列.例如:
When the number of columns differs, or when you want to achieve a better/less overloaded alignment of data from two queries, you can insert dummy literal columns into your SELECT statements. For example:
SELECT t.cola, t.colb, NULL, t.colc, NULL FROM t;
您甚至可以为第一个表保留一些列，为第二个表保留另一些列，以使它们在其他位置为NULL(但请记住，列名来自第一个查询，因此您可能希望确保它们全部都在那里命名):
You could even have some columns reserved for the first table and others for the second table, such that they are NULL elsewhere (but remember that the column names come from the first query, so you may wish to ensure they're all named there):
SELECT a, b, c, d, NULL AS e, NULL AS f, NULL AS g FROM t1
UNION ALL -- specify ALL because default is DISTINCT, which is wasted here
SELECT NULL, NULL, NULL, NULL, a, b, c FROM t2;
您可以尝试以这种方式对齐两个查询，然后将它们与UNION运算符组合；通过将LIMIT应用于UNION，您即将实现目标:
You could try aligning your two queries in this fashion, then combining them with a UNION operator; by applying LIMIT to the UNION, you're close to achieving your goal:
(SELECT ...)
UNION
(SELECT ...)
LIMIT 10;
剩下的唯一问题是，如上所述，第一个表中的10条或更多记录将推出"第二个表中的任何记录.但是，我们可以在外部查询中使用ORDER BY来解决此问题.
The only issue that remains is that, as presented above, 10 or more records from the first table will "push out" any records from the second. However, we can utilise an ORDER BY in the outer query to solve this.
将它们放在一起:
(
SELECT
dr.request_time AS event_time, m.member_name, -- shared columns
dr.request_id, dr.member1, dr.member2, -- request-only columns
NULL AS alert_id, NULL AS alerter_id, -- alert-only columns
NULL AS alertee_id, NULL AS type
FROM dating_requests dr JOIN members m ON dr.member1=m.member_id
WHERE dr.member2=:loggedin_id
ORDER BY event_time LIMIT 10 -- save ourselves performing excessive UNION
) UNION ALL (
SELECT
da.alert_time AS event_time, m.member_name, -- shared columns
NULL, NULL, NULL, -- request-only columns
da.alert_id, da.alerter_id, da.alertee_id, da.type -- alert-only columns
FROM
dating_alerts da
JOIN dating_alerts_status das USING (alert_id, alertee_id)
JOIN members m ON da.alerter_id=m.member_id
WHERE
da.alertee_id=:loggedin_id
AND da.type='platonic'
AND das.viewed='0'
AND das.viewed_time<da.alert_time
ORDER BY event_time LIMIT 10 -- save ourselves performing excessive UNION
)
ORDER BY event_time
LIMIT 10;
当然，现在由您决定在读取结果集中的每条记录时要处理的行类型(建议您测试request_id和/或alert_id的NULL值；或者可以在结果中添加一列，以明确说明每条记录从哪个表起源，但是如果这些id列为NOT NULL，则应该等效.
Of course, now it's up to you to determine what type of row you're dealing with as you read each record in the resultset (suggest you test request_id and/or alert_id for NULL values; alternatively one could add an additional column to the results that explicitly states from which table each record originated, but it should be equivalent provided those id columns are NOT NULL).
这篇关于MySQL-将两个select语句有效地组合成一个带有LIMIT的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
