006 MySQL SQL语句深入

感谢好兄弟闫先强 提供SQL语句

外连接形象图

把相同项的表，抽出字段，用括号包裹起来做成一个虚拟表，在不断的抽字段组合成新的表

内连接形象图

level1

SELECT sname  FROM school.`student` where sname like "张%" ;

level2

SELECT a.sid,a.sname,c.cid,c.cname, b.num  FROM student a JOIN score b on a.sid= b.student_id JOIN course c on b.course_id = c.cid

level 3

SELECT c.* ,hsrkkqcx.PHONENO FROM (SELECT a.*,linyi_txz_place.`name` as csmc FROM (SELECT *  FROM linyi_txz_place_record WHERE place_id ='9de03bf4eec54b268eb4f611da09b176'AND create_time >'2022-11-04 00:00:00'
) a  LEFT JOIN linyi_txz_place ON a.place_id=linyi_txz_place.id ) c LEFT JOIN hsrkkqcx ON c.card_number= hsrkkqcx.ID_CARD

level4

select
7yw1.province_name  as 省,7yw1.city as 市,7yw1.ls as '7月-w1GMV',7yw1.lkmll as '7月-w1老客交易利润率',7yw1.mll as '7月-w1总交易利润率',
7yw2.ls as '7月-w2GMV',7yw2.lkmll as '7月-w2老客交易利润率',7yw2.mll as '7月-w2总交易利润率',
7yw3.ls as '7月-w3GMV',7yw3.lkmll as '7月-w3老客交易利润率',7yw3.mll as '7月-w3总交易利润率',
7yw4.ls as '7月-w4GMV',7yw4.lkmll as '7月-w4老客交易利润率',7yw4.mll as '7月-w4总交易利润率',
7yw5.ls as '7月-w5GMV',7yw4.lkmll as '7月-w5老客交易利润率',7yw5.mll as '7月-w5总交易利润率',
8yw1.ls as '8月-w1GMV',8yw1.lkmll as '8月-w1老客交易利润率',8yw1.mll as '8月-w1总交易利润率',
8yw2.ls as '8月-w2GMV',8yw2.lkmll as '8月-w2老客交易利润率',8yw2.mll as '8月-w2总交易利润率',
8yw3.ls as '8月-w3GMV',8yw3.lkmll as '8月-w3老客交易利润率',8yw3.mll as '8月-w3总交易利润率',
8yw4.ls as '8月-w4GMV',8yw4.lkmll as '8月-w4老客交易利润率',8yw4.mll as '8月-w4总交易利润率'
from 
(select 
a.province_name,
if(a.city_name in ('市辖区','县','请选择市'),a.province_name,a.city_name)  as city,
sum(amount_gun) as ls,
sum(if(is_first=0,gross_profit,null))/sum(if(is_first=0,amount_gun,null)) as lkmll,
sum(gross_profit)/sum(amount_gun) as mll
from chezhubangapp.yfq_order_oil a
left join (
select
order_code,gross_profit
from settlement_order.order_gross_profit 
where date(pay_dt)>='2020-06-29'
and date(pay_dt)<='2020-07-05'
)b on a.order_id=b.order_code
where order_pay_flag in (1,4,6)
and date(a.pay_dt)>='2020-06-29'
and date(a.pay_dt)<='2020-07-05'
group by 1,2) 7yw1
left join 
(select 
a.province_name,
if(a.city_name in ('市辖区','县','请选择市'),a.province_name,a.city_name)  as city,
sum(amount_gun) as ls,
sum(if(is_first=0,gross_profit,null))/sum(if(is_first=0,amount_gun,null)) as lkmll,
sum(gross_profit)/sum(amount_gun) as mll
from chezhubangapp.yfq_order_oil a
left join (
select
order_code,gross_profit
from settlement_order.order_gross_profit 
where date(pay_dt)>='2020-07-06'
and date(pay_dt)<='2020-07-12'
)b on a.order_id=b.order_code
where order_pay_flag in (1,4,6)
and date(pay_dt)>='2020-07-06'
and date(pay_dt)<='2020-07-12'
group by 1,2) 7yw2 on 7yw1.city=7yw2.city and 7yw1.province_name=7yw2.province_name
left join 
(select 
a.province_name,
if(a.city_name in ('市辖区','县','请选择市'),a.province_name,a.city_name)  as city,
sum(amount_gun) as ls,
sum(if(is_first=0,gross_profit,null))/sum(if(is_first=0,amount_gun,null)) as lkmll,
sum(gross_profit)/sum(amount_gun) as mll
from chezhubangapp.yfq_order_oil a
left join (
select
order_code,gross_profit
from settlement_order.order_gross_profit 
where date(pay_dt)>='2020-07-13'
and date(pay_dt)<='2020-07-19'
)b on a.order_id=b.order_code
where order_pay_flag in (1,4,6)
and date(pay_dt)>='2020-07-13'
and date(pay_dt)<='2020-07-19'
group by 1,2) 7yw3 on 7yw1.city=7yw3.city and 7yw1.province_name=7yw3.province_name

