时间戳

首页 > 解决方案 > 消除多个条件并减少到一个（没有 OR 语句）

sql - 消除多个条件并减少到一个（没有 OR 语句）

问题描述

SELECT  "listings".*, trucks.*, cars.*, trucks.id as truck_id, cars.id as cars_id, listings.id as listing_id
FROM "listings" 
LEFT OUTER JOIN "trucks" ON "trucks"."id" = "listings"."listable_id" AND "listings"."listable_type" = 'Truck' 
LEFT OUTER JOIN "cars" ON "cars"."id" = "listings"."listable_id" AND "listings"."listable_type" = 'Car' 
WHERE (cars.user_id = 119 or trucks.user_id = 119) LIMIT $1  [["LIMIT", 11]]

有没有办法修改上面的sql做同样的事情，但只是调用user_id = 119而不是使用or子句？

我希望能够做类似的事情......

    SELECT  "listings".*, trucks.*, cars.*, trucks.id as truck_id, cars.id as cars_id, listings.id as listing_id
FROM "listings" 
LEFT OUTER JOIN "trucks" ON "trucks"."id" = "listings"."listable_id" AND "listings"."listable_type" = 'Truck' 
LEFT OUTER JOIN "cars" ON "cars"."id" = "listings"."listable_id" AND "listings"."listable_type" = 'Car' 
WHERE (user_id_alias = 119) LIMIT $1  [["LIMIT", 11]]

或类似的东西

WHERE (alias.user_id = 119) LIMIT $1  [["LIMIT", 11]]

简化代码的目的是我编写了一个只关心 user_id 的搜索查询对象，并且由于卡车和汽车是互斥的，因为你永远不可能同时拥有卡车和汽车的列表，我需要获取所有列表特定的用户。

标签： sqlpostgresql

解决方案

您可以加入"listings"和并集"cars"和"trucks"表。

SELECT  "listings".* 
FROM "listings" 
JOIN (
   SELECT 'Car' as "vtype", "id", "user_id"
   FROM "cars"
   UNION ALL
   SELECT 'Truck' as "vtype", "id", "user_id"
   FROM "trucks"
) vehs ON vehs."id" = "listings"."listable_id"  AND "listings"."listable_type" = vehs."vtype" AND vehs."user_id" = 119

推荐阅读