时间戳

首页 > 解决方案 > 使用“OR”和“HAVING SUM”Mysql 5.7

mysql - 使用“OR”和“HAVING SUM”Mysql 5.7

问题描述

这是我的小提琴：https ://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=71b1fd5d8e222ab1c51ace8d1af4c94f

CREATE TABLE order_match(ID int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
quantity decimal(10,2), createdAt date NOT NULL, order_status_id int(10) NOT NULL,
createdby int(11), code_order varchar(20) NOT NULL);



insert into order_match values
(1, 0.2, '2020-02-02', 6, 01, 0001),
(2, 1, '2020-02-03', 7, 02, 0002),
(3, 1.3, '2020-02-04', 7, 03, 0003),
(4, 1.4, '2020-02-08', 5, 08, 0004),
(5, 1.2, '2020-02-05', 8, 04, 0005),
(6, 1.4, '2020-03-01', 8, 05, 0006),
(7, 0.23, '2020-01-01', 8, 03, 0007),
(8, 2.3, '2020-02-07', 8, 04, 0009);

然后这是我的桌子

select order_status_id, createdby, createdAt from order_match;

    +-----------------+-----------+------------+
    | order_status_id | createdby | createdAt  |
    +-----------------+-----------+------------+
    |               6 |         1 | 2020-02-02 |
    |               7 |         2 | 2020-02-03 |
    |               7 |         3 | 2020-02-04 |
    |               5 |         8 | 2020-02-08 |
    |               8 |         4 | 2020-02-05 |
    |               8 |         5 | 2020-03-01 |
    |               8 |         3 | 2020-01-01 |
    +-----------------+-----------+------------+

order_status_id 为交易状态，“7”表示未批准交易，否则为批准，createdby 为进行交易的用户id，createdAt 为交易发生的日期。

所以我想找出在'2020-02-01'和'2020-02-28'之间进行交易的重复用户，重复用户是在'2020-02-28'之前进行批准交易并且至少在做的用户在日期范围内（'2020-02-01' 到 '2020-02-28'）再进行 1 次批准交易

根据我使用此查询的解释：

SELECT s1.createdby
               FROM order_match s1
               WHERE s1.order_status_Id in (4, 5, 6, 8)
               GROUP BY s1.createdby
               HAVING SUM(s1.createdAt BETWEEN '2020-02-01' AND '2020-02-28')
                  AND SUM(s1.createdAt <= '2020-02-28') > 1
                  OR exists (select 1 from order_match s1 where 
                              s1.createdAt < '2020-02-01'
                              and s1.order_status_id in (4, 5, 6, 8));

从那个查询，结果是这样的：

+-----------+
| createdby |
+-----------+
|         1 |
|         3 |
|         4 |
|         5 |
|         8 |
+-----------+

基于数据和解释的预期结果是这样的：

+-----------+
| createdby |
+-----------+
| null      |
+-----------+

因为没有符合“重复用户”条件的用户。我的错在哪里？

标签： mysql

解决方案

好像

SELECT createdby
FROM order_match
-- select rows in specified data range
WHERE createdAt BETWEEN '2020-02-01' AND '2020-02-28'
GROUP BY createdby
-- check that user has more than one transaction which' status is not non-approved
HAVING SUM(order_status_id != 7) > 1 -- or SUM(order_status_id in (4, 5, 6, 8)) > 1

这就是为什么我在“2020-02-01”之前使用或存在来检查用户的原因

对不起，我理解错了任务。

SELECT createdby
FROM order_match
GROUP BY createdby
-- check that user has more than one transaction which' status is not non-approved
HAVING SUM(order_status_id != 7) > 1
-- and at least one of them is in specified data range
   AND SUM(order_status_id != 7 AND createdAt BETWEEN '2020-02-01' AND '2020-02-28')

我的错在哪里？

In WHERE IN- 这个条件为每个createdby至少有一个批准的交易的人提供 TRUE，因为这个交易在这个条件下检查自己。

另外 -s1.createdAt BETWEEN '2020-02-01' AND '2020-02-28'重叠s1.createdAt <= '2020-02-28'，所以第二个条件是多余的（如果第一个是真的，那么第二个也是真的）。

推荐阅读