SQL查询语句:
1. 查询中用到的关键词主要包含六个,书写顺序为 select--from--where--group by--having--order by
2. 但是他们的执行顺序为 from--where--group by--having--select--order by
3. 多表查询的执行顺序为 from--join--on--where--group by--聚合函数--having--select--order by
4. 筛选功能实现: on--where--having (on是用在join之后,多表联合查询;where最终表的筛选;having是分组之后的中间表的筛选)
inner join 与 left join 之间的区别:
需求是从数据库查数据,在前端以柱形图的形式展现出来,查到的数据按行业分组,显示每个行业的户数及户数占比,涉及到的字段有A表的用户数、总用户数和B表的行业名称。本来是不管查不查的到数据,在X轴都应该显示行业名称的,结果是X、Y轴都没有任何数据显示。问题就是我用错了联结方式。
一、sql的left join 、right join 、inner join之间的区别
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 ,没有显示null
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录,没有显示null
inner join(等值连接) 只返回两个表中联结字段相等的行
举例如下:
--------------------------------------------
表A记录如下:
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115
表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
--------------------------------------------
1.left join
sql语句如下:
select * from A
left join B
on A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
(所影响的行数为 5 行)
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
--------------------------------------------
2.right join
sql语句如下:
select * from A
right join B
on A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
(所影响的行数为 5 行)
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
--------------------------------------------
3.inner join
sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
工作里数据分析常写的SQL语句总结,希望对你用帮助:
```sql整体的逻辑:
(1)先确定一个基准表a
(2)再根据你自己的需要去连接其他表,得到你想查询的数据
(3)熟悉各种连接方式及分组,最常用的是左连接和各种聚合函数
select a.id,c.qudao,sum(b.money),count(distinct c.touziid)
from a --基准表
left join
(
select b.money
from b
)bb on a.id = b.user_id
left join c on ...
where 注册时间='2018-08-26'
group by a.id, c.qudao
having sum(b.money)>=100万
select uu.id,(ifnull(ph.bid_money_first_stage,0)+ifnull(ph.bid_money_second_stage,0)+ifnull(ph.bid_money_third_stage,0)),
ph.bid_money_fourth_stage,ph.bid_money_fifth_stage,re.s_money,kk.s_money,
case when csv.id6=2 and csv.id1='专业渠道' then 'a专业渠道'
else '其他' end
from 表1 uu
left join
(
select
user_id,bid_money_first_stage,bid_money_second_stage,bid_money_third_stage,bid_money_fourth_stage,bid_money_fifth_stage
from 表2
)ph on uu.id=ph.user_id
left join
(
select user_id,sum(money) as s_money
from 表3
where date='2018-05-31'
group by user_id
)re on uu.id=re.user_id
left join
(
select nn.user_id,sum(nn.sss_money) as s_money
from
(
select aa.user_id,sum(aa.mm) as sss_money
from
(
select loan_user_id as user_id,sum(money) as mm
from 表4
where from_unixtime(time+8*3600,'yyyy-MM-dd') between '2018-06-01' and '2018-06-10' and status=0
and type in (1,2,3,7) group by loan_user_id
)aa
group by aa.user_id having sum(aa.mm)>0
union all
select bb.user_id,sum(bb.mm) as sss_money
from
(
select id as user_id,sum(money) as mm
from 表5 where date='2018-05-31'
group by id
union all
select user_id,sum(money) as mm
from 表6
where date='2018-05-31' group by user_id
)bb
group by bb.user_id
) nn
group by nn.user_id
) kk on uu.id=kk.user_id
left join 表7 csv on (case when (uu.current_refer_group_id is not null and uu.current_refer_group_id!=0) then uu.current_refer_group_id=csv.id3 else uu.site_id=csv.id7 end)
where re.s_money>0 and kk.s_money>1000
```
```sql
笛卡尔积问题:
select uu.current_refer_user_id,
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end,
count(distinct uu.id),count(distinct dx.user_id),sum(dx.mm),sum(nh.nianhua),sum(ye.mm),sum(dlr.sss_money),sum(charge.mm),sum(uc.ss_money),sum(dx1.money)
from
(
select id,current_refer_user_id,current_refer_group_id
from 表1
where current_refer_group_id in (1,2)
) uu
left join
(
select user_id, sum(money) as mm
from 表2
where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
group by user_id
)dx on uu.id = dx.user_id
left join
(
select aa.user_id,sum(aa.nianhua) as nianhua
from
(
select user_id, sum(nianhua) as nianhua
from 表3
where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
group by user_id
union all
select user_id,sum(nianhua) as nianhua
from 表4
where logdate between '2018-03-01' and '2018-03-31'
group by user_id
union all
select user_id,sum(nianhua) as nianhua
from 表5
where date between '2018-03-01' and '2018-03-31'
group by user_id
)aa
group by aa.user_id
)nh on dx.user_id = nh.user_id
left join
(
select nn.uuid,sum(nn.s_money) as mm
from
(
select id as uuid,sum(money) as s_money
from 表6
where date = '2018-02-28'
group by id
union all
select user_id as uuid,sum(money) as s_money
from 表7
where date = '2018-02-28'
group by user_id
) nn
group by nn.uuid
) ye on uu.id = ye.uuid
left join
(
select aa.user_id,sum(aa.mm) as sss_money
from
(
select loan_user_id as user_id,sum(money) as mm
from 表8
where from_unixtime(real_time+8*3600,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
and status=1 and type in (1,2,3,7)
group by loan_user_id
union all
select user_id,sum(money) as mm
from 表9
where from_unixtime(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
and withdraw_status=1
group by user_id
union all
select user_id,sum(money/100) as mm
from 表10
where from_unixtime(finish_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
and status=4
group by user_id
)aa
group by aa.user_id
) dlr on uu.id = dlr.user_id
left join
(
select aa.user_id,sum(aa.s_money) as mm
from
(
select user_id,sum(money) as s_money
from 表11
where from_unixtime(pay_time+8*3600,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
and is_paid=1
group by user_id
union all
select user_id,sum(amount/100) as s_money
from 表12
where pay_status=1 and from_unixtime(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
group by user_id
)aa
group by aa.user_id
)charge on uu.id = charge.user_id
left join
(
select aa.user_id,sum(aa.mm) as ss_money
from
(
select user_id,sum(money) as mm
from 表13
where from_unixtime(withdraw_time+8*3600,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
and withdraw_status=1
group by user_id
union all
select user_id,sum(amount/100) as mm
from 表14
where type=0 and withdraw_status=1
and from_unixtime(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
group by user_id
) aa
group by aa.user_id
)uc on uu.id = uc.user_id
left join
(
select owner_user_id,deal_load_id
from 表15
where status=2 and from_unixtime(update_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
) od on uu.id = od.owner_user_id
left join
(
select user_id, deal_load_id,money
from 表16
where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
)dx1 on dx1.deal_load_id = od.deal_load_id and dx1.user_id=od.owner_user_id
group by uu.current_refer_user_id,
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end
正确的:
select uu.current_refer_user_id,
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end,
count(distinct uu.id),count(distinct dx.user_id),sum(dx.mm),sum(nh.nianhua),sum(ye.mm),sum(dlr.sss_money),sum(charge.mm),sum(uc.ss_money)
from
...
group by uu.current_refer_user_id,
case
when uu.current_refer_group_id in (12,436) then 'c'
when uu.current_refer_group_id = 11 then 'b'
else 'a' end
+
select uu.current_refer_user_id,
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end,
sum(dx.money),sum(nh.nianhua)
from
(
select id,current_refer_user_id,current_refer_group_id
from DC_firstp2p_user
where current_refer_group_id in (1,2,3)
) uu
left join
(
select id,owner_user_id,deal_load_id
from o2o_discount
) od on uu.id = od.owner_user_id
inner join
(
select user_id, deal_load_id,money
from DC_all_deal_load_xin
where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-05-01' and '2018-05-29'
and invest_rank >=1
)dx on dx.deal_load_id = od.deal_load_id and dx.user_id=od.owner_user_id
group by uu.current_refer_user_id
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end
```
```sql
--实现同样效果的多重写法
--1
select uu.current_refer_user_id,sum(dx.mm),
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end
from
(
select id,current_refer_user_id,current_refer_group_id
from 表1
where current_refer_group_id in (1,2,3)
) uu
left join
(
select user_id, sum(money) as mm
from 表2
where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
group by user_id
)dx on uu.id = dx.user_id
group by uu.current_refer_user_id,
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end
--2
select aa.id,aa.current_refer_user_id,aa.qudao,dx1.money,dx1.product_class,dx1.deal_load_id
from
(
select uu.id,uu.current_refer_user_id,
case
when uu.current_refer_group_id in (436,12) then 'a'
when uu.current_refer_group_id in (11) then 'b'
when uu.current_refer_group_id in (382,383,384,100) then 'c'
else '其他' end as qudao
from DC_firstp2p_user uu
where uu.current_refer_group_id in(1,2,3)
) aa
left join
(
select user_id,deal_load_id,money,product_class
from 表1
where from_unixtime(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
)dx1 on aa.id=dx1.user_id
--3
select uu.current_refer_user_id,
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end,
dx.mm
from
(
select id,current_refer_user_id,current_refer_group_id
from 表1
where current_refer_group_id in (1,2,3)
) uu
left join
(
select user_id, sum(money) as mm
from 表2
where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
group by user_id
)dx on uu.id = dx.user_id
--4
select uu.current_refer_user_id,
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end,
sum(dx.money)
from
(
select id,current_refer_user_id,current_refer_group_id
from 表1
where current_refer_group_id in (1,2,3)
) uu
left join
(
select user_id, money
from表2
where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
)dx on uu.id = dx.user_id
group by uu.current_refer_user_id,
case
when uu.current_refer_group_id in (12,436) then 'a'
when uu.current_refer_group_id = 11 then 'b'
else 'c' end
```