最近女朋友要准备软件测试的面试,看到了一个SQL的面试题,找我要答案,我觉得挺有意思,特分享出来。

题目要求结合2张基本表的内容,按要求输出需要的结果。

基本表A:

user_idname
1000张三
1001李四

基本表B:

key_iduser_idresult
00011000
00021001
00031001
00041000
00051000

输出的结果表C

姓名
张三21
李四11

解法一:观察结果表可以看到胜负数通过B表的result可以推到出来,姓名可以用user_id暂时替代,中间表B_result如下

user_idwinfail
100021
100111

生成中间表SQL:

SELECT
    USER_ID,
    SUM(CASE WHEN RESULT = '胜' THEN 1 END) WIN,
    SUM(CASE WHEN RESULT = '负' THEN 1 END) FAIL
FROM B
GROUP BY USER_ID

完整的SQL:

SELECT
  A.NAME AS '姓名',
  WIN AS '胜',
  FAIL AS '负'
FROM A
  LEFT JOIN (
              SELECT
                USER_ID,
                SUM(CASE WHEN RESULT = '胜' THEN 1 END) WIN,
                SUM(CASE WHEN RESULT = '负' THEN 1 END) FAIL
              FROM B
              GROUP BY USER_ID) B_RESULT
  ON A.USER_ID = B_RESULT.USER_ID;

总结: 解法一的需要CASE WHEN语法和GROUP BY,其实就是一个行转列的体现。


解法二:同解法一,可以通过GROUP BY分组的形式生成中间表,SQL如下:

SELECT USER_ID,RESULT,COUNT(1) NUM  FROM B GROUP BY USER_ID,RESULT;

中间表如下:

user_idresultnum
10002
10001
10011
10011

接下来需要行转列:

SELECT
  USER_ID,
  SUM(CASE WHEN RESULT = '胜' THEN NUM END) WIN,
  SUM(CASE WHEN RESULT = '负' THEN NUM END) FAIL
FROM(
  SELECT USER_ID,RESULT,COUNT(1) NUM
  FROM B GROUP BY USER_ID,RESULT)B_TEMP GROUP BY USER_ID;

行转列的结果:

user_idwinfail
100021
100111

再关联表A即可:

SELECT NAME,WIN,FAIL FROM A LEFT JOIN (
  SELECT
      USER_ID,
      SUM(CASE WHEN RESULT = '胜'THEN NUM END) WIN,
      SUM(CASE WHEN RESULT = '负'THEN NUM END) FAIL
   FROM (
          SELECT USER_ID, RESULT,COUNT(1) NUM FROM B GROUP BY USER_ID, RESULT 
    ) B_TEMP
   GROUP BY USER_ID) B_RESULT ON A.USER_ID = B_RESULT.USER_ID

标签: MySQL

仅有一条评论

  1. 开头5个字劝退,向大佬低头

添加新评论