您的位置: 首页  >  文章  >  MySQL——4、SQL多表操作

MySQL——4、SQL多表操作

分类: 文章 • 2024-09-23 19:41:46

多表操作之行合并

  • 语法：
    Select 列名
    From 表A
    Union （all）
    Select 列名
    From 表B
  • 注意：
    1.合并二表字段/列数要相同
    2.Union : 二个或者多个表union的时候，会去掉重复的行。
    3.Union all : 二个或者多个表union的时候，不对重复的记录做处理。
    4.不能用排序order by ，想要排序，用子查询。见后。
  • 小案例熟悉
    周报：统计20170703-20170710周内每天及本周累计销售金额、订单量、会员数、订单占比
select sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
from dw.fct_sales
where dimDateID between '20170703' and '20170710';

select date_format(dimDateID,'%W') as week_day
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
								where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170710'
group by date_format(dimDateID,'%W');

用union把以上两个表合并起来：

select date_format(dimDateID,'%W') as week_day
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
								where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')
union 
select week(dimDateID,1) as week_1
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,'100%' as total
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by week(dimDateID,1);

结果显示：
MySQL——4、SQL多表操作

用union all把以上两个表合并起来：

select date_format(dimDateID,'%W') as week_day
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
								where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')
union all 
select week(dimDateID,1) as week_1
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,'100%' as total
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by week(dimDateID,1);

结果和union的结果一样。

对星期进行排序，用嵌套子查询

select t.*
from (
select date_format(dimDateID,'%W') as week_day
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
								where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')
-- order by date_format(dimDateID,'%W')
union 
select date_format(dimDateID,'%W') as week_day
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
								where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')) t
order by week_day

内关联：多表操作之多表关连(列）

  • 原理
    MySQL——4、SQL多表操作

  • 语法
    Select
    From 表A
    （INNER） JOIN 表B on 表A.字段=表B.字段
    （INNER ) JOIN 表C on 表C.字段=表B.字段
    …….
    Where .条件
    Group by 汇总

  • 多表关联注意点
    1、语法是 join 。。on
    2、如果没有on ,只有join:把1表中的每一行，都与2表中每一行匹配
    3、如果加上on ，表示在关联的只取能匹配上的，也就是相同的记录；如果不相同的记录，就不会生成；【把不匹配的不行都不显示】
    4、where 的作用，是把where满足条件的记录先筛选出来，再与另一张关联；如果不加where过滤条件，如果都匹配上，也相当于生成一个M*N行的相似数据量数据；
    5、在多表关联的时候：select 后面的列一定是表名.字段[避免关联的相关表的有名字相同的字段]
    6、在多表关联中，必须给每个表取别名；这样就更方便
    7、多表关联的时候，必须用相同意义的字段，但是字段的名字不一定是一样的！
    8、表中的字段名一样，并不意味着二个字段表示的意思也是一样！
    9、正常情况下：一定需要where 对表进行过滤；

外关联：多表操作之多表关连(列）

  • 左关联（常用）
    左表的所有信息都包括进去
    select * from 表名1
    left (inner) join 表名2 on关联字段
    where …….

  • 右关联:
    右表的所有信息都包括进去
    select *
    from 表名1
    right (inner) join 表名2 on 关联字段
    where …….

  • 注意点
    工作场景下很多时候我们统计的时候，外关联可以经常会结合子查询来使用（先会有一个子查询对某种表处理后，再关联；就可以避免重复）

select memeber_id
       ,sum(order_money) as order_money
       ,count(distinct t.id) as order_number
       ,sum(item_num) as item_nuber
from db_order.hy_order_20170410 t
left join 
	(select order_id
        ,sum(item_num) as item_num
	from db_order.hy_orderitem_20170410
	group by  order_id ) t1 on t1.order_id = t.id
group by memeber_id
order by sum(item_num) desc;

相关推荐