Sql笔记

2021-10-14 约 2322 字预计阅读 5 分钟次阅读

Mysql行列转置

需求

我们想跑一个数据，格式如下图：

但是我们一般的mysql语句跑出来的数据却是下面这样，不但不方便查看，在数据量比较大的时候，我们需要每个地区都转置粘贴一遍，耗时耗力还容易出错，下面提供一个方法，可以让跑出来的数据就是你一开始想要的样子（上图），不但方便线上化的查看，也方便线下的粘贴。

方法

代码 - case when语句：

1
2
3
4
5
6
7


SELECT t.`地区`,
COUNT(DISTINCT (case WHEN t.班级 =1 THEN t.学号 end)) AS'班级1',
COUNT(DISTINCT (case WHEN t.班级 =2 THEN t.学号 end)) AS'班级2',
COUNT(DISTINCT (case WHEN t.班级 =3 THEN t.学号 end)) AS'班级3',
COUNT(DISTINCT (case WHEN t.班级 =4 THEN t.学号 end)) AS'班级4'
FROM cm1_0625 t 
where t.`地区`<>'' GROUP BY 1;

代码 - IF语句：

1
2
3
4
5
6
7


SELECT t.`地区`,
COUNT(DISTINCT IF (t.班级 =1 , t.学号 ,0)) AS'班级1',
COUNT(DISTINCT IF (t.班级 =2 , t.学号 ,0)) AS'班级2',
COUNT(DISTINCT IF (t.班级 =3 , t.学号 ,0)) AS'班级3',
COUNT(DISTINCT IF (t.班级 =4 , t.学号 ,0)) AS'班级4'
FROM cm1_0625 t 
where t.`地区`<>'' GROUP BY 1;

运行结果：

将A列赋值给B列

Mysql

1

UPDATE tableName SET b = a;

PostgreSql

1

UPDATE tableName SET "b" = "a";

left join的用法

在讲left join（左关联）之前，要说下right join（右关联）的用法其实类似left join,把right join想象成是left join的一种转化方式即可。

我们工作中经常会用到left join来进行联表取数分析，今天就专门来讲讲left join的用法。很多基础的数据库应用的书籍都有讲到left join的用法，但是都没有深入在运用过程中应该注意哪些地方。如果你仅是单纯地套用你会发现在实际工作中会遇到各种各样的问题。

一、基本语句

1
2
3
4
5
6
7


  select
     a.id,
     a.column,
     b.id,
     b.column
   from a
   left join b on a.id = b.id

在这里a表与b表以主键id进行联接。查询的结果保留a表中全部的id数据和选到的某列的数据，而b.id和其他列只保留与a表相匹配的数据。而不匹配的b表的id和其他列以null填充。这个用法类似excel中的vlookup的用法。掌握了vlookup的用法，基本上对left join的用法也掌握了。

二、如何解决一对多的问题

当a表有100行数据，而b表有1000行数据，并且b表中的b.id是有重复值的，而且在实际应用过程中，这确实属于正常现象。我们可以想象a表是用户表，b表是订单表。自然也就想到了一个用户可能多次下单。我们假设b表中的用户id在a表中匹配到50个用户id，但是这50个用户id总订单数是500个。那么当我们在执行以上sql语句时，就会出现查询的结果550条，为什么呢？因为当left join 一对多的时候，就会出现将b表中相同匹配的数据填充到查询结果去。如何解决这种问题呢，我们可以根据实际情况来做调整。

把一对多的问题转化成聚合查询

如果业务需求是要查询用户的下单量，可以将sql语句编程如下

1
2
3
4
5
6
7


 select 
      a.用户id, 
      a.用户姓名, 
      count(订单id) 
  from 用户表 a 
  left join 订单表b on a.用户id = b.用户id
  group by a.用户id, a.用户姓名

把一对多的问题转化成多对一的问题

如果业务需求是要查询用户的下单明细，可以将sql语句编程如下

1
2
3
4
5
6
7
8
9


 select 
      a.订单id, 
      a.业务类型
      a.单价
      a.消费数量
      b.用户姓名, 
      b.用户联系方式
  from 订单表 a 
  left join 用户表b on a.用户id = b.用户id

三、条件联接查询

以上的sql语句都是全量查询，在面对表数据较大的情况，全量查询是非常耗时的。所以查询过程中，我们一定要运用where子句来限定条件，提高查询效率。在这里有两种方式：一种是全量查询后+where子句，一种是联接前就先做限定。

全量查询后+where子句。

如查询用户为男，消费总价大于1w元的用户明细

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


 select 
      a.订单id, 
      a.业务类型
      a.单价
      a.消费数量
      b.用户姓名, 
      b.用户联系方式
  from 订单表 a 
  left join 用户表b on a.用户id = b.用户id
  where b.用户性别 = '男'
  and (a.单价 * a.消费数量) > 10000
  and b.用户id is not null

联接前就先做限定。

如我们需要查询用户在某时间段，访问A页面再访问B页面的留存用户量。查询的表只有用户行为表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


select 
   count(a.user_id),
   count(b.user_id)
 from
 (select 
      distinct
      user_id
  from 用户行为表
  where date_str = '2016-11-01'
  and url = ‘A页面路径’)a
left join
  (select 
      distinct
      user_id
   from 用户行为表
   where date_str = '2016-11-01'
   and url = ‘B页面路径’)b on a.user_id = b.user_id
 where b.user_id is not null

OK! 先讲到这里，希望对大家有用！

目录