MYSQL:选择已更改的行，其中
MYSQL:选择已更改的行，其中
提问于 2013-06-13 16:14:54
1-
create table changes(snapdate date,value int, uid int);
insert into changes values
('2013-04-22', 0, 1 ),
('2013-04-21', 1,1 ),
('2013-04-20', 1,1 ),
('2013-04-19', 1,1 ),
('2013-04-19', 0,2 ),
('2013-04-19', 1,1 ),
('2013-04-18', 0,1 ),
('2013-04-17', 0,1 ),
('2013-04-17', 1,2 ),
('2013-04-16', 1,1 ),
('2013-04-16', 0 ,2); 2-
SELECT a.snapdate, a.value
FROM (
SELECT t1.*, COUNT(*) AS rank
FROM changes t1
LEFT JOIN changes t2 ON t1.snapdate >= t2.snapdate
GROUP BY t1.snapdate
) AS a
LEFT JOIN (
SELECT t1.*, COUNT(*) AS rank
FROM changes t1
LEFT JOIN changes t2 ON t1.snapdate >= t2.snapdate
GROUP BY t1.snapdate
) AS b ON a.rank = b.rank+1 AND a.value = b.value
WHERE b.snapdate IS NULL
ORDER BY a.snapdate DESC;运行良好，但如何选择WHERE uid=2为例？我不能使用临时表：
create temporary table changes_temp
as
select *
from changes
where uid = 2 http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html
回答 2
Stack Overflow用户
回答已采纳
发布于 2013-06-13 16:26:18
尝试在子查询中添加WHERE子句
SELECT a.snapdate, a.value
FROM (
SELECT t1.*, COUNT(*) AS rank
FROM changes t1
LEFT JOIN changes t2 ON t1.snapdate >= t2.snapdate
WHERE t2.uid=t1.uid AND t2.uid=2
GROUP BY t1.snapdate
) AS a
LEFT JOIN (
SELECT t1.*, COUNT(*) AS rank
FROM changes t1
LEFT JOIN changes t2 ON t1.snapdate >= t2.snapdate
WHERE t2.uid=t1.uid AND t2.uid=2
GROUP BY t1.snapdate
) AS b ON a.rank = b.rank+1 AND a.value = b.value
WHERE b.snapdate IS NULL
ORDER BY a.snapdate DESC;已创建用于测试的SQLFiddle。这是你想要的吗？您没有解释查询的目的
Stack Overflow用户
发布于 2013-06-13 16:25:51
添加where子句：
SELECT a.snapdate, a.value
FROM (
SELECT t1.*, COUNT(*) AS rank
FROM changes t1
LEFT JOIN changes t2 ON t1.snapdate >= t2.snapdate
WHERE t1.uid = 2
GROUP BY t1.snapdate
) AS a
LEFT JOIN (
SELECT t1.*, COUNT(*) AS rank
FROM changes t1
LEFT JOIN changes t2 ON t1.snapdate >= t2.snapdate
WHERE t1.uid = 2
GROUP BY t1.snapdate
) AS b ON a.rank = b.rank+1 AND a.value = b.value
WHERE b.snapdate IS NULL
ORDER BY a.snapdate DESC;sqlfiddle
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17082306
复制相关文章
相似问题
腾讯云开发者
