这是一个创建于 1113 天前的主题，其中的信息可能已经有所发展或是发生改变。
面试中遇到一道简单的 sql 题，小弟也能写出来，但写的非常乱，嵌套了很多子查询，我感觉有更美妙的写法，请教各位
题目
我的解答
SELECT a.id, a.name, a.group_id, b.name group_name, c.sum_goods_sales_volume, d.sum_group_sales_volume FROM goods a, goods_group b, (select sum(sales_volume) sum_goods_sales_volume, goods_id from goods_sales_record group by goods_id order by sum_goods_sales_volume desc limit 3) c, (select sum(sales_volume) sum_group_sales_volume, bb.group_id FROM goods_sales_record aa JOIN goods bb WHERE aa.goods_id = bb.id GROUP BY bb.group_id) d WHERE a.group_id = b.id AND a.id = c.goods_id AND a.group_id = d.group_id order by sum_group_sales_volume desc, sum_goods_sales_volume desc; 附上表结构 及 数据 方便大佬们使用
goods
/* Navicat Premium Data Transfer Source Server : 本地 MySql Source Server Type : MySQL Source Server Version : 80028 Source Host : localhost:3306 Source Schema : transaction Target Server Type : MySQL Target Server Version : 80028 File Encoding : 65001 Date: 17/11/2022 12:43:34 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for goods -- ---------------------------- DROP TABLE IF EXISTS `goods`; CREATE TABLE `goods` ( `id` int NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `group_id` int NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of goods -- ---------------------------- INSERT INTO `goods` VALUES (1, '苹果手机', 1); INSERT INTO `goods` VALUES (2, '三星手机', 1); INSERT INTO `goods` VALUES (3, '联想电脑', 2); INSERT INTO `goods` VALUES (4, '华为手机', 1); INSERT INTO `goods` VALUES (5, '华硕电脑', 2); INSERT INTO `goods` VALUES (6, 'IKBC', 3); SET FOREIGN_KEY_CHECKS = 1; goods_group
/* Navicat Premium Data Transfer Source Server : 本地 MySql Source Server Type : MySQL Source Server Version : 80028 Source Host : localhost:3306 Source Schema : transaction Target Server Type : MySQL Target Server Version : 80028 File Encoding : 65001 Date: 17/11/2022 12:43:41 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for goods_group -- ---------------------------- DROP TABLE IF EXISTS `goods_group`; CREATE TABLE `goods_group` ( `id` int NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of goods_group -- ---------------------------- INSERT INTO `goods_group` VALUES (1, '手机'); INSERT INTO `goods_group` VALUES (2, '电脑'); INSERT INTO `goods_group` VALUES (3, '键盘'); SET FOREIGN_KEY_CHECKS = 1; goods_sales_record
/* Navicat Premium Data Transfer Source Server : 本地 MySql Source Server Type : MySQL Source Server Version : 80028 Source Host : localhost:3306 Source Schema : transaction Target Server Type : MySQL Target Server Version : 80028 File Encoding : 65001 Date: 17/11/2022 12:43:26 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for goods_sales_record -- ---------------------------- DROP TABLE IF EXISTS `goods_sales_record`; CREATE TABLE `goods_sales_record` ( `id` int NOT NULL, `goods_id` int NULL DEFAULT NULL, `sales_volume` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of goods_sales_record -- ---------------------------- INSERT INTO `goods_sales_record` VALUES (1, 1, '50'); INSERT INTO `goods_sales_record` VALUES (2, 2, '30'); INSERT INTO `goods_sales_record` VALUES (3, 3, '88'); INSERT INTO `goods_sales_record` VALUES (4, 4, '88'); INSERT INTO `goods_sales_record` VALUES (5, 5, '444'); INSERT INTO `goods_sales_record` VALUES (6, 6, '34'); SET FOREIGN_KEY_CHECKS = 1;  | 1 zhangxh1023 2022-11-17 13:40:44 +08:00 这种东西感觉没个半小时调试加搜索我感觉我写不出来♂ |
 | 2 fengjianxinghun 2022-11-17 13:51:04 +08:00  11 这题没有 copilot 做不出来 |
 | 3 wangnimabenma 2022-11-17 13:54:35 +08:00 1 抖个机灵，我会分开查不会写复杂的子查询或者其他。原因我可以和面试官好好说说 |
 | 4 iseki 2022-11-17 14:04:42 +08:00 用 with 拆开会更好一点 |
 | 5 qiyong OP 忘说了 是第二道题 |
 | 6 zhzy0077 2022-11-17 14:27:47 +08:00 第一问第二问不是连着的吗 SELECT TOP 3 A.name, C.sales_volume, B.name FROM goods A JOIN goods_group B ON A.group_id = B.id JOIN goods_sales_record C ON A.id = C.goods_id ORDER BY sales_volume DESC SELECT D.good_name, D.sales_volume, D.group_name, E.sum_sales FROM ( SELECT TOP 3 A.name AS good_name, C.sales_volume, B.name AS group_name, B.id AS group_id FROM goods A JOIN goods_group B ON A.group_id = B.id JOIN goods_sales_record C ON A.id = C.goods_id ORDER BY sales_volume DESC ) D JOIN ( SELECT B.id AS id, SUM(C.sales_volume) AS sum_sales FROM goods A JOIN goods_group B ON A.group_id = B.id JOIN goods_sales_record C ON A.id = C.goods_id GROUP BY B.id ) E ON D.group_id = E.id ORDER BY sum_sales DESC, sales_volume DESC |
 | 7 CRVV 2022-11-17 14:46:13 +08:00 2 首先把销量的类型改成数字， `sales_volume` INT 最后都需要套一层子查询来重新排序，就不写了。 这三种写法都是用 ORDER BY volume DESC LIMIT 3 来选出前 3 ，还可以用 rank <= 3 来选前 3 的，如果有重复的会得到不同结果 都可以在最新的 MySQL 上执行 SELECT goods.name, goods_sales_record.sales_volume, goods_group.name AS group_name, t.group_volume FROM goods INNER JOIN goods_sales_record ON goods.id = goods_sales_record.goods_id INNER JOIN goods_group ON goods.group_id = goods_group.id INNER JOIN (SELECT goods_group.id, sum(goods_sales_record.sales_volume) AS group_volume FROM goods_group INNER JOIN goods ON goods.group_id = goods_group.id INNER JOIN goods_sales_record on goods.id = goods_ales_record.goods_id GROUP BY goods_group.id) AS t ON t.id = goods_group.id ORDER BY 2 DESC limit 3; SELECT goods.name, goods_sales_record.sales_volume, goods_group.name AS group_name, t.group_volume FROM goods INNER JOIN goods_sales_record ON goods.id = goods_sales_record.goods_id INNER JOIN goods_group ON goods.group_id = goods_group.id CROSS JOIN LATERAL (SELECT sum(goods_sales_record.sales_volume) AS group_volume FROM goods INNER JOIN goods_sales_record on goods.id = goods_sales_record.goods_id WHERE group_id = goods_group.id) AS t ORDER BY 2 DESC limit 3; SELECT goods.name, goods_sales_record.sales_volume, goods_group.name AS group_name, sum(sales_volume) OVER (PARTITION BY goods_group.id) AS group_volume FROM goods INNER JOIN goods_sales_record ON goods.id = goods_sales_record.goods_id INNER JOIN goods_group ON goods.group_id = goods_group.id ORDER BY 2 DESC LIMIT 3; |
 | 8 DinnyXu 2022-11-17 14:56:37 +08:00 这个题用 SQL 是能做出来，但是对于实际业务来说 SQL 不会写成这种 join 和 嵌套，商品和商品分组查出来，在单独去统计各个商品的销量以及商品分组总销量。要是我面试就可以跟面试官扯皮一下，这种题拿来面试是侮辱智商的。直接喷他 |
| 9 CRVV 2022-11-17 15:08:19 +08:00 |
 | 10 jhb 2022-11-17 15:08:28 +08:00 这应该是一道考窗口函数的题目,不用写复杂的分组语句 |
 | 11 qzwmjv 2022-11-17 15:43:56 +08:00 这不写嵌套 sql 就完事？除非有数据倾斜需要解决 |
 | 12 potatowish 2022-11-17 16:13:30 +08:00 1 select t0.* from ( select t.goods_name, t.sales_volume sv, t.group_name, sum(t.sales_volume) OVER (PARTITION BY t.group_name)gsv from ( select g.id as goods_id, g.name as goods_name, gg.name as group_name, gsr.sales_volume from goods_sales_record gsr left join goods_group gg on gg.id = g.group_id )t order by sv desc limit 3 )t0 order by t0.gsv desc, t0.sv desc; |
 | 13 raysonlu 2022-11-17 16:52:32 +08:00 @CRVV 这类的查询业务，数据量到达亿级的话，是把整个查询业务都放在 sql 呢（比如这种面试题需要一句 sql 语句得出最终结果，或窗口函数），还是多次 sql 查询分步处理好？（比如 php 或 java 先查出销量最好三个，再根据分组 id 查分组销量情况，再拼凑） |
 | 14 ponder09 2022-11-17 16:56:52 +08:00 |
| 17 CRVV 2022-11-17 17:48:34 +08:00 @raysonlu 如果能分步处理当然分步处理是很多人首选的方案，因为代码比较好懂，会写复杂 SQL 的人没那么多。 但就这个题来说，如果商品有 1 亿个，销量表是分小时的，要查所有商品，按近一个月的销量倒序排列，要能搜索能翻页，还是那种能直接跳到第 10000 页的设计。这样的不太可能分步来处理吧，每一个中间步骤的结果都很大。实际的需求通常都比这个复杂。 写一个大 SQL 可能直接就把数据库弄死了，当然也不行。 所以我上面说 BigQuery ，这东西就是干这个事用的，应该算是解决这种问题的方案之一。 |
 | 18 sadfQED2 2022-11-17 17:53:34 +08:00 via Android |
 | 19 no13bus 2022-11-17 17:59:35 +08:00 有的时候业务量没那么大的时候，假设想差一些临时性的数据，逻辑比较麻烦，这个时候写 sql 是很正常的做法。 |
 | 20 m2276699 2022-11-17 18:39:08 +08:00 cte 、rank |
 | 21 asmile1993 2022-11-17 19:33:40 +08:00 with goods_sales as( -- 获取每种商品的总销售额 select distinct g.id as goods_id, g.name as goods_name, g.group_id, gg.name as group_name, sum(gsr.sales_volume) over(partition by g.id, g.name, g.group_id, gg.name) sum_goods_sales_volume, sum(gsr.sales_volume) over(partition by g.group_id, gg.name) sum_group_sales_volume from goods g inner join goods_group gg on g.group_id= gg.id inner join goods_sales_record gsr on g.id= gsr.goods_id order by sum_goods_sales_volume desc -- 以商品的总销售倒序排列，并取前三名 limit 3 ) select goods_id, goods_name, group_id, group_name, sum_goods_sales_volume, -- 每种商品的总销售额 sum_group_sales_volume -- 每种分类的总销售额 from goods_sales order by sum_group_sales_volume desc, sum_goods_sales_volume desc -- 以每种分类的总销售额、商品的总销售倒序排列 |
 | 22 wxf666 2022-11-17 20:09:01 +08:00 @potatowish wangxin3 `goods_sales_record` 表应该允许多次售出某个商品吧 因为楼主 @qiyong 也用了 `FROM goods_sales_record GROUP BY goods_id` 我试了下，`goods_sales_record` 表加个 `(7, 1, '100')`，#12 #14 结果就不对了 试着拿 `SQLite` 写了下： *（ V 站排版原因，行首有全角空格，记得删除）* ```sql WITH goods(id, name, group_id) AS ( VALUES (1, '苹果手机', 1), (2, '三星手机', 1), (3, '联想电脑', 2), (4, '华为手机', 1), (5, '华硕电脑', 2), (6, 'IKBC', 3) ), goods_group(id, name) AS ( VALUES (1, '手机'), (2, '电脑'), (3, '键盘') ), goods_sales_record(id, goods_id, sales_volume) AS ( VALUES (1, 1, '50'), (2, 2, '30'), (3, 3, '88'), (4, 4, '88'), (5, 5, '444'), (6, 6, '34') ), goods_sales(goods_id, total_sales) AS ( SELECT goods_id, SUM(sales_volume) FROM goods_sales_record GROUP BY 1 ), top3 AS ( SELECT * FROM goods_sales ORDER BY total_sales DESC LIMIT 3 ) SELECT g1.name 商品名, gg.name 商品所属分组名, t3.total_sales 商品销量, SUM(gs.total_sales) 分组内所有商品总销量 FROM top3 t3 JOIN goods g1 ON g1.id = t3.goods_id JOIN goods g2 USING(group_id) JOIN goods_group gg ON gg.id = g1.group_id JOIN goods_sales gs ON g2.id = gs.goods_id GROUP BY g1.id ORDER BY 分组内所有商品总销量 DESC, 商品销量 DESC; ``` |
 | 23 Goooooos 2022-11-17 21:22:23 +08:00 在 V2 ，笔试都是政治不正确的 |
 | 24 rabbbit 2022-11-17 22:30:13 +08:00 问个问题，真实业务里允许像查商品销量写 from a, b, c 这种写法吗？ 不是会导致查询结果过多吗? |
 | 25 c6h6benzene 2022-11-17 22:37:49 +08:00 via iPhone 大概…开窗函数可以解决？ |
| 26 iseki 2022-11-17 23:34:26 +08:00 @DinnyXu 如果性能敏感，以至于需要给其中的部分子查询增加额外的缓存，那才会选择拆开；否则这种简单查询直接一个 SQL 是最优解，不管是性能上还是功能上。 |
 | 27 qinrui 2022-11-17 23:41:27 +08:00 via iPhone 应该用开窗吧 |
| 28 iseki 2022-11-17 23:52:45 +08:00 个人不倾向这里用开窗，题目中感觉并没有明确提出开窗统计的需求，更多的其实是就是把多个统计维度的数据攒到一块了，那 with 几下感觉更好一点 |
| 29 DinnyXu 2022-11-18 00:21:30 +08:00 |
| 30 iseki 2022-11-18 01:45:11 +08:00 via Android @DinnyXu 这种需要全表扫来扫去的 SQL 一般都不会经常跑，也就是个定时任务，一个小时刷一遍排行榜之类的 |
 | 31 swcat 2022-11-18 09:48:16 +08:00 销量类型得改为 int 类型, 不然排序回出错 select sum(sr.sales_volume) over sv '分组总销量', sr.sales_volume '商品销量', sr.goods_id as '商品 id', g.name '商品名', gg.name '分组名' from goods_sales_record sr join goods g on g.id = sr.goods_id join goods_group gg on g.group_id = gg.id window sv as (partition by g.group_id ) order by 1 desc, 2 desc limit 3; |
| 32 raysonlu 2022-11-18 10:24:53 +08:00 @CRVV 如果按你说的场景并且只能局限于此，的确只能 BigQuery 了。但我还是比较好奇在实际场景中，遇到这种 BigQuery 是几乎开放式的（多用户或多进程可同时进行请求），这时候还能这么搞？（我实际项目中反而遇到很多这种用户可能会频繁访问 BigQuery 的情景） 我理解中的“分步处理”不是为了“代码比较好懂”而是为了“不让 mysql 高负荷运行复杂查询“，维护简单的 sql 查询比较容易（比如简单的索引组合，分表之类），但 BigQuery 的查询维护就变得很难把控。 在结合一些实际业务情况，我或者会考虑这些： 1 、针对查询业务做一个汇总表，并动态更新，这相当于持久化的热缓存了； 2 、尽量减少搜素条件需求，或针对搜索条件对分步查询进行调整； 3 、慎重考虑分页。分析 BigQuery 过程其实很多情况下（特别是排序），sql 已经把相关数据全部扫描了，然后我们只取部分数据（估计考虑数据传输压力到中间层），我觉得这种情况不如“不分页查询”。数据库和中间层之间数据传输压力（如 in 查询的数量限制也算是），可以尝试在分步处理的各个步骤当中再进行”分步查询“，中间层和客户端层之间的传输压力，那就甩锅给带宽吧（ nginx 已经有数据压缩传输处理了，不想管了） 总结我对 BigQuery 的使用场景是：业务量不大，且让业务快速到位。 以上，个人鄙见 |
 | 33 nig001 2022-11-18 11:59:47 +08:00 SELECT a.id,a.name,c.sales_volume,gb.group_name,gb.g_sales_volumes FROM goods a LEFT JOIN goods_sales_record c ON a.id = c.goods_id LEFT JOIN (SELECT b.id as group_id,b.name AS group_name,sum(c.sales_volume) AS g_sales_volumes FROM goods_sales_record c LEFT JOIN goods a ON a.id=c.goods_id LEFT JOIN goods_group b ON a.group_id = b.id GROUP BY b.id) gb ON a.group_id = gb.group_id ORDER BY gb.g_sales_volumes,c.sales_volume DESC LIMIT 3 |
| 34 CRVV 2022-11-18 14:01:25 +08:00 @raysonlu “不让 mysql 高负荷运行复杂查询” 这应该是一个对关系型数据库的误解，来自于 MySQL 的 planner 太弱。 要得到一个查询的结果，不论查询是不是分步的，总的工作量一定有下限。在 planner 足够好的情况下，一定是分步查询的工作量大，一条大 SQL 的工作量小（因为不需要把可能很大的中间结果传回来），所以写一条大 SQL 才是节约数据库的做法。 在 MySQL 上，“planner 足够好” 通常不成立，这事就反过来了，分步查才是工作小的做法，所以才有分开写简单 SQL 的习惯。换成 PostgreSQL 就不是那么回事了。 至于其它的很多都是需求问题，很多时候产品经理的需求就要分页要搜索，总不能直接说这东西做不了吧。 |
 | 35 weizhen199 2022-11-18 16:13:43 +08:00 实际上 oracle 的 cbo 也就这样，所以该分几步还是分吧 |
 | 36 Pandaaaa906 2022-11-19 10:34:30 +08:00 via Android 没限制数据库吧？ mysql 太渣~用 postgres 了 select * from ( select g.name, t.sales_volume_sum, gg.name, sum(t.sales_volume_sum) over(partition by g.group_id) total_sum from ( select goods_id, sum(gsr.sales_volume) sales_volume_sum from goods_sales_record gsr group by gsr.goods_id ) t left join goods g on t.goods_id = g.id left join goods_group gg on g.group_id = gg.id order by t.sales_volume_sum desc limit 3 ) tmp order by total_sum desc, sales_volume_sum desc |
 | 37 nuanshen 2022-11-19 14:44:01 +08:00 这简单啊，不过销量字段类型先改成 int 吧， 求组内总销量用 sum(sales_volume) over(partition by group_id) ， 销量排序 rank()over(order by sales_volume desc) # mysql8 select t.goods_name `商品名`, t.sales_volume `商品销量`, t.group_name `商品组名`, t.group_sales_volume `商品组总销量` from ( select t.goods_id, t.sales_volume, g.name goods_name, gp.name group_name, sum(t.sales_volume)over(partition by g.group_id) group_sales_volume, rank() over (order by t.sales_volume) sales_rank from goods_sales_record t left join goods g on g.id = t.goods_id left join goods_group gp on gp.id = g.group_id ) t where t.sales_rank<=3 order by t.group_sales_volume desc,t.sales_volume desc; |
Select Language