使用大量表进行选择

Question

我试图对销售报告进行查询，其中将显示销售代码、日期、产品数量(仅限于)、门票数量、总销售额以及员工姓名。我所使用的单击此处查看表之间的连接。查询，它不返回错误，而是只返回一行，即使Sales表中没有任何内容，也可以返回满行。

select sales.codv
, customers.name as customers
, concat(day(datav),'/',month(datav),'/',year(datav)) as date
, sum(prod_sal.amount) as 'products'
, count(tickets.codb) as 'tickets'
, sum(ifnull(products.preco,0) * ifnull(prod_sal.amount,0) + 
ifnull(tickets.price,0)) as 'total/€'
, employees.nome as employee
from sales
LEFT JOIN customers ON customers.codc=sales.codc
INNER JOIN employees ON employees.codf=sales.codf
LEFT JOIN prod_sal ON prod_sal.codv=sales.codv
LEFT JOIN products ON products.codp=prod_sal.codp
LEFT JOIN tickets ON tickets.codv=sales.codv

注意事项：datav是销售日期

Answer 1

如果不使用group by子句，MySQL将假设要聚合与where子句匹配的所有行。由于您没有where子句，这就是数据库中的所有内容。

select 
  sales.codv
  , customers.name as customers
  , concat(day(datav),'/',month(datav),'/',year(datav)) as date
  , sum(prod_sal.amount) as 'products'
  , count(tickets.codb) as 'tickets'
  , sum(ifnull(products.preco,0) * ifnull(prod_sal.amount,0) + ifnull(tickets.price,0)) as 'total/€'
  , employees.nome as employee
from 
  sales
  LEFT JOIN customers 
    ON customers.codc=sales.codc
  INNER JOIN employees 
    ON employees.codf=sales.codf
  LEFT JOIN prod_sal 
    ON prod_sal.codv=sales.codv
  LEFT JOIN products 
    ON products.codp=prod_sal.codp
  LEFT JOIN tickets 
    ON tickets.codv=sales.codv
group by
  sales.codv
  , customers.name
  ,  concat(day(datav),'/',month(datav),'/',year(datav)) as date