SQL中如何根据范围对记录进行分组

Question

你好我想创建一个SQL来根据范围对记录进行分组

例如，假设我

Number    Time            Price
100    20100810           10.0
100    20100812           15.0
160    20100810           10.0
200    20100810           12.0
210    20100811           13.0
300    20100811           14.0
350    20100810           16.0

现在需要根据“Number”的范围获取记录：[100,200),[200,300), [300,400) 和 [0,400]。对于每个范围，我需要最新“时间”的“价格”，

因此结果应该是

NumberRange         Time            Price
    1            20100812           15.0
    2            20100811           13.0
    3            20100811           14.0
    4            20100812           15.0

如何构建 SQL 语句来生成此结果？

我没有在特定的数据库上工作。所以我正在寻找没有特定数据库的SQL语句

Answer 1

使用：

 SELECT x.rank, x.time, x.price
   FROM (SELECT *,
                CASE 
                   WHEN number BETWEEN 100 and 199 THEN 1
                   WHEN number BETWEEN 200 and 299 THEN 2
                   WHEN number BETWEEN 300 and 399 THEN 3
                   ELSE NULL
                END AS rank
           FROM TABLE) x
           JOIN (SELECT t.rank,
                        MAX(t.time) AS max_time
                   FROM (SELECT *,
                                CASE 
                                  WHEN number BETWEEN 100 and 199 THEN 1
                                  WHEN number BETWEEN 200 and 299 THEN 2
                                  WHEN number BETWEEN 300 and 399 THEN 3
                                  ELSE NULL
                                END AS rank
                           FROM TABLE) t
               GROUP BY t.rank) y ON y.rank = x.rank 
                                 AND y.max_time = x.time
UNION ALL
SELECT x.rank, x.time, x.price
  FROM (SELECT *,
               CASE 
                 WHEN number BETWEEN 0 and 400 THEN 4
                 ELSE NULL
               END AS rank
          FROM TABLE) x
          JOIN (SELECT t.rank,
                       MAX(t.time) AS max_time
                  FROM (SELECT *,
                               CASE  
                                 WHEN number BETWEEN 0 and 400 THEN 4
                                 ELSE NULL
                               END AS rank
                          FROM TABLE) t
              GROUP BY t.rank) y ON y.rank = x.rank 
                                AND y.max_time = x.time

Answer 2

家庭作业？

您可以使用 if 语句选择附加列来确定相关的范围 ID。然后按这个新列进行分组。

Answer 3

尽管以下一些要求不属于核心标准 SQL 的范围，但它们是完整的标准 SQL 功能（例如，行构造函数是完整的 SQL-92 功能，CTE 是完整的 SQL-99 功能等），并且确实会在某些SQL Server 和 Oracle 等产品：

WITH MyTable (my_Number, my_Time, Price)
     AS
     (
      SELECT my_Number, CAST(my_Time AS DATE), 
             CAST(Price AS DECIMAL(5, 2))
        FROM (
              VALUES (100, '2010-08-10', 10), 
                     (100, '2010-08-12', 15), 
                     (160, '2010-08-10', 10), 
                     (200, '2010-08-10', 12), 
                     (210, '2010-08-11', 13), 
                     (300, '2010-08-11', 14), 
                     (350, '2010-08-10', 16)
             ) AS MyTable (my_Number, my_Time, Price)
     ), Ranges (NumberRange, range_start, range_end)
     AS 
     (
      SELECT NumberRange, range_start, range_end
        FROM (
              VALUES (1, 100, 200),
                     (2, 200, 300),
                     (3, 300, 400), 
                     (4, 0, 400)
             ) AS Ranges (NumberRange, range_start, range_end)
     ), 
     RangesMaxTimes (NumberRange, range_start, range_end, max_time)
     AS 
     ( 
      SELECT R1.NumberRange, 
             R1.range_start, R1.range_end, 
             MAX(M1.my_Time) AS max_time
        FROM MyTable AS M1
       INNER JOIN Ranges AS R1
          ON R1.range_start <= M1.my_Number
             AND M1.my_Number < R1.range_end
       GROUP 
          BY R1.NumberRange, R1.range_start, R1.range_end
     )
SELECT R1.NumberRange, M1.my_Time, M1.Price
  FROM MyTable AS M1
       INNER JOIN RangesMaxTimes AS R1
          ON R1.range_start <= M1.my_Number
             AND M1.my_Number < R1.range_end
             AND M1.my_Time = R1.max_time;