问题描述:

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.

相关阅读:
Top