檢索用戶明智的累計達到記錄

我有鍛煉資料表，其中包含用戶騎自行車的詳細資訊以及他們每天達到的距離。我想在距離列中超過 1000 個值后獲取每個用戶記錄，如果該用戶未超過 1000，則可以獲得記錄的最大值。

我希望按 1000 個到達的用戶和日期按升序排列記錄，然后按距離降序排列。因此，只有我才能獲得第一個獲得的用戶，而下一個未獲得的用戶則相反。

Example Data:
Date       User        Distance
1614944833   1           100
1614944232   2           100
1624944831   1           150
1615944832   3           250
1614644836   1           500
1614954835   2           100
1614344834   3           100
1614964831   1           260
1614944238   1           200

我試過下面的累積總和查詢，但它不起作用，

select  date,
            sum(distance)  as distance,
            (case when (sum(distance) >= 1000)  
                  THEN  1000  ELSE sum(distance) END) as distance_completed  
from `workouts_data` as `w1` 
where `date` between 1609372800 and 1640995140 
and `w1`.`User` in (1,2,3)  group by `User` 
order by `distance_completed` desc,`date` asc

預期產出

Date       User        distance_completed
1614964831   1           1010
1614954835   2           200
1614344834   3           350 

任何人都可以建議使用正確的查詢或存盤程序方法嗎？

uj5u.com熱心網友回復：

你可以試試：

 select   w1.`user`, 
         case when t1.distance >='1000' 
         then '1000' else t1.distance end as distance,
         t3.date
from workouts_data w1
inner join (
               select   `user`,
                         sum(distance)  as `distance`           
               from `workouts_data`
               where `date` between 1609372800 and 1640995140
               and  `user` in (1,2,3)  
               group by `user`
             ) as t1 on w1.user=t1.user
inner join (
              select `date`,
                      id 
               from workouts_data
               where (id,user) in (select max(id),user from workouts_data group by user)
              ) as t3 on w1.date=t3.date                                  
order by t1.distance  desc;

結果：

user  distance    date
1     1000       1614964831
3     350        1614344834
2     200        1614954835

演示：https : //www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/164

uj5u.com熱心網友回復：

您可以通過這種方式使用總和，您需要一個外部 Select 來處理分組后的資料

我不明白，你為什么選擇這些日期，所以你需要檢查 MAX( date) 是否合適

SELECT 
    `Date`, `User`, IF(distance_completed > 1000, 1000,distance_completed)  as distance_completed
FROM (SELECT MAX(`Date`) as 'date', `User`, SUM(`Distance`) AS  distance_completed
FROM exdata w1
where `date` between 1609372800 and 1640995140 
and `w1`.`User` in (1,2,3)  group by `User` ) t1
order by `distance_completed` desc,`date` asc

      日期 | 用戶 | 距離_完成
---------: | ---: | -----------------：
1624944831 | 1 | 1000
1615944832 | 3 | 350
1614954835 | 2 | 200

db<>在這里擺弄

SELECT 
    `Date`, `User`, IF(distance_completed > 1000, 1000,distance_completed)  as distance_completed
FROM (SELECT MIN(`Date`) as 'date', `User`, SUM(`Distance`) AS  distance_completed
FROM exdata w1
where `date` between 1609372800 and 1640995140 
and `w1`.`User` in (1,2,3)  group by `User` ) t1
order by `distance_completed` desc,`date` asc

      日期 | 用戶 | 距離_完成
---------: | ---: | -----------------：
1614644836 | 1 | 1000
1614344834 | 3 | 350
1614944232 | 2 | 200

db<>在這里擺弄

當我們添加一個 id 時，您可以獲得想要的數字，但是這個應該給出的重要資訊一直存在于問題中。

SELECT 
    w2.`Date`, t1. `User`, IF(distance_completed > 1000, 1000,distance_completed)  as distance_completed
FROM (SELECT  `User`, SUM(`Distance`) AS  distance_completed
FROM workouts_data  w1
where `date` between 1609372800 and 1640995140 
and `w1`.`User` in (1,2,3)  group by `User` ) t1 
inner join (SELECT `Date` , `User` 
FROM workouts_data  wd2 
WHERE (id,`User`) IN (SELECT MAX(id),`User` FROM  workouts_data GROUP BY `User`)) w2 ON t1.`User` = w2.`User`
order by `distance_completed` desc,`date` asc

      日期 | 用戶 | 距離_完成
---------: | ---: | -----------------：
1624944831 | 1 | 950
1614344834 | 3 | 350
1614954835 | 2 | 200

db<>在這里擺弄

標籤：php mysql 数据库 存储过程 累积和

上一篇：如何使用Joins和Groupby撰寫查詢

下一篇：無法將列的值存盤在變數SQLTRIGGERS中