用条件调用多列的SQL查询(使用NAs)

Question

这是来自我的前一个问题的一个连续的问题。我想编写一个SQL查询，调用几个有条件的列。我正在使用RMySQL包开发R Studio。我的服务器是MySQL。

桌子看起来像这样。

organisation          A           B           C           D
Ikea         2018-04-01  2018-05-07  2018-05-09  2018-05-01
Ikea         2018-06-01  2018-05-03  2018-05-29          NA   
Orange       2018-04-02  2018-05-01  2018-07-08  2018-05-26 
Ikea         2018-06-02  2018-05-01          NA  2018-05-26
Nestle       2018-06-02  2018-05-01          NA  2018-05-26
Ikea                 NA  2018-05-05  2018-04-02  2018-06-01

我想找一排，这个组织是宜家，四栏(A，B，C，D)最早的日期是2018-05-01和2018-05-31。

在包含NA值的行中，我想忽略NAs，看看其余值中最早的日期是什么。例如，对于第二行，最早的日期是“2018-05-03”(B栏)，因此它符合标准。

因此，只有第二行，即上面原始表的第四行，才符合条件。我想得到的结果应该是：

organisation          A           B           C           D
Ikea         2018-06-01  2018-05-03  2018-05-29          NA    
Ikea         2018-06-02  2018-05-01          NA  2018-05-26

如何编写SQL查询？以下是我从前一个问题中得到答案后的尝试，但是对于带有NAs的行，它并不能很好地工作。

SELECT * FROM myTable 
WHERE organisation LIKE Ikea
LEAST(A, B, C, D) >= '2018-05-01' AND
LEAST(A, B, C, D) < '2018-06-01'

感谢您的各种帮助！

Answer 1

只需使用coalesce()

SELECT *
FROM myTable 
WHERE organisation LIKE 'Ikea' AND
      LEAST(COALESCE(A, '2019-01-01'), COALESCE(B, '2019-01-01'), COALESCE(C, '2019-01-01'), COALESCE(D, '2019-01-01')) >= '2018-05-01' AND
      LEAST(COALESCE(A, '2019-01-01'), COALESCE(B, '2019-01-01'), COALESCE(C, '2019-01-01'), COALESCE(D, '2019-01-01')) < '2018-06-01';

'2019-01-01'是范围之后的任意日期。

Answer 2

您可以尝试对值使用IFNULL()，并且使用getdate()，所以如果它是null，那么它现在就只使用，然后就不会被认为是最小的，这基本上就是忽略它们。

喜欢：LEAST(IFNULL(A,getdate()), IFNULL(B,getdate()), IFNULL(C,getdate()), IFNULL(D,getdate()))

希望这能把你推向正确的方向。

除非NA是一个字符串，否则您可以使用像case when A = 'NA' THEN getdate() end这样的case语句

可能最终看起来像这样

SELECT * FROM myTable 
WHERE organisation LIKE Ikea
LEAST(IFNULL(A,getdate()), IFNULL(B,getdate()), IFNULL(C,getdate()), IFNULL(D,getdate())) >= '2018-05-01' AND
LEAST(IFNULL(A,getdate()), IFNULL(B,getdate()), IFNULL(C,getdate()), IFNULL(D,getdate())) < '2018-06-01'

或

SELECT * FROM myTable 
WHERE organisation LIKE Ikea
LEAST(case when A = 'NA' THEN getdate() else A end, case when B = 'NA' THEN getdate() else B end, case when C = 'NA' THEN getdate() else C end, case when D = 'NA' THEN getdate() else D end) >= '2018-05-01' AND
LEAST(case when A = 'NA' THEN getdate() else A end, case when B = 'NA' THEN getdate() else B end, case when C = 'NA' THEN getdate() else C end, case when D = 'NA' THEN getdate() else D end) < '2018-06-01'

Answer 3

考虑加入使用UNION ALL派生的聚合查询

SELECT m.*
FROM myTable m
INNER JOIN 
   (SELECT t.ID, MIN(t.`DateValue`) As MinDate
    FROM 
       (SELECT ID, organisation, 'A' As 'Category', A As `DateValue` FROM myTable
        UNION ALL
        SELECT ID, organisation, 'B' As 'Category', B As `DateValue` FROM myTable
        UNION ALL
        SELECT ID, organisation, 'C' As 'Category', C As `DateValue` FROM myTable
        UNION ALL
        SELECT ID, organisation, 'D' As 'Category', D As `DateValue` FROM myTable) As t
    WHERE t.organisation = 'Ikea'
    GROUP BY t.ID
    HAVING MIN(t.`DateValue`) >= '2018-05-01' AND MIN(t.`DateValue`) < '2018-06-01') As agg
ON m.ID = agg.ID;

RexTester演示 (注:日期采用DD-MM-YYYY格式)

​

​