使用SQL查找税阶，不包含最小值和最大值字段

Question

我正试图在SQL中选择一个税阶。

我可以简单地制作一个minbracket和maxbracket假ld，但这将是重复的数据。

目前，我有：

SELECT s1.statetaxid, s1.statecode, s1.type, s1.taxrate, s1.bracket, s1.exemptperfiler, s1.exemperdependent, s1.deductfederaltaxonstate
FROM
    statetax s1
    INNER JOIN  statetax s2
        ON s1.statetaxid = s2.statetaxid - 1
WHERE                     
    s1.statecode  = 'NY' AND
    s1.type = 'Single' AND
    s1.bracket <= 300000 AND
    s2.bracket > 300000

这对30万人来说很好，但当我达到纽约最高税率时就不行了，因为我正在寻找一个被列为"低于"的2州，但不是100万。

statetaxid  statecode   type  taxrate bracket exemptperfiler  exemperdependent  deductfederaltaxonstate
107         NY        Single   0.0400       0           7700                 0                     1000
108         NY        Single   0.0450    8200           7700                 0                     1000
109         NY        Single   0.0525   11300           7700                 0                     1000
110         NY        Single   0.0590   13350           7700                 0                     1000
111         NY        Single   0.0645   20550           7700                 0                     1000
112         NY        Single   0.0665   77150           7700                 0                     1000
113         NY        Single   0.0685  205850           7700                 0                     1000
114         NY        Single   0.0882 1029250           7700                 0                     1000

如何将工资高于最高门槛的情况包括在内？

Answer 1

假设你从0开始，你想找到MAX括号小于你工资的行：

SELECT s1.statetaxid, s1.statecode, s1.type, s1.taxrate, s1.bracket, s1.exemptperfiler, s1.exemperdependent, s1.deductfederaltaxonstate
FROM
    statetax s1
WHERE                     
    s1.statecode  = 'NY' AND
    s1.type = 'Single'   AND
    s1.bracket = (
      select max(s2.bracket)
      FROM
        statetax s2
      WHERE                     
        s1.statecode  = s2.statcode AND
        s1.type = s2.type           AND
        s2.bracket < 300000 -- salary?
      )