SQL如何一键生成日报？（concat、条件汇总、分组）

【面试题】

A公寓为A地区的租房公司，现有房间表、公寓表，社区表。

房间表：包含房间id，房间名称，公寓id，出租的状态：已租、未租

公寓表：包含公寓id，公寓编号，该公寓是否为自营，对应的小区id

 社区表：包含小区id，小区名称和对应的街道名称

 

请用一句SQL语句取出公寓每日报表，显示结果如下图：

 

【解题步骤】

题目要求报表的格式，其中公寓编号、是否为自营、小区地址为现有表中的字段，需要添加的字段是未出租数量、该公寓所有房间数量。

1.如何将3个表组合在同一个表显示呢？

观察得知，通过“公寓id”可将房间表和公寓表联结；通过“社区id”可将公寓表和社区表联结。

使用哪种联结呢？

从要求的报表结果可以知道，要求保留公寓表中所有公寓。所以用公寓表作为左表，使用左联，这样就可以保留左表（公寓表）中的全部数据。

select a.公寓编号,
       a.是否为自营,
       b.小区名称,b.街道名称
 from 公寓表 as a
 left join 社区表 as b
 on a.小区id=b.小区id
 left join 房间表 c
 on  a.公寓id =c.公寓id;

 

2.如何得到小区地址？

联结三表后得出公寓对应所有房间的状态信息。我们再回到要求报表的格式，报表中的“小区地址”是把“小区名称”和“街道名称”合并在一起显示。

可以用字符串连接函数（concat）得到，也就是在上一步SQL中的select子句中的 b.小区名称,b.街道名称 修改成：

concat(b.小区名称,'-',b.街道名称) as '小区地址

 加入后的SQL如下

select a.公寓编号,
       a.是否为自营,
       b.小区名称,b.街道名称
       concat(b.小区名称,'-',b.街道名称) as '小区地址
 from 公寓表 as a
 left join 社区表 as b
 on a.小区id=b.小区id
 left join 房间表 c
 on  a.公寓id =c.公寓id;

 3.该公寓所有房间数量怎么得出?

“该公寓所有房间数量”翻译成大白话就是，每个公寓的房间数量。

涉及到“每个”要想到《猴子 从零学会SQL》里讲过是要用到分组汇总。按公寓编号分组（group by），按房价id进行计数（count）可得出每个公寓的房间数量。

select a.公寓编号,
       a.是否为自营,
       count(c.房间id) as '该公寓所有房间数量',
       concat(b.小区名称,'-',b.街道名称) as '小区地址'
from 公寓表 a
 left join 社区表 b
  on a.小区id=b.小区id
 left join 房间表 c
  on  a.公寓id =c.公寓id
 group by a.公寓编号

 4.未出租数量如何得出？

房间表中显示房间的状态为“未租”和“已租”。需要分析出每间公寓的房的未出租房间数量。

这种多条件判断的业务问题，要想到用《猴子 从零学会SQL》里讲过的case表达式。

利用case来判断创建新字段，符合条件的数据标记为1，不符合标记为0，然后用sum统计出未出租数量。

select a.公寓编号,
       a.是否为自营,
       sum(case when c.出租状态 = '未租'
                  then 1 
                  else 0 
              end ) as '未出租数量',
       count(c.房间id) as '该公寓所有房间数量',
       concat(b.小区名称,'-',b.街道名称) as '小区地址'
from 公寓表 a
 left join 社区表 b
  on a.小区id=b.小区id
 left join 房间表 c
  on  a.公寓id =c.公寓id
 group by a.公寓编号

 

【总结】

1.当查询结果里涉及到多个表的时候，要想到用多表联结，再根据具体业务场景得出联结类型。

2.涉及到“每个”的时候，要想到用分组汇总。

3.遇到需要对字段信息进行判断且增加新字段的情况，要想到用case 进行条件判断。