在 Mysql 中合并两个查询 [英] Merge two queries in Mysql
问题描述
我有一个包含所有用户视频下载历史记录的表格:
I have a table which includes all the users video downloading history:
src_ip vlan_id video_id area date_pl
aaaa A 1 123 xxxx-xx-xx
aaaa A 2 123 xxxx-xx-xx
aaaa B 1 456 xxxx-xx-xx
bbbb A 4 123 xxxx-xx-xx
bbbb C 6 567 xxxx-xx-xx
...
我使用 src_ip 和 vlan_id 的组合来识别不同的用户,现在我想随机选择 50 个不同的用户(这意味着 src_ip 和 vlan_id 的 50 个不同的组合)并列出他们所有的下载历史.
I use the combination of src_ip and vlan_id to identify different users, Now I want to randomly select 50 different users (which means 50 distinct combinations of src_ip and vlan_id )and list all their downloading history.
所以我首先做了:
SELECT distinct src_ip, vlan_id from video_2 as table2
WHERE date_pl >= '2011-11-29 00:00' AND date_pl <= '2011-12-05 23:55' order by rand() limit 50
然后我做到了:
SELECT src_ip, vlan_id, video_id, area from video_2
where video_2.src_ip = table2.src_ip AND video_2.vlan_id = table2.vlan_id
AND date_pl >= '2011-11-29 00:00' AND date_pl <= '2011-12-05 23:55'
但问题是,由于它们是两个独立的查询,在第二个查询中,它不知道 table2 是什么.
But the problem is, since they are two independent queries, in the second query, it doesn't know what table2 is.
我该如何解决这个问题或者如何将这两个查询合二为一?
How can I solve this problem or how to merge these two queries into one?
推荐答案
CREATE TEMPORARY TABLE table2
AS
SELECT distinct src_ip, vlan_id from video_2
WHERE date_pl >= '2011-11-29 00:00' AND date_pl <= '2011-12-05 23:55'
ORDER BY rand()
LIMIT 50;
SELECT src_ip, vlan_id, video_id, area
FROM
video_2
JOIN
table2 ON video_2.src_ip = table2.src_ip AND video_2.vlan_id = table2.vlan_id';
您可以 不能在行中派生table2",因为限制在子查询中使用LIMIT.否则就是
SELECT
v2.src_ip, v2.vlan_id, v2.video_id, v2.area
FROM
video_2 v2
JOIN
(SELECT distinct v.src_ip, v.vlan_id from video_2 v
WHERE v.date_pl >= '2011-11-29 00:00' AND v.date_pl <= '2011-12-05 23:55'
ORDER BY rand()
LIMIT 50
) table2 ON v2.src_ip = table2.src_ip AND v2.vlan_id = table2.vlan_id;
子查询中的限制 LIMIT 主要适用于 IN,而不适用于派生表
Restrictions LIMITs in subqueries apply to IN mostly, not derived tables
编辑 2:添加了更多别名
Edit 2: added more aliases
这篇关于在 Mysql 中合并两个查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!