SQL查询GROUP，with，最少，MIN

Question

我想编写一个SQL查询，调用几个条件有点复杂的列。我正在使用RMySQL包开发R Studio。我的服务器是MySQL。

桌子看起来像这样。

organisation    Tour_ID             A           B           C           D  
Ikea                  a    2018-04-01  2018-05-07  2018-05-09  2018-05-01
Ikea                  a    2018-06-01  2018-05-03  2018-05-29          NA   
Ikea                  a    2018-04-02  2018-05-01  2018-07-08  2018-05-26 
Ikea                  b    2018-06-02  2018-05-01          NA  2018-05-26
Ikea                  b    2018-06-02  2018-05-01          NA  2018-05-26
Ikea                  b            NA  2018-05-05  2018-08-02  2018-06-01
Ikea                  c    2018-06-01  2018-05-07  2018-05-09  2018-05-01
Ikea                  c    2018-06-01  2018-05-03          NA          NA   
Ikea                  c    2018-08-02  2018-05-09  2018-07-08  2018-05-26

这就是我想要做的：

1. 过滤organisation = Ikea所在的行
2. 群由Tour_ID这样的： Tour_ID A B C D宜家2018-04-01 2018-05-07 2018-05-09 2018-05-01宜家2018-06-01 2018-05-03 2018-05-29 NA Ikea 2018-04-02 2018-05-01 2018-07 2018-07-08宜家b 2018-06-02 2018-05-01 NA 2018-05-26宜家b 2018-06-02 2018-05-01 NA 2018-05-26宜家b 2018-05-05 2018-08-02宜家2018-06-01宜家2018-06-01 2018-05 2018-05- 0507 2018-05-09 2018-05-01宜家c 2018-06-01 2018-05-03 NA Ikea c 2018-08-02 2018-05-09 2018-07-08 2018-05-26
3. 在每组Tour_ID中，查看A、B、C和D列中最早的日期。如果组中四列中最早的日期在2018-05-01和2018-05-31之间，则返回整个组。如果一行包含NA值，我想忽略NA，看看其他值中最早的日期是什么。例如，对于Tour_ID = a组，最早的日期是2018-04-01，因此它不符合标准。

总之，只有Tour_ID = b和Tour_ID = c符合条件的组。其结果应该是：

organisation    Tour_ID             A           B           C           D
Ikea                  b    2018-06-02  2018-05-01          NA  2018-05-26
Ikea                  b    2018-06-02  2018-05-01          NA  2018-05-26
Ikea                  b            NA  2018-05-05  2018-08-02  2018-06-01
Ikea                  c    2018-06-01  2018-05-07  2018-05-09  2018-05-01
Ikea                  c    2018-06-01  2018-05-03          NA          NA   
Ikea                  c    2018-08-02  2018-05-09  2018-07-08  2018-05-26

如何编写SQL查询？以下是我的尝试：

"SELECT t.* FROM myTable JOIN (SELECT organisation, Tour_ID 
                                                   FROM myTable
                                                   WHERE organisation LIKE 'Ikea' AND
                                                   GROUP BY organisation, Tour_ID
                                                   HAVING LEAST(COALESCE(MIN(A), '2119-01-01'), 
                                                                COALESCE(MIN(B), '2119-01-01'), 
                                                                COALESCE(MIN(C), '2119-01-01'), 
                                                                COALESCE(MIN(D), '2119-01-01')) >= '2018-05-01' AND
                                                          LEAST(COALESCE(MIN(A), '2119-01-01'), 
                                                                COALESCE(MIN(B), '2119-01-01'), 
                                                                COALESCE(MIN(C), '2119-01-01'), 
                                                                COALESCE(MIN(D), '2119-01-01')) < '2018-06-01'
                                                  ) tt
                                                  ON tt.Tour_ID = t.Tour_ID AND
                                                     tt.organisation = t.organisation"

但我不认为它正确地选择了Tour_ID = c，因为COALESCE的使用。

对于Tour_ID = c组，D列中有NA，因此COALESCE(MIN(D), '2119-01-01')返回2119-01-01。但是不应该这样，因为列D中最小的值是2018-05-01，这必须与A、B和C列中的最小值进行比较。实际上，列D中的D是整个组中最小的值，所以我认为COALESCE(MIN(D), '2119-01-01')是错误的。

但我不知道有什么解决办法。任何帮助都将不胜感激！

Answer 1

P.Salmon的解决方案假设，当整个组织中的最低日期+ Tour_ID在给定的时间范围内时，则需要显示该日期。当您希望在某一行的A、B、C或D值在时间范围内最低时显示组时，您需要这样做：

SELECT myTable.*
FROM myTable 
JOIN (
   SELECT organisation, tour_id
   FROM myTable
   WHERE  
         LEAST(COALESCE(A, '2119-01-01'), COALESCE(B, '2119-01-01'), 
               COALESCE(C, '2119-01-01'), COALESCE(D, '2119-01-01'))
         BETWEEN '2018-05-01' AND '2018-05-31'
   GROUP BY organisation,tour_id
) s ON s.organisation = myTable.organisation AND s.tour_id = myTable.tour_id
WHERE myTable.organisation = 'ikea';

Answer 2

我将使用和如果，而不是合并和重新排列代码稍微

select t.*
from t 
join
(
select organisation,tour_id,
        min(
        least(if(a='na',date(now()),a),if(b='na',date(now()),b),if(c='na',date(now()),c),if(d='na',date(now()),d))
        ) mindt
from t
group by organisation,tour_id
) s on s.organisation = t.organisation and s.tour_id = t.tour_id
where t.organisation = 'ikea' and s.mindt between '2018-05-01' and '2018-05-31';

+----+--------------+---------+------------+------------+------------+------------+
| id | organisation | Tour_ID | A          | B          | C          | D          |
+----+--------------+---------+------------+------------+------------+------------+
|  4 | Ikea         | b       | 2018-06-02 | 2018-05-01 | NA         | 2018-05-26 |
|  5 | Ikea         | b       | 2018-06-02 | 2018-05-01 | NA         | 2018-05-26 |
|  6 | Ikea         | b       | NA         | 2018-05-05 | 2018-08-02 | 2018-06-01 |
|  7 | Ikea         | c       | 2018-06-01 | 2018-05-07 | 2018-05-09 | 2018-05-01 |
|  8 | Ikea         | c       | 2018-06-01 | 2018-05-03 | NA         | NA         |
|  9 | Ikea         | c       | 2018-08-02 | 2018-05-09 | 2018-07-08 | 2018-05-26 |
+----+--------------+---------+------------+------------+------------+------------+
6 rows in set (0.00 sec)