SQL助你面大厂（行列转换）

客官°小女子只卖身不卖艺 2023-10-14 23:12 133阅读 0赞

在面试中,不仅有算法题,还有这个老大难的SQL编写

![98ac9c7a7f6e45e3b5299f38871b53f5.png][] SQL在面试中也是会经常会被问到，不仅仅是为了面试，在做项目的时候，往往用的最多的就是CRUD,这也提高不了我们的编写SQL的能力，所以最近我准备总结几个面试模板，以及一些解决问题的技巧分享给大家，相信大家的SQL能力肯定会突飞猛进！！！

在这里先复习一下SQL中的执行顺序，了解执行顺序才是解决问题的最关键的问题

编写和执行顺序：

![d3804c82d29c4d83b32dd3b788558a23.png][]

首先我们现在数据库中进行创表，将我们等会要的数据先加入到数据库中

--创建我们需要的数据
    CREATE TABLE test(
    --年份
    YEAR VARCHAR(20),
    --月份
    MONTH VARCHAR(20),
    --营业额
    amount VARCHAR(20)
    )
    
    --往数据库里填写我们需要的信息
    INSERT INTO test(YEAR,MONTH,amount)VALUE('2002','1','1.1'),('2002','2','2.3'),('2002','3','3.1'),('2003','1','0.5'),('2003','2','1.6'),('2003','3','4.6')

这是不是与我们平时看的月份销量额有所不同，我们平时所看的是一般是这样的

![c4ab09d801e84b5ab1b8d9ee607b8e15.png][]

我们行转列、列转行的过程中，我们的**代码模板**是：

group by+sum(if())【或者count(if())】

我们先用case...when...来解决这个问题：

SELECT YEAR,
        SUM(CASE WHEN MONTH=1 THEN amount ELSE 0 END) AS M1,
         SUM(CASE WHEN MONTH=2 THEN amount ELSE 0 END) AS M2,
         SUM(CASE WHEN MONTH=3 THEN amount ELSE 0 END) AS M3
        FROM test
        GROUP BY YEAR

![584dc38a6b774660b261d57e0b30e0ef.png][]

结果和我们想的一致，这就达到了我们的预期，我们也就可以用三目运算符去代替case when

SELECT YEAR,
        SUM(IF(MONTH=1,amount,0)) AS M1,
        SUM(IF(MONTH=2,amount,0)) AS M2,
        SUM(IF(MONTH=3,amount,0)) AS M3
        FROM test
        GROUP BY YEAR

但是这里又有人问？博主博主，这个if和case when待敌有什么区别？

其实当结果只有两个的时候，if和case when其实是没什么区别的，if语句反而可读性较高，但是当结果有多个时，这个时候if语句就失去它的作用，反而case的功能更加强大一点，case when可以选择很多的种情况

case 
       when *** 
         then ***
       when ***
         then ***
       else
          ***
    end

但是又有人会问，为什么这样写可以将列转行，其实这里我们无意识间已经做了简化操作，其实上述的代码是我们简化后的代码，那让我们一步一步来讲SQL为什么会这么写？

先让我们看这个代码：

SELECT *,
        IF(MONTH=1,amount,0) AS M1
        FROM test

![8dadeb9c793842eb9277335d4fd7381e.png][]

![82616d3ecdc94a489aad7e8ec231cc71.png][]

在新加字段的M1中，除了2002年的1月份有值之外，其他月份都是0，所以这个时候我们将表按照year进行分组，得到的是不是

![906bb59451c34dafabd9afd5734c79f3.png][]

我们再将M1中求和是不是就是我们2002年的1月销售额

SELECT YEAR,
        SUM(IF(MONTH=1,amount,0)) AS M1
        FROM test
        GROUP BY YEAR

![10e91ccca32747f2bf61859b61ed47ce.png][]

这时又有人问了，这个为什么进行分组后，前面查询的\*要换成year？这是必须要进行换么？

这种情况其实也是很常见的，因为你对这些还是不太怎么了解，在进行分组（group by）后,前面的select 查询语句后面只需要查找group by后面跟的字段以及聚合函数，如果查找了其他字段会报错

SELECT *,
        SUM(IF(MONTH=1,amount,0)) AS M1
        FROM test
        GROUP BY YEAR

![1ba48adcfcc84fed90854552c5425df4.png][]M1解决了，我们再来看M2

SELECT YEAR,
        SUM(IF(MONTH=1,amount,0)) AS M1,
        SUM(IF(MONTH=2,amount,0)) AS M2
        FROM test
        GROUP BY YEAR

![fea9239e017d45bb841c68d987bccbec.png][]

所以原理就是如此，根据分组创建临时表，然后通过SUM函数进行操作

这道题相信大家已经看懂了！！！冲冲冲

下面有一道题可供大家练习：

![84f74933f41a41a8ba8899973879bd50.png][]

统计每天的胜负场次，以下图方式呈现：

![c3e0c2fed0d6426f8243bf82752cbd6d.png][]

