SQL 函数式 WHERE 语句

Question

我已经研究了很长时间并且已经广泛地研究了这个问题；到目前为止，还没有出现类似的情况。 tl;博士低于

下面是我的问题。

我正在尝试创建一个 SELECT SQLite 中带有条件过滤的语句，其工作方式有点像函数。下面的示例伪代码:

SELECT col_date, col_hour FROM table1 JOIN table2 
ON table1.col_date = table2_col_date AND table1.col_hour = table2.col_hour AND table1.col_name = table2.col_name
    WHERE
        IF table2.col_name = "a" THEN {filter these records further such that its table2.col_volume >= 600} AND
        IF table2.col_name = "b" THEN {filter these records further such that its table2.col_volume >= 550}
        BUT {if any of these two statements are not met completely, do not get any of the col_date, col_hour}

*我知道 SQLite 不支持 IF声明，但这只是为了表明我的意图。

这是我到目前为止一直在做的事情。根据 this文章，可以转换 CASE子句转换为 bool 逻辑，这样您将在此处看到:

SELECT table1.col_date, table1.col_hour FROM table1 INNER JOIN table2 
ON table1.col_date = table2.col_date AND table1.col_hour = table2.col_hour AND table1.col_name = table2.col_name
    WHERE
        ((NOT table2.col_name = "a") OR table2.col_volume >= 600) AND
        ((NOT table2.col_name = "b") OR table2.col_volume >= 550)

在这种语法中，问题是我仍然得到 col_date s 和 col_hour至少有一个 col_name的 col_volume对于那个特定的 col_date和 col_hour没有达到它的要求。 (例如，我仍然得到带有 col_date = 2010-12-31 和 col_hour = 5 的记录条目，但是 col_name = "a" 的 col_volume = 200 而 col_name = "b" 的 col_volume = 900 。这表示日期和时间不应出现在查询中，因为“a "的音量不 >= 600，即使 "b"满足 >= 550 的音量要求。)

对于 tl;博士

如果所有这些都变得令人困惑，这里是带有示例正确查询结果的示例表，因此您可以忘记上面的所有内容并继续前进:

表格1

col_date,col_hour,col_name,extra1,extra2
2010-12-31,4,"a","hi",1
2010-12-31,4,"a","he",1
2010-12-31,4,"a","ho",1
2010-12-31,5,"a","hi",1
2010-12-31,5,"a","he",1
2010-12-31,5,"a","ho",1
2010-12-31,6,"a","hi",1
2010-12-31,6,"a","he",1
2010-12-31,6,"a","ho",1
2010-12-31,4,"b","hi",1
2010-12-31,4,"b","he",1
2010-12-31,4,"b","ho",1
2010-12-31,5,"b","hi",1
2010-12-31,5,"b","he",1
2010-12-31,5,"b","ho",1
2010-12-31,6,"b","hi",1
2010-12-31,6,"b","he",1
2010-12-31,6,"b","ho",1

表2

col_date,col_hour,col_name,col_volume
2010-12-31,4,"a",750
2010-12-31,4,"b",750
2010-12-31,5,"a",200
2010-12-31,5,"b",900
2010-12-31,6,"a",700
2010-12-31,6,"b",800

正确的查询结果( col_volume 过滤器:'a' 为 600，'b' 为 550)应该是:

2010-12-31,4
2010-12-31,6

Answer 1

试试这个:

      SELECT table1.col_date, 
             table1.col_hour 
        FROM table1 
  INNER JOIN table2
          ON table1.col_date = table2.col_date 
         AND table1.col_hour = table2.col_hour 
         AND table1.col_name = table2.col_name
       WHERE EXISTS ( -- here I'm appling the filter logic 
                      select col_date,
                             col_hour
                        from table2 sub
                       where (col_name = 'a' and col_volume >= 600) 
                          or (col_name = 'b' and col_volume >= 550) 
                         and sub.col_date = table2.col_date 
                         and sub.col_hour = table2.col_hour 
                         and sub.col_name = table2.col_name
                    group by col_date,
                             col_hour
                      having count(1) = 2 -- I assume there could be only two rows:
                                          -- one for 'a' and one for 'b'
                    ) 

您可以在 SQLfiddle 中查看此演示

最后一件事，您显示来自 Table1 的相同列您用于连接，但我想这只是为了这个例子