时间戳

首页 > 解决方案 > 根据优先级加入和获取列

sql - 根据优先级加入和获取列

问题描述

CREATE TABLE orders
(
    product CHAR(3), 
    yr CHAR(4)
);

INSERT INTO orders VALUES ('a', '2019');
INSERT INTO orders VALUES ('m', '2019');

CREATE TABLE customers
(
    cus_name CHAR(20), 
    columnX CHAR(3), 
    columnY CHAR(3),
    price INT
);

INSERT INTO customers VALUES ('john', 'b', 'a', 100);
INSERT INTO customers VALUES ('brad', 'a', 'd', 200);
INSERT INTO customers VALUES ('chris', 'm', 'y', 200);
INSERT INTO customers VALUES ('Luis', 'r', 'm', 200);

我想根据表中的列将订单表与客户表连接product起来orders。

我想price根据列columnY或columnX每个产品获取一个。

columnY应优先考虑。如果产品存在，columnY则从该行获取价格。

如果它不存在，则检查columnX并获取该价格。

下面的带有 OR 操作的查询给了我两行，但我只想要第一行cus_nameas John

select *
from orders a
left join customers c on a.product = c.columnY 
                      or a.product = c.columnX
产品 年 cus_name X 列 Y 列 价格
一个 2019 约翰 b 一个 100
一个 2019 布拉德 一个 d 200
米 2019 克里斯 米 是的 200
米 2019 路易斯 r 米 200

预期输出为：

产品 年 cus_name X 列 Y 列 价格
一个 2019 约翰 b 一个 100
米 2019 路易斯 r 米 200

提前致谢

columnX为清楚起见进行编辑：每个产品在and中只会出现一次columnY，即ColumnY并且ColumnX不能有多个产品“a”

编辑 2 - 在订单表中包含多个产品。

标签： sqlsql-servertsqljoin

解决方案

使用CASE表达式，您可以按定义的优先级排序，然后使用TOP1进入第一行。

不过，这并不能解决有 2 行与您的优先级匹配的问题。

select top 1 *
from orders a
left join customers c on 
    a.product = c.columnY 
    or a.product = c.columnX
order by case when a.product = c.columnY then 1 else 0 end desc;

随着您需要多种产品的澄清，您需要一个完全不同的解决方案。一个常见的解决方案是使用row_number()窗口函数来查找每个产品的第一行，例如

with cte as (
    select *
        -- Use the same ordering as before, but now partitioned by product.
        , row_number() over (partition by a.product order by case when a.product = c.columnY then 1 else 0 end desc) rn
    from #orders a
    left join #customers c on 
        a.product = c.columnY 
        or a.product = c.columnX
)
select product, yr, cus_name, columnX, columnY, price
from cte
-- Only pick the first result, ordered by our priority, per product
where rn = 1
order by product, yr, cus_name;

返回：

产品 年 cus_name X 列 Y 列 价格
一个 2019 约翰 b 一个 100
米 2019 路易斯 r 米 200

推荐阅读