sql查询groupby，with coalesce，least，min

nwsw7zdq 于 2021-06-20 发布在 Mysql

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

我想编写一个sql查询，调用几个条件有点复杂的列。我正在使用rmysql包开发r studio。我的服务器是mysql。
这张table看起来像这样。

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

这就是我想做的：
过滤其中的行 organisation = Ikea 分组方式 Tour_ID 这样地：

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

每组 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 ，有 NA 在列中 D 所以呢 COALESCE(MIN(D), '2119-01-01') 退货 2119-01-01 . 但不应该，因为列中最小的值 D 是 2018-05-01 这必须与列中的最小值进行比较 A , B 以及 C . 的确， 2018-05-01 在列中 D 是整个组中最小的值所以我认为 COALESCE(MIN(D), '2119-01-01') 这是错误的。
但我不知道怎么解决这个问题。任何帮助都将不胜感激！

sql mysql coalesce group-by Min

来源：https://stackoverflow.com/questions/51358248/sql-query-for-group-by-with-coalesce-least-min

2条答案

按热度按时间

gxwragnw1#

p、 salmon的解决方案假设，当整个组织中的最低日期+旅游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';

赞(0）回复(0）举报 2021-06-20

mlnl4t2r2#

我将使用and if而不是稍微合并和重新排列代码

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)

赞(0）回复(0）举报 2021-06-20

我来回答

sql查询groupby，with coalesce，least，min

2条答案

相关问题

热门标签

最新问答