代码之家 › 专栏 › 技术社区 › P. Boro

自定义表联接

join postgresql sql

P. Boro · 技术社区 · 7 年前

假设PostgreSQL数据库中有4个表:

users {
 id: int
}

cars {
 id: int
}

usage_items {
 id: int,
 user_id: int,
 car_id: int,
 start: date,
 end: date
}

prices {
 id: int,
 car_id: int,
 price: int
}

当用户租车时,我会创建一个使用项目记录来跟踪租车时间。月底,我给他寄去了一张发票,上面列有计算好的费用。这里的SQL非常简单:

SELECT usage_items.start, usage_items.end, prices.price
FROM usage_items
JOIN prices ON prices.car_id = usage_items.car_id

(这里我省略了带日期比较的WHERE子句,以及我在Ruby代码中所做的其余计算)

我现在面临的问题是,我的一些用户与我签订了定制合同,以确保他们的价格更低。我正在寻找一种方法来在我的数据库中表达这个逻辑。我想出了一个主意,将用户ID列添加到价格表中,但是这样我就需要为每个用户创建价格。所以我决定实现以下逻辑:如果prices行中的car_id为空,这意味着它是所有用户的默认价格。否则,它是特定于用户的。但是我不知道如何为这个案例编写SQL,因为:

SELECT usage_items.start, usage_items.end, prices.price
FROM usage_items
JOIN prices ON prices.car_id = usage_items.car_id
WHERE prices.user_id IS NULL OR prices.user_id = usage_items.user_id

返回两个价格的行。我只需要一个有关联组的,或者如果它不存在,也只需要一个组ID为空的。

你能帮我修一下这个SQL吗?或者我的设计不好,我应该改变它?

1 回复 | 直到 7 年前

404 Aniket Jha 7 年前

考虑到您现有的模式,这是实现您想要的目标的一种方法:

设置:

CREATE TABLE usage_items (user_id INTEGER, car_id INTEGER);
CREATE TABLE prices (user_id INTEGER, car_id INTEGER, price INTEGER);

INSERT INTO usage_items VALUES (1, 10), (2, 11), (3, 12);
INSERT INTO prices VALUES
    (1, 10, 101),
    (2, 11, 102),
    (4, 12, 104),
    (NULL, 10, 201),
    (NULL, 11, 202),
    (NULL, 12, 304);

查询(我不使用start/end,但它是相同的):

SELECT DISTINCT ON (u.user_id, u.car_id) u.user_id, u.car_id, p.price
FROM usage_items u
LEFT JOIN prices p
    ON u.car_id = p.car_id
    AND (u.user_id = p.user_id OR p.user_id IS NULL)
ORDER BY u.user_id, u.car_id, CASE WHEN p.user_id IS NOT NULL THEN 1 ELSE 2 END

结果:

| user_id | car_id | price |
| ------- | ------ | ----- |
| 1       | 10     | 101   |
| 2       | 11     | 102   |
| 3       | 12     | 304   |

如你所见,记录 usage_items 他们的汽车和用户的相应记录 prices 获取其自定义价格而不是空版本;没有自定义价格的用户3获取空版本(而不是其他客户的自定义价格)。

这里测试 https://www.db-fiddle.com/f/wPVWEY3r22n22iKpDMrcMC/0

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Kallydi · jdbc无法连接到docker中的postgresql数据库

1 年前

dietzi96 · Pandas DataFrame.to_sql随机和静默地失败,没有错误消息

1 年前

Peter Schofield · 类型转换Postgresql

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Kevin Smeeks · Pyspark JDBC分区读取

1 年前

Micah Armantrout · 如何使用sql脚本将数据插入到代码优先的数据库中?

1 年前

Vanortton · 使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

MWood · Postgres jsonpath用于引用单例元素或元素数组

1 年前