MySQL數據庫多表之間的查詢
問題描述
問題解答
回答1:思路一分兩種情況選出符合要求的company_id并union
把這些company_id的earning求和(2013-2014)
連接上company_name
好像搞的比較復雜。
with cid(id) as ( select company_id from tableB where year = 2014 and earning > 20 union select company_id from tableB where year in (2013, 2014) group by company_id having sum(earning) > 50), cid_earning(id, earning) as ( select company_id, sum(earning) from tableB where company_id in (select id from cid) and year in (2013, 2014) group by company_id)select a.company_name, c.earningfrom cid_earning c left join tableA a using(id)思路二
如果把2013和2014年的earning作為表的兩個field,SQL的邏輯會清晰很多:
withe3(id, earning) as ( select company_id, earning from tableB where year = 2013), e4(id, earning) as ( select company_id, earning from tableB where year = 2014)select a.company_name, e3.earning + e4.earning as earningfrom e3 inner join e4 using(id)left join tableA a using(id)where e4.earning > 20 or e3.earning + e4.earning > 50回答2:
好復雜哦,同問,這樣的sql怎么寫,我在想是不是可以寫個存儲過程,畢竟存儲過程處理這樣復雜的邏輯容易一點
相關文章:
1. 請教一個python字符串處理的問題?2. php - 第三方支付平臺在很短時間內多次異步通知,訂單多次確認收款3. 我在導入模板資源時遇到無法顯示的問題,請老師解答下4. mysql新建字段時 timestamp NOT NULL DEFAULT ’0000-00-00 00:00:00’ 報錯5. python - django 按日歸檔統計訂單求解6. javascript - 百度echarts series數據更新問題7. objective-c - iPhone如何實現微信的搖一搖功能?8. mysql - 一個表和多個表是多對多的關系,該怎么設計9. mysql scripts提示 /usr/bin/perl: bad interpreter10. Mysql && Redis 并發問題