left join 
(select 
a.province_name,
if(a.city_name in ('市辖区','县','请选择市'),a.province_name,a.city_name)  as city,
sum(amount_gun) as ls,
sum(if(is_first=0,gross_profit,null))/sum(if(is_first=0,amount_gun,null)) as lkmll,
sum(gross_profit)/sum(amount_gun) as mll
from chezhubangapp.yfq_order_oil a
left join (
select
order_code,gross_profit
from settlement_order.order_gross_profit 
where date(pay_dt)>='2020-07-20'
and date(pay_dt)<='2020-07-26'
)b on a.order_id=b.order_code
where order_pay_flag in (1,4,6)
and date(pay_dt)>='2020-07-20'
and date(pay_dt)<='2020-07-26'
group by 1,2) 7yw4 on 7yw1.city=7yw4.city and 7yw1.province_name=7yw4.province_name
left join 
(select 
a.province_name,
if(a.city_name in ('市辖区','县','请选择市'),a.province_name,a.city_name)  as city,
sum(amount_gun) as ls,
sum(if(is_first=0,gross_profit,null))/sum(if(is_first=0,amount_gun,null)) as lkmll,
sum(gross_profit)/sum(amount_gun) as mll
from chezhubangapp.yfq_order_oil a
left join (
select
order_code,gross_profit
from settlement_order.order_gross_profit 
where date(pay_dt)>='2020-07-27'
and date(pay_dt)<='2020-08-02'
)b on a.order_id=b.order_code
where order_pay_flag in (1,4,6)
and date(pay_dt)>='2020-07-27'
and date(pay_dt)<='2020-08-02'
group by 1,2) 7yw5 on 7yw1.city=7yw5.city and 7yw1.province_name=7yw5.province_name
left join 
(select 
a.province_name,
if(a.city_name in ('市辖区','县','请选择市'),a.province_name,a.city_name)  as city,
sum(amount_gun) as ls,
sum(if(is_first=0,gross_profit,null))/sum(if(is_first=0,amount_gun,null)) as lkmll,
sum(gross_profit)/sum(amount_gun) as mll
from chezhubangapp.yfq_order_oil a
left join (
select
order_code,gross_profit
from settlement_order.order_gross_profit 
where date(pay_dt)>='2020-08-03'
and date(pay_dt)<='2020-08-09'
)b on a.order_id=b.order_code
where order_pay_flag in (1,4,6)
and date(pay_dt)>='2020-08-03'
and date(pay_dt)<='2020-08-09'
group by 1,2) 8yw1 on 7yw1.city=8yw1.city and 7yw1.province_name=8yw1.province_name
left join 
(select 
a.province_name,
if(a.city_name in ('市辖区','县','请选择市'),a.province_name,a.city_name)  as city,
sum(amount_gun) as ls,
sum(if(is_first=0,gross_profit,null))/sum(if(is_first=0,amount_gun,null)) as lkmll,
sum(gross_profit)/sum(amount_gun) as mll
from chezhubangapp.yfq_order_oil a
left join (
select
order_code,gross_profit
from settlement_order.order_gross_profit 
where date(pay_dt)>='2020-08-10'
and date(pay_dt)<='2020-08-16'
)b on a.order_id=b.order_code
where order_pay_flag in (1,4,6)
and date(pay_dt)>='2020-08-10'
and date(pay_dt)<='2020-08-16'
group by 1,2) 8yw2 on 7yw1.city=8yw2.city and 7yw1.province_name=8yw2.province_name
left join 
(select 
a.province_name,
if(a.city_name in ('市辖区','县','请选择市'),a.province_name,a.city_name)  as city,
sum(amount_gun) as ls,
sum(if(is_first=0,gross_profit,null))/sum(if(is_first=0,amount_gun,null)) as lkmll,
sum(gross_profit)/sum(amount_gun) as mll
from chezhubangapp.yfq_order_oil a
left join (
select
order_code,gross_profit
from settlement_order.order_gross_profit 
where date(pay_dt)>='2020-08-17'
and date(pay_dt)<='2020-08-23'
)b on a.order_id=b.order_code
where order_pay_flag in (1,4,6)
and date(pay_dt)>='2020-08-17'
and date(pay_dt)<='2020-08-23'
group by 1,2) 8yw3 on 7yw1.city=8yw3.city and 7yw1.province_name=8yw3.province_name
left join 
(select 
a.province_name,
if(a.city_name in ('市辖区','县','请选择市'),a.province_name,a.city_name)  as city,
sum(amount_gun) as ls,
sum(if(is_first=0,gross_profit,null))/sum(if(is_first=0,amount_gun,null)) as lkmll,
sum(gross_profit)/sum(amount_gun) as mll
from chezhubangapp.yfq_order_oil a
left join (
select
order_code,gross_profit
from settlement_order.order_gross_profit 
where date(pay_dt)>='2020-08-24'
and date(pay_dt)<='2020-08-26'
)b on a.order_id=b.order_code
where order_pay_flag in (1,4,6)
and date(pay_dt)>='2020-08-24'
and date(pay_dt)<='2020-08-26'
group by 1,2) 8yw4 on 7yw1.city=8yw4.city and 7yw1.province_name=8yw4.province_name