SQL中的WHERE条件问题 [英] WHERE condition issue in SQL

问题描述

我在使用SQL查询时遇到问题:

I am facing problem with an SQL query:

这是我的表格数据:

+------------+--------+--------+------------+------------+
| priceId_PK | mPrice | aPrice | validFrom  | validTo    |
+------------+--------+--------+------------+------------+
|          1 |   0.00 |   0.00 | 2013-07-01 | 2013-08-31 |
|          2 |   7.50 |   2.50 | 2013-09-01 | 2013-12-31 |
|          3 |  15.00 |   5.00 | 2014-01-01 | 2035-12-31 |
+------------+--------+--------+------------+------------+

我的SQL查询是

SELECT mPrice, aPrice, 

CASE 
    WHEN validFrom < '2013-11-01' 
        THEN '2013-11-01' 
    ELSE validFrom END AS validFrom, 

CASE 
    WHEN validTo > '2013-11-30' 
        THEN '2013-11-30' 
    ELSE validTo END AS validTo 

FROM commission 

WHERE (validfrom BETWEEN '2013-11-01' AND '2013-11-30' OR validto BETWEEN '2013-11-01' AND '2013-11-30') ORDER BY validFrom

我的预期结果:

+--------+--------+------------+------------+
| mPrice | aPrice | validFrom  | validTo    |
+--------+--------+------------+------------+
|   7.50 |   2.50 | 2013-11-01 | 2013-11-30 |
+--------+--------+------------+------------+

但是查询实际上返回一个空集.

But the query actually returns an empty set.

在没有WHERE条件的情况下，查询为

Without the WHERE condition, the query is

SELECT mPrice, aPrice, 

CASE 
    WHEN validFrom < '2013-11-01' 
        THEN '2013-11-01' 
    ELSE validFrom END AS validFrom, 

CASE 
    WHEN validTo > '2013-11-30' 
        THEN '2013-11-30' 
    ELSE validTo END AS validTo 

FROM commission 

这将返回:

+--------+--------+------------+------------+
| mPrice | aPrice | validFrom  | validTo    |
+--------+--------+------------+------------+
|   0.00 |   0.00 | 2013-11-01 | 2013-08-31 |
|   7.50 |   2.50 | 2013-11-01 | 2013-11-30 |
|  15.00 |   5.00 | 2014-01-01 | 2013-11-30 |
+--------+--------+------------+------------+

我的问题是，当条件为WHERE的查询返回单个结果集时，为什么返回空数据?

My question is, Why does the query with the WHERE condition return empty data when I expect it to return a single result set?

SELECT和WHERE条件的执行顺序是否有问题?

Is it a problem with the execution order of SELECT and WHERE conditions?

还是WHERE条件无法正常运行的另一个问题?

Or is it another issue with WHERE condition that is not working properly?

推荐答案

我认为您正在尝试在where条件下通过案例查询访问validFrom和validTo.如果是这种情况，则需要重新格式化查询.

I think you are trying to access the validFrom and validTo from the case query in the where condition. If that is the case, you need to reformat your query.

SELECT t1.* FROM ( SELECT mPrice, aPrice, 
CASE 
    WHEN validFrom < '2013-11-01' 
        THEN '2013-11-01' 
    ELSE validFrom END AS validFrom, 

CASE 
    WHEN validTo > '2013-11-30' 
        THEN '2013-11-30' 
    ELSE validTo END AS validTo 

FROM commission) t1 

WHERE ((t1.validfrom BETWEEN '2013-11-01' AND '2013-11-30') OR (t1.validto BETWEEN '2013-11-01' AND '2013-11-30')) ORDER BY t1.validFrom

但这将返回3个结果.如果需要获得预期的结果，则需要使用AND条件而不是OR.

But this will return 3 results. If you need to get the expected result, then you need to use an AND condition instead of OR.

那么您的查询将是

SELECT t1.* FROM ( SELECT mPrice, aPrice, 
CASE 
    WHEN validFrom < '2013-11-01' 
        THEN '2013-11-01' 
    ELSE validFrom END AS validFrom, 

CASE 
    WHEN validTo > '2013-11-30' 
        THEN '2013-11-30' 
    ELSE validTo END AS validTo 

FROM commission) t1 

WHERE ((t1.validfrom BETWEEN '2013-11-01' AND '2013-11-30') AND (t1.validto BETWEEN '2013-11-01' AND '2013-11-30')) ORDER BY t1.validFrom

这篇关于SQL中的WHERE条件问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！