SQL 返回一组合并的结果 [英] SQL to return a merged set of results
问题描述
我有以下 SQL:
SELECT `table1`.`value`, `table2`.*
FROM `table2`
INNER JOIN `table1` ON `table2`.`product_id` = `table1`.`entity_id`
WHERE `table2`.`created_at` > '2012-04-23' and
(`table1`.`value` = 264 OR `table1`.`value` = 260)
order by order_id
返回这样的结果集(这只是返回结果的一部分):
Which returns a result set like this (This is only a part of the returned results):
value order_id ...
260 1234
260 1235
260 1236
264 1236
260 1237
260 1238
260 1239
264 1239
264 1240
260 1241
我想要的是一个查询,它将获取这些结果并且只返回 order_id 包含值 260 和 264 的订单.基于这个例子,我正在寻找的最终结果是
What I want is a query that will take these results and only return orders where the order_id contains both value 260 and 264. Based on this example, the end result I am looking for is
260 1236
264 1236
260 1239
264 1239
我的想法是这可以用一个子集来完成,但我不确定要完成它的细节.
My thought is this can be done with a subset but I am not exactly sure on the details to accomplish it.
推荐答案
这可以通过 关系划分:
select r.order_id from (
select
dividend.*
from your_table_or_query as dividend -- assumes no duplicates in `dividend`; use `distinct` if there are any
inner join divisor
on dividend.value = divisor.value
) as r
group by r.order_id
having count(*) = (select count(*) from divisor);
结果:
+----------+
| order_id |
+----------+
| 1236 |
| 1239 |
+----------+
2 rows in set (0.00 sec)
您的查询在哪里 your_table_or_query
和
where your query is your_table_or_query
and
select 260 as value from dual union select 264 as value from dual
是除数
.
这将返回订单 ID 1236 和 1239;然后可以将它们join
ed 到原始查询以获取具有这些订单 ID 的所有行(如果这是您想要的).
This will return the order ids 1236 and 1239; they can then be join
ed to original query to get all the rows with those order ids if that's what you want.
完整查询和插入语句:
create table divisor (value int);
insert into divisor values (260), (264);
create table your_table_or_query (value int, order_id int);
insert into your_table_or_query values (260, 1234), (260, 1235), (260, 1236), (264, 1236), (260, 1237), (260, 1238), (260, 1239), (264, 1239), (264, 1240), (260, 1241);
select y.* from (
select r.order_id from (
select
dividend.*
from your_table_or_query as dividend
inner join divisor
on dividend.value = divisor.value
) as r
group by r.order_id
having count(*) = (select count(*) from divisor)
) as quotient
inner join your_table_or_query y
on quotient.order_id = y.order_id;
结果:
+-------+----------+
| value | order_id |
+-------+----------+
| 260 | 1236 |
| 264 | 1236 |
| 260 | 1239 |
| 264 | 1239 |
+-------+----------+
4 rows in set (0.00 sec)
这篇关于SQL 返回一组合并的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!