从多个表中排除记录的最佳方法

发布于 2024-12-11 22:08:23 字数 619 浏览 0 评论 0原文

我得到了以下表格（仅作为示例）：车辆、车辆描述、车辆零件

车辆与车辆描述和车辆零件有 1 到多个。给定车辆可能没有相应的车辆描述/零件。

SELECT * FROM vehicles
   LEFT OUTER JOIN vehicles d ON vehicles.vin = d.vin AND d.summary NOT LIKE 'honda'
   LEFT OUTER JOIN        
   (SELECT SUM(desc_total) FROM vehicle_descriptions WHERE NOT LIKE desc 'honda' GROUP BY vin) b 
   ON vehicles.vin = vehicle_b.vin
   LEFT OUTER JOIN 
   (SELECT SUM(part_count) FROM vehicle_parts WHERE part_for NOT LIKE 'honda' GROUP BY vin) c ON vehicles.vin = c.vin

如果vehicle_desc、vehicles 或part 包含排除项，则整个记录不应显示在结果集中。即使其中一个表包含排除项 Honda，上面的查询也将返回一条记录。我将如何解决上述查询？

原文

I got the following tables (just an example): vehicles, vehicle_descriptions, vehicle_parts

vehicles have 1 to many with vehicle_descriptions and vehicle_parts. There may not be a corresponding vehicle_description/part for a given vehicle.

SELECT * FROM vehicles
   LEFT OUTER JOIN vehicles d ON vehicles.vin = d.vin AND d.summary NOT LIKE 'honda'
   LEFT OUTER JOIN        
   (SELECT SUM(desc_total) FROM vehicle_descriptions WHERE NOT LIKE desc 'honda' GROUP BY vin) b 
   ON vehicles.vin = vehicle_b.vin
   LEFT OUTER JOIN 
   (SELECT SUM(part_count) FROM vehicle_parts WHERE part_for NOT LIKE 'honda' GROUP BY vin) c ON vehicles.vin = c.vin

If either vehicle_desc, vehicles, or part contains the exclusion term, the whole record should not show up in the result set. The query above will return a record even if one of the tables contain the exclusion term Honda. How would I fix the above query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

人间不值得 2024-12-18 22:08:23

您没有使用 sum() 中的任何信息作为显示内容的一部分，只是为了决定是否包含车辆。并且您在第一个子句中进行了不必要的自连接。一般来说，在这种情况下，“存在”和“不存在”子句效果很好。那么这个呢？我将使用 Oracle 语法，当然您可以转换为 ANSI。

    SELECT * FROM vehicles v where summary <> 'honda' 
and not exists (select 1 from vehicle_descriptions d where d.vin = v.vin and d.desc <> 'honda') 
and not exists (select 1 from vehicle_parts p where p.vin = v.vin and p.part_for <> 'honda')

You're not using any of the information in either sum() as part of what you show, just to decide whether to include the vehicle. And you're doing an unnecessary self join in your first clause. Generally in situations like this, the "exists" and "not exists" clauses work well. So what about this? I'll use Oracle syntax, you can convert to ANSI of course.

    SELECT * FROM vehicles v where summary <> 'honda' 
and not exists (select 1 from vehicle_descriptions d where d.vin = v.vin and d.desc <> 'honda') 
and not exists (select 1 from vehicle_parts p where p.vin = v.vin and p.part_for <> 'honda')

回复收藏 0 原文

~没有更多了~