鱼C论坛

 找回密码
 立即注册
查看: 1637|回复: 0

MYSQL(面试真题)【滴滴】【多表连接】

[复制链接]
发表于 2020-7-24 18:28:10 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能^_^

您需要 登录 才可以下载或查看,没有账号?立即注册

x
参考链接:https://mp.weixin.qq.com/s?__biz ... =21#wechat_redirect

  1. USE train;
  2. #创建表
  3. DROP TABLE IF EXISTS 司机数据;
  4. CREATE TABLE 司机数据(
  5.         日期 DATE,
  6.         司机id VARCHAR(255),
  7.         城市id VARCHAR(255),
  8.         首次完成订单时间 DATE
  9. );
  10. INSERT INTO 司机数据
  11. VALUES('2020-08-01', '1', '100000', '2016-01-01'),
  12.         ('2020-08-02', '4', '100000', '2016-02-24'),
  13.         ('2020-08-07', '16', '200000', '2016-07-08'),
  14.         ('2020-08-11', '20', '200000', '2020-08-01'),
  15.         ('2020-09-29', '71', '300000', '2020-09-01');


  16. DROP TABLE IF EXISTS 订单数据;
  17. CREATE TABLE 订单数据(
  18.         日期 DATE,
  19.         订单id VARCHAR(255),
  20.         司机id VARCHAR(255),
  21.         乘客id VARCHAR(255),
  22.         产品线id VARCHAR(255),
  23.         流水 INT
  24. );
  25. INSERT INTO 订单数据
  26. VALUES('2020-08-01', '1001', '1', '301', '1', 200),
  27.         ('2020-08-01', '1002', '1', '302', '1', 100),
  28.         ('2020-08-04', '1008', '4', '308', '1', 100),
  29.         ('2020-09-01', '1036', '32', '336', '2', 128),
  30.         ('2020-09-09', '1044', '40', '344', '3', 136);
  31.        

  32. DROP TABLE IF EXISTS 在线时长数据;
  33. CREATE TABLE 在线时长数据(
  34.         日期 DATE,
  35.         司机id VARCHAR(255),
  36.         在线时长 FLOAT(1)
  37. );

  38. INSERT INTO 在线时长数据
  39. VALUES('2020-08-01', '1', 2),
  40.         ('2020-08-01', '2', 1.1),
  41.         ('2020-08-02', '2', 5),
  42.         ('2020-08-02', '3', 3),
  43.         ('2020-08-03', '5', 4);
  44.        
  45. DROP TABLE IF EXISTS 城市匹配数据;
  46. CREATE TABLE 城市匹配数据(
  47.         城市id VARCHAR(255),
  48.         城市名称 VARCHAR(255)
  49. );

  50. INSERT INTO 城市匹配数据
  51. VALUES('100000', '北京'),
  52.         ('200000', '上海'),
  53.         ('300000', '天津');
  54.        
  55. #1.分析出2020年8月各城市每天的司机数、快车订单量和快车流水数据。
  56. #(1)2020年8月各城市每天的司机数(两表连接)
  57. SELECT 日期, COUNT(*) AS 司机数
  58. FROM 城市匹配数据 AS data1 RIGHT JOIN 司机数据 AS data2
  59.         ON data1.城市id = data2.城市id
  60. WHERE 日期 BETWEEN  '2020-08-01' AND '2020-08-31'
  61. GROUP BY 城市名称, 日期;
  62. #(2)快车订单(三表连接)
  63. SELECT data3.城市名称, data2.日期, COUNT(data2.订单id) AS 快车订单数量
  64. FROM 司机数据 data1
  65.         RIGHT JOIN 订单数据 data2
  66.                 ON data1.司机id = data2.司机id
  67.         LEFT JOIN 城市匹配数据 data3
  68.                 ON data1.城市id = data3.城市id
  69. WHERE data2.日期 BETWEEN '2020-08-01' AND '2002-08-31'
  70.         AND data2.产品线id = 3
  71. GROUP BY data3.城市名称, data2.日期;
  72. #(3)2020年8月各城市每天的快车流水(三表连接)
  73. SELECT data3.城市名称, data2.日期, SUM(data2.流水) AS 快车流水
  74. FROM 司机数据 data1
  75.         RIGHT JOIN 订单数据 data2
  76.                 ON data1.司机id = data2.司机id
  77.         LEFT JOIN 城市匹配数据 data3
  78.                 ON data1.城市id = data3.城市id
  79. WHERE data2.日期 BETWEEN '2020-08-01' AND '2002-08-31'
  80.         AND data2.产品线id = 3
  81. GROUP BY data3.城市名称, data2.日期;

  82. #2.提取2020年8月和9月,每个月的北京市新老司机(首单日期在当月为新司机)的司机数、在线时长和TPH(订单量/在线时长)数据。
  83. #新司机
  84. SELECT DATE_FORMAT(日期, '%Y-%m'), COUNT(*)
  85. FROM 司机数据
  86. WHERE (日期 BETWEEN '2020-08-01' AND '2020-08-31'
  87.                 OR 日期 BETWEEN '2020-09-01' AND '2020-09-30')
  88.         AND YEAR(日期) = YEAR(首次完成订单时间)
  89.         AND MONTH(日期) = MONTH(首次完成订单时间)
  90.         AND 城市id = '100000'
  91. GROUP BY DATE_FORMAT(日期, '%Y-%m')
  92. #老司机
  93. SELECT DATE_FORMAT(日期, '%Y-%m'), COUNT(*)
  94. FROM 司机数据
  95. WHERE (日期 BETWEEN '2020-08-01' AND '2020-08-31'
  96.                 OR 日期 BETWEEN '2020-09-01' AND '2020-09-30')
  97.         AND (YEAR(日期) != YEAR(首次完成订单时间)
  98.                 OR MONTH(日期) != MONTH(首次完成订单时间))
  99.         AND 城市id = '100000'
  100. GROUP BY DATE_FORMAT(日期, '%Y-%m')
  101. #老司机在线时长
  102. DROP TABLE IF EXISTS temp1;
  103. CREATE TABLE temp1
  104. SELECT data1.司机id, DATE_FORMAT(data1.日期, '%Y-%m') AS 日期,  IF(ISNULL(SUM(在线时长)), 0, SUM(在线时长))  AS 在线总时长
  105. FROM((
  106.         SELECT *
  107.         FROM 司机数据
  108.         WHERE (日期 BETWEEN '2020-08-01' AND '2020-08-31'
  109.                         OR 日期 BETWEEN '2020-09-01' AND '2020-09-30')
  110.                 AND (YEAR(日期) != YEAR(首次完成订单时间)
  111.                         OR MONTH(日期) != MONTH(首次完成订单时间))
  112.                 AND 城市id = '100000') AS data1 LEFT JOIN 在线时长数据 AS data2
  113.                         ON data1.司机id = data2.司机id)
  114. GROUP BY data1.司机id, DATE_FORMAT(data1.日期, '%Y-%m');
  115. #在线时长和TPH(订单量/在线时长)
  116. DROP TABLE IF EXISTS temp2;
  117. CREATE TABLE temp2
  118. SELECT data1.司机id, DATE_FORMAT(data1.日期, '%Y-%m') AS 日期,  COUNT(data2.订单id)  AS 总订单数
  119. FROM((
  120.         SELECT *
  121.         FROM 司机数据
  122.         WHERE (日期 BETWEEN '2020-08-01' AND '2020-08-31'
  123.                         OR 日期 BETWEEN '2020-09-01' AND '2020-09-30')
  124.                 AND (YEAR(日期) != YEAR(首次完成订单时间)
  125.                         OR MONTH(日期) != MONTH(首次完成订单时间))
  126.                 AND 城市id = '100000') AS data1 LEFT JOIN 订单数据 AS data2
  127.                         ON data1.司机id = data2.司机id)
  128. GROUP BY data1.司机id, DATE_FORMAT(data1.日期, '%Y-%m');

  129. SELECT temp1.司机id, temp1.日期, IF(ISNULL(总订单数 / 在线总时长), 0, 总订单数/在线总时长) AS TPH
  130. FROM temp1 LEFT JOIN temp2 ON temp1.司机id = temp2.司机id  
  131.         AND temp1.日期 = temp2.日期

  132. #3. 分别提取司机数大于20,司机总在线时长大于2小时,订单量大于1,乘客数大于1的城市名称数据。*****
  133. SELECT a.司机id, a.乘客数, c.城市名称
  134. FROM(
  135.         SELECT 司机id, COUNT(乘客id) AS 乘客数
  136.         FROM 订单数据
  137.         GROUP BY 司机id
  138.         HAVING COUNT(乘客id)  > 10
  139. ) AS a
  140.         LEFT JOIN (
  141.                 SELECT 司机id, 城市id
  142.                 FROM 司机数据)AS b
  143.                         ON a.司机id = b.司机id
  144.         JOIN 城市匹配数据 AS c
  145.                 ON b.城市id = c.城市id
复制代码

本帖被以下淘专辑推荐:

想知道小甲鱼最近在做啥?请访问 -> ilovefishc.com
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

小黑屋|手机版|Archiver|鱼C工作室 ( 粤ICP备18085999号-1 | 粤公网安备 44051102000585号)

GMT+8, 2024-3-28 23:55

Powered by Discuz! X3.4

© 2001-2023 Discuz! Team.

快速回复 返回顶部 返回列表