多表的SQL查询 [英] SQL QUERY ON MULTIPLES TABLES

问题描述

我是Pure Sql的新手，我想将其编写为Query

select items.* 
from items 
LEFT OUTER JOIN 
    (select sum(purchase_details.quantity) as total 
    from purchase_details 
    where (purchase_details.item_id=items.id)  
GROUP BY purchase_details.item_id) ABC 

但这会导致错误

 You have an error in your SQL syntax; check the manual that corresponds to 
    your MariaDB server version for the right syntax to use near 'LIMIT 0, 25' 
    at line 1

我不知道为什么它不起作用

解决方案

此处的语法错误是您需要为left join使用on子句.但是潜在的概念性问题有所不同:您不能join使用依赖于的子查询.

您可以这样修改查询:

select items.* 
from items 
LEFT OUTER JOIN (
  select item_id, sum(purchase_details.quantity) as total
  from purchase_details 
  GROUP BY purchase_details.item_id
) ABC on ABC.item_id = items.id;

这将您的内部where条件(这取决于items.id，这是不允许的，因为它不在范围内)移到了on子句.因此，item_id也被添加到内部的select中(因为在外部需要).

另一种写法是

select items.*, 
   (select sum(purchase_details.quantity) 
    from purchase_details 
    where purchase_details.item_id=items.id) as total
from items;

在这里，您有一个依赖子查询:内部where子句取决于外部items.id.您不再需要group by了，因为where条件已经只使用了该项目的行. (而且，在这种情况下，您最多也只能返回一行.)

这两个查询是等效的，并且可以(如果优化程序找到了执行计划)在内部实际上以完全相同的方式执行(只要您提供适当的索引，就不必在乎什么了). /p>

因此，在您的情况下，您可以同时使用两者(并可能检查哪一个更快)；如果您想获取该商品的其他信息，则应首选left join版本，例如使用

...
LEFT OUTER JOIN (
  select item_id, 
    sum(purchase_details.quantity) as total,
    count(purchase_details.item_id) as cnt,
    max(purchase_details.quantity) as max_quantity_per_order,
    max(purchase_details.date) as latest_order,
    ...
  from purchase_details 
  GROUP BY purchase_details.item_id
) ABC on ABC.item_id = items.id;

I'm new in Pure Sql I want to write this as Query

select items.* 
from items 
LEFT OUTER JOIN 
    (select sum(purchase_details.quantity) as total 
    from purchase_details 
    where (purchase_details.item_id=items.id)  
GROUP BY purchase_details.item_id) ABC 

but this gives an error

 You have an error in your SQL syntax; check the manual that corresponds to 
    your MariaDB server version for the right syntax to use near 'LIMIT 0, 25' 
    at line 1

i don't know why it's not working

解决方案

The syntax error here is that you need an on-clause for your left join. But the underlying conceptual problem is different: you cannot join with a dependent subquery.

You can fix your query like this:

select items.* 
from items 
LEFT OUTER JOIN (
  select item_id, sum(purchase_details.quantity) as total
  from purchase_details 
  GROUP BY purchase_details.item_id
) ABC on ABC.item_id = items.id;

This moved your inner where-condition (that would depend on items.id, which is not allowed, as it is out of scope) to the on-clause. Thus item_id is also added in the inner select (as it is needed outside).

A different way to write this would be

select items.*, 
   (select sum(purchase_details.quantity) 
    from purchase_details 
    where purchase_details.item_id=items.id) as total
from items;

Here you have a dependent subquery: the inner where-clause depends on the outer items.id. You do not need a group by anymore, as the where-condition already uses just the rows for that item. (And you can also only return at most one row in this context anyway.)

Both queries are equivalent, and can (if the optimizer finds that execution plan) internally actually be executed in exactly the same way (which is nothing you have to care about much though, as long as you provide appropriate indexes).

So in your case you can use both (and maybe check which one is faster); if you want to get additional information for that item, you should prefer the left join-version though, e.g. use

...
LEFT OUTER JOIN (
  select item_id, 
    sum(purchase_details.quantity) as total,
    count(purchase_details.item_id) as cnt,
    max(purchase_details.quantity) as max_quantity_per_order,
    max(purchase_details.date) as latest_order,
    ...
  from purchase_details 
  GROUP BY purchase_details.item_id
) ABC on ABC.item_id = items.id;

这篇关于多表的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！