MySQL - 如何简化这个查询？

问题描述：

select 
     sequence, 
     1 added 
     from scoredtable 
     where score_timestamp=1292239056000 
     and sequence 
     not in (select sequence from scoredtable where score_timestamp=1292238452000) 
     union 
select 
     sequence, 
     0 added 
     from scoredtable 
     where score_timestamp=1292238452000 
     and sequence 
     not in (select sequence from scoredtable where score_timestamp=1292239056000);

任何想法？基本上我想从同一个表中提取两个时间戳值之间不同的序列。用一个“添加”的列来表示一行是新的还是一行已被删除。

源表：

score_timestamp    sequence 
1292239056000    0 
1292239056000    1 
1292239056000    2 
1292238452000    1 
1292238452000    2 
1292238452000    3

（2行）（1292239056000，1292238452000）查询结果之间示例：

sequence added 
3  1 
0  0

之间实施例（1292238452000，1292239056000）查询结果（2行）：

sequence added 
0  1 
3  0

（129 2239056000，1292239056000）查询结果（0行）：

sequence added

答

此查询获取所有sequences这两个时间戳内仅出现一次，并检查它是否发生用于第一或第二时间戳。

SELECT 
    sequence, 
    CASE WHEN MIN(score_timestamp) = 1292239056000 THEN 0 ELSE 1 END AS added 
FROM scoredtable 
WHERE score_timestamp IN (1292239056000, 1292238452000) 
    AND (1292239056000 <> 1292238452000) -- No rows, when timestamp is the same 
GROUP BY sequence 
HAVING COUNT(*) = 1

它返回你期望的结果：

sequence added 
3  1 
0  0

+1彼得，我错过/搞砸了我的理解:) – 2010-12-13 12:53:33

太棒了！非常感谢！ – 2010-12-13 13:08:29

糟糕！这不是同一个查询！我添加了更多的例子，这不满意这个查询:(例如，当与相同的时间戳比较或比较逆向顺序（我想比较较低的时间戳也）... – 2010-12-13 14:01:30

答

给定两个时间戳

SET @ts1 := 1292239056000 
SET @ts2 := 1292238452000

，你可以得到你的补充，并与删除：根据数量

SELECT s1.sequence AS sequence, 0 as added 
FROM scoredtable s1 LEFT JOIN 
    scoredtable s2 ON 
         s2.score_timestamp = @ts2 AND 
         s1.sequence = s2.sequence 
WHERE 
    s1.score_timestamp = @ts1 AND 
    s2.score_timestampe IS NULL 
UNION ALL 
SELECT s2.sequence, 1 
FROM scoredtable s1 RIGHT JOIN 
    scoredtable s2 ON s1.score_timestamp = @ts1 AND 
         s1.sequence = s2.sequence 
WHERE 
    s2.score_timestamp = @ts2 AND 
    s1.score_timestampe IS NULL

行和统计信息cs上面的查询可能会表现更好，然后按group by和count（*）= 1版本（我认为总是需要全表扫描，而上面的联合应该可以做2 x anti-join这可能会更好）

如果你有大量的数据集，请让我们知道哪个更快（与SQL_NO_CACHE测试比较结果）

MySQL - 如何简化这个查询？

相关推荐