php - 用不同的值加入同一个表两次
问题描述
我正在尝试使用 ID 将“transfers.pickup_areas_group_id”和“transfers.drop_areas_group_id”的值替换为“areas_group”表中的值
我正在使用这个查询:
SELECT
transfers.id AS transfer_id,
transfers.name AS transfer_name,
transfers.pickup_areas_group_id AS transfer_pickup_areas_group_id,
transfers.drop_areas_group_id AS transfer_drop_areas_group_id,
transfers_pricing.vehicle_id AS vehicle_id,
transfers_pricing.date_start AS date_start,
transfers_pricing.date_end AS date_end,
transfers_pricing.price AS price
FROM transfers
INNER JOIN transfers_pricing ON transfers_pricing.transfer_id = transfers.id
我尝试了一个额外的 INNER JOIN 来替换第一个值“transfers.pickup_areas_group_id”,但我找不到替换第二个值“transfers.drop_areas_group_id”的方法
我试过这个查询:
SELECT
transfers.id AS transfer_id,
transfers.name AS transfer_name,
transfers.pickup_areas_group_id AS transfer_pickup_areas_group_id,
areas_group.area_id AS pickup_area_ids,
transfers.drop_areas_group_id AS transfer_drop_areas_group_id,
transfers_pricing.vehicle_id AS vehicle_id,
transfers_pricing.date_start AS date_start,
transfers_pricing.date_end AS date_end,
transfers_pricing.price AS price
FROM transfers
INNER JOIN transfers_pricing ON transfers_pricing.transfer_id = transfers.id
INNER JOIN areas_group ON areas_group.id = transfers.pickup_areas_group_id
谢谢,
解决方案
基本上,你需要另一个加入areas_group
;要消除对同一个表的两个引用的歧义,您需要使用表别名。
实际上,对查询中起作用的所有表使用表别名是一种很好的做法:这使得查询的读写时间更短。
SELECT
t.id AS transfer_id,
t.name AS transfer_name,
t.pickup_areas_group_id AS transfer_pickup_areas_group_id,
ag1.area_id AS pickup_area_ids,
t.drop_areas_group_id AS transfer_drop_areas_group_id,
ag2.area_id AS drop_area_ids
tp.vehicle_id AS vehicle_id,
tp.date_start AS date_start,
tp.date_end AS date_end,
tp.price AS price
FROM transfers t
INNER JOIN transfers_pricing tp ON tp.transfer_id = t.id
INNER JOIN areas_group ag1 ON ag1.id = t.pickup_areas_group_id
INNER JOIN areas_group ag2 ON ag2.id = t.drop_areas_group_id
推荐阅读
- ios - iOS swift - UICollectionView scrollToItem 在项目的 scrollPosition
- c++ - C++ 使用 GLFW,绘制三角形函数表现怪异, cout 被注释掉
- android - 如何解析对对象列表的 WebSocket 响应
- javascript - 通过在输入字段中输入 id 显示所有信息
- python - 如何创建基于模板的表并在多个 DITA xml 中使用它并通过它填充数据
- python - 无法遍历 Pytorch Dataloader 。不等张量
- html - 使用 VBA 从网站源代码更新/提取变化的元素
- botframework - 为bot FRAMEWORK连接ETIMEDOUT 137.116.128.188:443,可以扩展
- json - 在 Hive 中将结构数组转换为 Json
- c++ - 如何解决 SDL 中未找到 IMG_Load() 引用的错误?