当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - SQL between query with small range error

问题描述:

I have two tables, Table A and Table B

Table A contains

+----+------+------+-------+-------+

| HW | Year | Sec | From | To |

+----+------+------+-------+-------+

| 1 | 2012 | 40 | 15.04 | 19.07 |

| 1 | 2012 | 40 | 19.07 | 19.14 |

| 2 | 2012 | 40 | 0 | 1.81 |

+----+------+------+-------+-------+

Table B contains

+------+------+-------+-------+------+

| Year | Sec | From | To | Rate |

+------+------+-------+-------+------+

| 2012 | 40 | 0 | 9.93 | 70 |

| 2012 | 40 | 14.4 | 14.47 | 60 |

| 2012 | 40 | 14.47 | 19.14 | 55 |

+------+------+-------+-------+------+

My job is to match year and sec in both tables and update Table A with a new column rate from Table B

Here is the query:

SELECT DISTINCT A.[Year]

, A.[Sec]

, A.[From]

, B.[From]

, A.[To]

, B.[To]

, B.[Rate]

FROM TABLE A

JOIN TABLE B ON A.Sec = B.SEC

AND A.Year = B.YEAR

WHERE ((A.FROM >= [B.From] AND A.To <= [B.To]))

AND A.Year = '2012'

AND A.control_section = '40'

I get following result:

+------+------+--------+--------+-------+-------+------+

| Year | Sec | A.From | B.From | A.To | B.To | Rate |

+------+------+--------+--------+-------+-------+------+

| 2012 | 40 | 15.04 | 0 | 19.07 | 9.93 | 70 |

| 2012 | 40 | 15.04 | 14.47 | 19.07 | 19.14 | 55 |

| 2012 | 40 | 19.07 | 0 | 19.14 | 9.93 | 70 |

| 2012 | 40 | 19.07 | 14.47 | 19.14 | 19.14 | 55 |

| 2012 | 40 | 0 | 0 | 1.81 | 9.93 | 70 |

+------+------+--------+--------+-------+-------+------+

Desired output should be as follows:

+------+------+--------+--------+-------+-------+------+

| Year | Sec | A.From | B.From | A.To | B.To | Rate |

+------+------+--------+--------+-------+-------+------+

| 2012 | 40 | 15.04 | 14.47 | 19.07 | 19.14 | 55 |

| 2012 | 40 | 19.07 | 14.47 | 19.14 | 19.14 | 55 |

| 2012 | 40 | 0 | 0 | 1.81 | 9.93 | 70 |

+------+------+--------+--------+-------+-------+------+

Why am I getting the other two additional rows?

网友答案:

The result you get are wrong because you are probably not using a numeric data type for the columns From and To. See :

 SELECT '19.07' <= '9.93' ## Returns 1 (true);
 SELECT 19.07 <= 9.93     ## Returns 0 (false);

Thus all the From and To comparisons in your query are probably wrong and that's why you are getting an unexpected result.

By using the appropriate data type, DECIMAL(6,2) for instance, your query will return the expected result. See this Fiddle.

分享给朋友:
您可能感兴趣的文章:
随机阅读: