sql - 如何在 SQL Server 中使用 Joins 编写这个具有排名功能的特定 Co-related 子查询？

Question

假设您有下表，其中包含城市、酒店、价格。您需要编写代码才能找到每个城市中最便宜的酒店，并且与同一城市中价格最高的酒店相比要便宜 %。仅使用联接。仅加入！

这是示例：请仅在 TEST_DB 中运行。

create table citycheap 
(
     city varchar(100), 
     Hotel varchar(100), 
     prici money
)

insert into citycheap 
values ('Poway', 'Ramada Inn', 100),  ('Poway', 'Elks Oaks', 70),
       ('Poway', 'Days Inn', 85),
       ('Long Beach', 'Days Inn', 95), ('Long Beach', 'Motel 8', 65),
       ('Long Beach', 'Hampton Inn', 105),
       ('San Diego', 'Motel 6', 55), ('San Diego', 'Beach Inn', 115),
       ('San Diego', 'Days Inn', 85)

select * 
from citycheap

标签： sqlsql-servertsqljoin

Answer 1

您根本不需要连接：

WITH DataSource AS 
(
    select city
          ,Hotel
          ,prici
          ,ROW_NUMBER() OVER (PARTITION BY city ORDER BY prici ASC) AS rowID
          ,MAX(prici) OVER (PARTITION BY city) AS total_price
    from citycheap
)
SELECT city
      ,hotel
      ,prici as LowPrice
      ,CAST((total_price - prici) * 100.0 / total_price AS DECIMAL(9,2)) as [% Cheapter]
FROM DataSource
WHERE rowID = 1;

---

WITH DataSource AS
(
    SELECT city
          ,MIN(prici) as min_price
          ,MAX(prici) as max_price
          ,CAST((MAX(prici) - MIN(prici)) * 100.0 / MAX(prici) AS DECIMAL(9,2)) as [% Cheapter]
    FROM citycheap
    GROUP BY  city
)
SELECT CH.city
      ,CH.Hotel
      ,CH.prici  as LowPrice
      ,DS.[% Cheapter]
FROM citycheap CH
INNER JOIN DataSource DS
    ON CH.prici = DS.[min_price]