带有联合和连接的 SQL 查询 [英] SQL Query with union and join

问题描述

我的设置是这样的

表1:company_group - company_id, company_name表 2:商店 - store_id、store_name表 3:销售额 - sales_id、company_id、store_id、日期、销售额表 4:wh_sales - wh_sale_id、company_id、store_id、日期、销售额表5:购买-purchase_id、company_id、store_id、日期、购买

现在我可以使用 select 和 union 查询获取前四个表的数据，但是我无法弄清楚应该如何以及使用哪个连接来获取同一个表中 table5 的数据

我正在使用前四个表的查询

选择`company_group`.`company_name,`store`.`store_name`, MONTHNAME(date) AS MONTH,`sales`.`sales`来自 company_group、商店、销售其中`company_group`.`company.id`=`sales`.`company.id`和`store`.`store.id`=`sales`.`store.id`按 company_name,store_name, 'MONTH' 分组联盟选择`company_group`.`company_name,`store`.`store_name`, MONTHNAME(date) AS MONTH,`wh_sales`.`sales`来自 company_group, store,wh_sales其中`company_group`.`company.id`=`wh_sales`.`company.id`和`store`.`store.id`=`wh_sales`.`store.id`按 company_name,store_name, 'MONTH' 分组

现在如何包含 Table5 以便获得类似的结果

company_name store_name 月销售购买company-a store-c 十二月 40000 45000

解决方案

Sales vs Whole &销售，显示两者的购买价格对吗?试试这个.

选择`company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH,`sales`.`sales`, `purchase`.`purchase`来自 company_group、商店、销售、购买其中`company_group`.`company.id`=`sales`.`company.id`和`store`.`store.id`=`sales`.`store.id`按 company_name,store_name, 'MONTH' 分组联盟选择`company_group`.`company_name,`store`.`store_name`, MONTHNAME(date) AS MONTH,`wh_sales`.`sales`, `purchase`.`purchase`来自 company_group、商店、wh_sales、购买其中`company_group`.`company.id`=`wh_sales`.`company.id`和`store`.`store.id`=`wh_sales`.`store.id`按 company_name,store_name, 'MONTH' 分组

将 purchase.purchase 添加到 SELECT 子句和 purchase TABLE 到 FROM 子句提供对表的 purchase 价格字段的访问.其中，根据您的投影表的purchase 列是必不可少的AND 最简单的方法.

my set-up is like this

Table1: company_group - company_id, company_name
Table2: store         - store_id, store_name
Table3: sales         - sales_id, company_id, store_id, date, sales
Table4: wh_sales      - wh_sale_id, company_id, store_id, date, sales

Table5: purchase      - purchase_id, company_id, store_id, date, purchase

Now I am able to get the data for the first four tables using the select and union query, but I can't make out how and which join I should use to get the data for the table5 in the same table

I am using the query for the first four tables like

select `company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH,`sales`.`sales` 
from company_group, store,sales
where `company_group`.`company.id`=`sales`.`company.id`
and `store`.`store.id`=`sales`.`store.id`
group by company_name,store_name, 'MONTH'

UNION

select `company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH,`wh_sales`.`sales`
from company_group, store,wh_sales
where `company_group`.`company.id`=`wh_sales`.`company.id`
and `store`.`store.id`=`wh_sales`.`store.id`
group by company_name,store_name, 'MONTH'

now how can I include the Table5 so that I can get the result like

company_name store_name   month      sales    purchase
company-a     store-c    December    40000    45000

解决方案

Sales vs whole & sales, displaying the purchase price for both right ? Try this one.

select `company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH,`sales`.`sales`, `purchase`.`purchase`
from company_group, store,sales, purchase
where `company_group`.`company.id`=`sales`.`company.id`
and `store`.`store.id`=`sales`.`store.id`
group by company_name,store_name, 'MONTH'

UNION

select `company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH,`wh_sales`.`sales`, `purchase`.`purchase`
from company_group, store,wh_sales, purchase
where `company_group`.`company.id`=`wh_sales`.`company.id`
and `store`.`store.id`=`wh_sales`.`store.id`
group by company_name,store_name, 'MONTH'

Adding purchase.purchase to the SELECT clause and the purchase TABLE to the FROM clause provides access to the table's purchase price field. Which, according to your projected table's purchase column is essential AND the simplest method.

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