sqlite：使用复合选择查询附加列

发布于 2024-10-16 07:50:15 字数 534 浏览 0 评论 0原文

我有一个包含此列的表：

| name | city | time | version |

我使用此查询来查找两个版本之间具有新地址的所有行：

select name, city from adresses where version = 2 
except 
select name, city from adresses where version = 1

它正在工作，但我还想检索时间值，但每个版本之间的时间值发生变化。

我想要这样的东西：

NOT WORKING
select time from adresses where version = 2 and 
(select name, city from adresses where version = 1 
except select name, city from adresses where version = 2);

有人知道我该怎么做吗？

谢谢

原文

I have a table containing this columns :

| name | city | time | version |

I use this query to find all rows with new adresses between 2 versions :

select name, city from adresses where version = 2 
except 
select name, city from adresses where version = 1

It's working but i would like to retrieve also time value but time value change between each version.

I would like something like that :

NOT WORKING
select time from adresses where version = 2 and 
(select name, city from adresses where version = 1 
except select name, city from adresses where version = 2);

Someone know how i can do that ?

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦魇绽荼蘼 2024-10-23 07:50:15

不确定我是否正确理解您的问题，但根据您编写的查询，我认为这就是您真正想要的：

SELECT time FROM adresses a
INNER JOIN (
   select name, city from adresses where version = 1 
   except 
   select name, city from adresses where version = 2
) new ON new.name = a.name AND new.city = a.city
WHERE version = 2

如果您正在寻找记录的两个版本之间的时间差（这也是您在问题）那么你可以这样做：

SELECT a2.*, a2.time - a1.time as time_diff
FROM adresses a1
INNER JOIN adresses a2 ON a1.name = a2.name AND a1.city = a2.city
WHERE a1.version = 1 AND a2.version = 2

Not sure I'm understanding your question correctly, but based on the query you wrote, I think this is what you actually wanted:

SELECT time FROM adresses a
INNER JOIN (
   select name, city from adresses where version = 1 
   except 
   select name, city from adresses where version = 2
) new ON new.name = a.name AND new.city = a.city
WHERE version = 2

If you are looking for the time difference between two versions of a record (which is what you also mention in your question) then you could do something like this:

SELECT a2.*, a2.time - a1.time as time_diff
FROM adresses a1
INNER JOIN adresses a2 ON a1.name = a2.name AND a1.city = a2.city
WHERE a1.version = 1 AND a2.version = 2

回复收藏 0 原文

~没有更多了~