代码之家 › 专栏 › 技术社区 › machinatus

比使用“联合(a中的b)”更有效的sql?

where union sql

machinatus · 技术社区 · 15 年前

编辑1(澄清): 谢谢你到目前为止的回答!反应令人满意。
我想澄清一下这个问题,因为根据我的回答,我认为我没有正确地描述问题的一个方面(我确信这是我的错,因为我很难为自己定义问题)。
问题是:结果集应该只包含“2010-01-03”和“2010-01-09”之间带有tstamp的记录,并且一记录第一组中每个订单的tstamp为空的位置(将总是每个订单的tstamp为空。
目前给出的答案似乎包括 全部的 某个订单的记录任何 “2010-01-03”和“2010-01-09”之间的TSTAMP。例如,如果还有一个order_num=2且tstamp=2010-01-12 00:00:00的记录,则应该不包含在结果中。

原题:
考虑一个包含id(惟一)、order-num、tstamp(时间戳)和item-id(订单中包含的单个项)的orders表。tstamp为空,除非订单已被修改,在这种情况下,还有一条订单号和tstamp相同的记录,然后包含更改发生时的时间戳。

例子。。。

id  order_num  tstamp               item_id
__  _________  ___________________  _______
 0          1                           100
 1          2                           101
 2          2  2010-01-05 12:34:56      102
 3          3                           113
 4          4                           124
 5          5                           135
 6          5  2010-01-07 01:23:45      136
 7          5  2010-01-07 02:46:00      137
 8          6                           100
 9          6  2010-01-13 08:33:55      105

什么是最有效的sql语句来检索在特定日期范围内修改了一次或多次的所有订单(基于订单数量)?换言之,对于每个订单,我们需要具有相同订单号的所有记录(包括具有空tstamp的记录),对于每个订单号,其中至少有一个订单号在“2010-01-03”和“2010-01-09”之间具有tstamp不为空和tstamp。这是“至少有一个订单的tstamp不为空”我有困难。

结果集应如下所示:

id  order_num  tstamp               item_id
__  _________  ___________________  _______
 1          2                           101
 2          2  2010-01-05 12:34:56      102
 5          5                           135
 6          5  2010-01-07 01:23:45      136
 7          5  2010-01-07 02:46:00      137

我提出的sql基本上是“a union(b in a)”,但它执行缓慢,我希望有一个更有效的解决方案:

SELECT history_orders.order_id, history_orders.tstamp, history_orders.item_id
FROM
   (SELECT orders.order_id, orders.tstamp, orders.item_id
    FROM orders
    WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09')
    AS history_orders
UNION
SELECT current_orders.order_id, current_orders.tstamp, current_orders.item_id
FROM
   (SELECT orders.order_id, orders.tstamp, orders.item_id
    FROM orders
    WHERE orders.tstamp IS NULL)
    AS current_orders
WHERE current_orders.order_id IN
   (SELECT orders.order_id
    FROM orders
    WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09');

6 回复 | 直到 15 年前

jspcal 15 年前

可能是子查询:

select * from order o where o.order_num in (select distinct
  order_num from order where tstamp between '2010-01-03' and '2010-01-09')

AdaTheDev 15 年前

除非我误解了,否则像这样的事情应该能起到作用:

SELECT o1.id, o1.order_num, o.tstamp, o.item_id
FROM  orders o1
WHERE EXISTS(
    SELECT * FROM orders o2 
    WHERE o1.order_num = o2.order_num 
        AND o2.tstamp BETWEEN '2010-01-03' AND '2010-01-09')

使用exists的好处是一旦罚款第一场比赛就停止。

Jeff Atwood 15 年前

我知道现在回复已经很晚了,但是我刚刚看到这个帖子,我想我应该试试这个,这个查询怎么样,和上面所有的解决方案相比,它真的很小,并且解决了这个问题。

select * from orders_gc where order_num in 
    (select order_num
     from orders_gc 
     group by order_num 
     having count(id) > 1 and 
     MAX(tstamp) between '03-jan-2010' and '09-jan-2010')

Peter Lang 15 年前

希望我回答对了。这将返回在提供的时间戳内已更改的所有订单。

SELECT o.order_id, o.tstamp, o.item_id
FROM orders o
JOIN ( SELECT DISTINCT o2.order_num
       FROM orders o2
       WHERE o2.tstamp BETWEEN '2010-01-03' AND '2010-01-09' ) o3
ON ( o3.order_num = o.order_num )

Alison R. 15 年前

你可以自己加入这张桌子。简单地说,这看起来像:

select order_id
from orders all_orders
inner join orders not_null_orders
    on all_orders.order_id = not_null_orders.order_id
where
    not_null_orders.tstamp is not null
    and all_orders.tstamp between '2010-01-03' AND '2010-01-09'

machinatus 15 年前

再次感谢你的建议。我找到了三个可行的解决方案,包括我的原创方案。最后,我添加了一些性能结果,这些结果并没有我所希望的那么好。如果有人能在这方面有所改进,我会很激动的!

1)目前发现的最佳解决方案似乎是:

SELECT history_orders.order_id, history_orders.tstamp, history_orders.item_id
FROM
   (SELECT orders.order_id, orders.tstamp, orders.item_id
    FROM orders
    WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09'
    OR orders.tstamp IS NULL)
    AS history_orders
WHERE history_orders.order_id IN
   (SELECT orders.order_id
    FROM orders
    WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09');

2)我还尝试使用exists in代替in,这需要在最后一个select中添加一个where子句:

SELECT history_orders.order_id, history_orders.tstamp, history_orders.item_id
FROM
   (SELECT orders.order_id, orders.tstamp, orders.item_id
    FROM orders
    WHERE orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09'
    OR orders.tstamp IS NULL)
    AS history_orders
WHERE EXISTS
   (SELECT orders.order_id
    FROM orders
    WHERE history_orders.order_id = orders.order_id
    AND orders.tstamp BETWEEN '2010-01-03' AND '2010-01-09');

3)最后是我的原始解决方案,使用union。

评论:
为了评价表的大小,我实际的“真实世界”问题涉及4个表(与内部连接相连),分别包含98、2189、43897、785656条记录。

性能-我运行每个解决方案三次,以下是我的实际结果:
1:52,51,51秒
2:54、54、53秒
3:56、56、56秒