根据其他行的值调整min(date)

ih99xse1 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(269)

表格：

+----+--------+------------+------+---------+-----------------+
| id | userID | difference | type | context |      time       |
+----+--------+------------+------+---------+-----------------+
| 83 |    111 |         30 |      |         | 7/15/2019 15:23 |
| 84 |    111 |         10 |      |         | 7/16/2019 15:28 |
| 85 |    111 |        -10 |      | Reset   | 7/16/2020 15:28 |
| 86 |    222 |         50 |      |         | 7/8/2020 15:28  |
| 87 |    222 |        -10 |      | Reset   | 7/8/2020 15:28  |
| 88 |    333 |        -10 |      | Reset   | 5/11/2020 13:15 |
| 89 |    333 |         10 |      |         | 7/16/2019 13:16 |
| 91 |    111 |         20 |      |         | 7/17/2019 23:15 |
+----+--------+------------+------+---------+-----------------+

我正在寻找一个查询，它为每个最小日期为'16/7/2019'且带有exeption的userid返回“difference”（>0）之和，如果这个userid在上下文行中有一个重置，那么必须忽略一个小于重置记录位置的最小日期。
所以输出应该如下所示：

+--------+-------------+
| userID | SUM(points) |
+--------+-------------+
|    111 |          30|
+--------+-------------+

我的尝试：

SELECT userID, SUM(case when difference > 0 then difference else 0 end),    
FROM test WHERE (CASE 
WHEN (Select MAX(CAST(time as DATE)) where context ='Reset') > (Select 
MIN(CAST(time as DATE))) THEN (Select MAX(CAST(time as DATE)) where 
context ='Reset') ELSE (Select MIN(CAST(time as DATE))) END) = '7/16/2019' group by userID

下面是创建标签的步骤：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  `difference` int(11) NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `context` text COLLATE utf8_unicode_ci NOT NULL,
  `time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `test` (`id`, `userID`, `difference`, `type`, `context`, `time`)    VALUES
(83, 111, 30, '', '', '2019-07-15 15:23:22'),
(84, 111, 10, '', '', '2019-07-16 15:28:27'),
(85, 111, -10, '', 'Reset', '2020-07-16 15:28:27'),
(86, 222, 50, '', '', '2020-07-08 15:28:27'),
(87, 222, -10, '', 'Reset', '2020-07-08 15:28:27'),
(88, 333, -10, '', 'Reset', '2020-05-11 13:15:05'),
(89, 333, -10, '', '', '2019-07-16 13:16:35'),
(91, 111, 20, '', '', '2019-07-17 23:15:57');

sql mysql

来源：https://stackoverflow.com/questions/62829992/adjust-mindate-based-on-other-rows-value

1条答案

按热度按时间

oxcyiej71#

试试这样的。对于每个用户，找到重置日期并按旧记录筛选

SELECT 
    t1.userId, SUM(points)
FROM 
    table1 t1
LEFT JOIN (
    SELECT t2.userId, max(t2.date) date
    FROM table2 t2 
    WHERE context = "Reset"
    GROUP BY t2.userId
) resetDates on resetDates.userId = t1.userId
WHERE 
    t1.date >= IFNULL(resetDates.date, "01-01-2020")
GROUP BY 
    t1.userId;

赞(0）回复(0）举报 2021-07-24

我来回答

根据其他行的值调整min(date)

1条答案

相关问题

热门标签

最新问答