CREATE TABLE win(
         DATE VARCHAR(20),
         result VARCHAR(20)    
        )
        
        INSERT INTO win(DATE,result)VALUE('2023-8-19','胜'),('2023-8-19','胜'),('2023-8-19','负'),('2023-8-20','负'),('2023-8-20','负')

*  **sum+if:**

SELECT DATE,
          SUM(IF(result='胜',1,0)) AS"胜",
           SUM(IF(result='负',1,0)) AS"负"
            FROM win
            GROUP BY DATE

![da3f97d7ca504e569dfa0b92c4b0527c.png][]

*  **count+if:**

SELECT DATE,
          COUNT(IF(result='胜',1,NULL)) AS"胜",
           COUNT(IF(result='负',1,NULL)) AS"负"
            FROM win
            GROUP BY DATE

![aea08428501b43279e907ed42970c18c.png][]

这时候肯定还有人问？![70f8611995404f46a5f5d8d089f29d82.png][]

为什么count在写判断的时候，要将0换成null，我们先将0换回来看看会发生什么事情？

SELECT DATE,
          COUNT(IF(result='胜',1,0)) AS"胜",
           COUNT(IF(result='负',1,0)) AS"负"
            FROM win
            GROUP BY DATE

![2377c7a7e4a14dd99a6200931f48befd.png][]

我们将分组暂时去掉，看看我们会查到什么数据

SELECT *,
          IF(result='胜',1,0) AS"胜",
          IF(result='负',1,0) AS '负'
            FROM win

![585a3a5b71734912aa4aec80d4ae2c18.png][]

这里就要说一下count聚合函数了，它是统计数据条数的，只有为null值，count才会无视这条数据，所以在统计条数的时候，避免字段中出现null值，但是统计这些结果值的时候，我们往往把不存在的数据置为null，避免统计时出现问题，因为0也是代表一条正确的数据，所以count在统计的时候也会将数值为0的数据计入到结果集中，这就出现了错误

今天的面试专题行列互换已经结束了，希望此文对你们有所帮助！！！

这么辛苦，给个关注再走呗！！！

[98ac9c7a7f6e45e3b5299f38871b53f5.png]: https://img-blog.csdnimg.cn/98ac9c7a7f6e45e3b5299f38871b53f5.png
[d3804c82d29c4d83b32dd3b788558a23.png]: https://img-blog.csdnimg.cn/d3804c82d29c4d83b32dd3b788558a23.png
[c4ab09d801e84b5ab1b8d9ee607b8e15.png]: https://img-blog.csdnimg.cn/c4ab09d801e84b5ab1b8d9ee607b8e15.png
[584dc38a6b774660b261d57e0b30e0ef.png]: https://img-blog.csdnimg.cn/584dc38a6b774660b261d57e0b30e0ef.png
[8dadeb9c793842eb9277335d4fd7381e.png]: https://img-blog.csdnimg.cn/8dadeb9c793842eb9277335d4fd7381e.png
[82616d3ecdc94a489aad7e8ec231cc71.png]: https://img-blog.csdnimg.cn/82616d3ecdc94a489aad7e8ec231cc71.png
[906bb59451c34dafabd9afd5734c79f3.png]: https://img-blog.csdnimg.cn/906bb59451c34dafabd9afd5734c79f3.png
[10e91ccca32747f2bf61859b61ed47ce.png]: https://img-blog.csdnimg.cn/10e91ccca32747f2bf61859b61ed47ce.png
[1ba48adcfcc84fed90854552c5425df4.png]: https://img-blog.csdnimg.cn/1ba48adcfcc84fed90854552c5425df4.png
[fea9239e017d45bb841c68d987bccbec.png]: https://img-blog.csdnimg.cn/fea9239e017d45bb841c68d987bccbec.png
[84f74933f41a41a8ba8899973879bd50.png]: https://img-blog.csdnimg.cn/84f74933f41a41a8ba8899973879bd50.png
[c3e0c2fed0d6426f8243bf82752cbd6d.png]: https://img-blog.csdnimg.cn/c3e0c2fed0d6426f8243bf82752cbd6d.png
[da3f97d7ca504e569dfa0b92c4b0527c.png]: https://img-blog.csdnimg.cn/da3f97d7ca504e569dfa0b92c4b0527c.png
[aea08428501b43279e907ed42970c18c.png]: https://img-blog.csdnimg.cn/aea08428501b43279e907ed42970c18c.png
[70f8611995404f46a5f5d8d089f29d82.png]: https://img-blog.csdnimg.cn/70f8611995404f46a5f5d8d089f29d82.png
[2377c7a7e4a14dd99a6200931f48befd.png]: https://img-blog.csdnimg.cn/2377c7a7e4a14dd99a6200931f48befd.png
[585a3a5b71734912aa4aec80d4ae2c18.png]: https://img-blog.csdnimg.cn/585a3a5b71734912aa4aec80d4ae2c18.png