問題描述
我的設置是這樣的
表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' 分組聯(lián)盟選擇`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 45000Sales 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' 分組聯(lián)盟選擇`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.
這篇關于帶有聯(lián)合和連接的 SQL 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！