mariadb 每用户单列的Mysql datediff

relj7zay 于 7个月前发布在 Mysql

关注(0)|答案(2)|浏览(76)

我在SQL中有这样一个表：
| | | |
| --|--|--|
| 1 |user1| 2023-07-10 2023-07-10|
| 2 |user1| 2023-06-25|
| 3 |user2| 2023-06-21 2023-06-21 2023-06-21|
| 4 |user3| 2023-06-27|
| 5 |user3| 2023-07-11 2023-07-11 2023-07-11|
| 6 |用户4| 2023-07-14 2023-07-14 2023-07-14|
| 7 |user2| 2023-07-16|
| 8 |user1| 2023-07-17|
| 9 |用户4| 2023-07-15|
| 10 |使用者5| 2023-03-06|
我需要计算每个连续日期的差异，但每个用户（类似这样）：
| | | | |
| --|--|--|--|
| user1| 2023-06-25| 2023-07-10 2023-07-10| 15 |
| user1| 2023-06- 10 2023-06-10 2023-06-10| 2023-07-17| 7 |
| user2| 2023-06-21 2023-06-21 2023-06-21| 2023-07-16| 25 |
| user3| 2023-06-27| 2023-07-11 2023-07-11 2023-07-11| 14 |
等等。我已经在网上搜索过了，但是只找到了没有每个用户的解决方案。
更新：
我到达这个查询是非常好的，但输出有多次相同的日期作为开始，我不想要的：

SELECT 
    p1.id AS id,
    p1.name AS user,
    p1.date AS date1,
    p2.date AS date2,
    DATEDIFF(p2.date, p1.date) AS date_difference
FROM prova p1
JOIN prova p2 ON p1.name = p2.name AND p2.date > p1.date
ORDER BY p1.name, p1.date;

SQL Fiddle

mariadb

来源：https://stackoverflow.com/questions/77053334/mysql-datediff-with-single-column-per-user

2条答案

按热度按时间

ubby3x7f1#

找到了解决方案：

SELECT
    p1.id AS id,
    p1.name AS user,
    p1.date AS date1,
    p2.date AS date2,
    DATEDIFF(p2.date, p1.date) AS date_difference
FROM prova p1
JOIN prova p2 ON p1.name = p2.name AND p1.date < p2.date
LEFT JOIN prova p3 ON p1.name = p3.name AND p1.date < p3.date AND p3.date < p2.date
WHERE p3.id IS NULL
ORDER BY p1.name, p1.date

SQL Fiddle

赞(0）回复(0）举报 7个月前

zz2j4svz2#

使用这样的查询

SELECT `name`, `date`, DATEDIFF( 
    (SELECT `date` FROM prova sub 
        WHERE sub.`name` = main.`name` AND sub.`date` > main.`date` ORDER BY sub.`date` LIMIT 1)
        ,`date`) as nxttime 
  FROM prova AS main 
  order by `name`, `date`;

样品
http://sqlfiddle.com/#!9/aa0faf69/34

赞(0）回复(0）举报 7个月前

我来回答

mariadb 每用户单列的Mysql datediff

2条答案

相关问题

热门标签

最新问答