遇到个 sql 难题,求助 sql 大佬

330 天前
 Worldispow

1.表结构:前几列为 id ,name 之类的字段,v1-v100 为数值类型

id name v1 v2 v3 v4
1 a 1 2 3 10
2 b 5 2 3 10
3 c 5 3 3 10
4 d 1 2 3 10

2.需求:求 v ( n )列的和,并找出 v ( n )的和最大时的列名和内容。如示例数据

sum ( v1 )=12
sum ( v2 )=9
sum ( v3 )=12
sum ( v4 )=40

显然 v4 列的和最大,那么结果就是 v4 和对应的每一行的值.

id name v4
1 a 10
2 b 10
3 c 10
4 d 10

3.其他要求:只能用 sql ,环境为 mysql5.7.

1828 次点击
所在节点    数据库
14 条回复
googlefans
330 天前
WITH ColumnSums AS (
SELECT 'v1' AS column_name, SUM(v1) AS total_sum FROM your_table
UNION ALL
SELECT 'v2', SUM(v2) FROM your_table
UNION ALL
SELECT 'v3', SUM(v3) FROM your_table
-- 重复这个模式直到 v100
UNION ALL
SELECT 'v100', SUM(v100) FROM your_table
),
MaxSum AS (
SELECT column_name, total_sum
FROM ColumnSums
WHERE total_sum = (SELECT MAX(total_sum) FROM ColumnSums)
)
SELECT MaxSum.column_name, your_table.*
FROM MaxSum
CROSS JOIN your_table;

这个查询首先在 ColumnSums 中为每一列计算总和,然后在 MaxSum 中找出最大的总和及其对应的列名。最后,通过与原始表的交叉连接( CROSS JOIN ),你可以得到和最大的那一列的名字以及对应的每一行的值。

这个查询在列数非常多的情况下可能效率不高。如果你的表结构允许,考虑使用更动态的方法来处理这种类型的查询,例如编写一个存储过程。
Worldispow
330 天前
@googlefans 大佬。5.7 好像没有 with as 的语法。。。
googlefans
330 天前
你可以通过创建一个临时表来存储每列的总和,然后从这个临时表中查询最大值。
fluter
330 天前
大体思路是一楼的,你可以建临时表啊、create table tmp_001 as ....,这个表主要是算 v1-v100 每列的和,然后再用 tmp_001 中求出 max 和最大的这列,得到临时表 tmp_002 。后面看你是跑一次,还是要每天自动跑。知道 v 几这列是最大的。然后你的数据加一行,全是 v 几的值。再用你的表筛选 v 几的这列值等于这个 v 几的值。具体的没操作,不知道能不能实现。
stone666
330 天前
为什么非得一条 sql 就查出来呢
Worldispow
330 天前
@googlefans
@fluter
@stone666
我不是开发人员,是做业务分析用的。每次分析的内容不是很固定,因为是生产环境数据库只有只读权限(为了不必要的麻烦,读写权限的账号我一般不登录),所以尽量想减少数据库的操作,看能不能一条 sql 直接出来。
cleanery
330 天前
SELECT
id,
NAME,
(case (SELECT column_name FROM( SELECT 'v1' AS column_name, SUM(v1) AS sum_value FROM your_table UNION ALL SELECT 'v2' AS column_name, SUM(v2) AS sum_value FROM your_table UNION ALL SELECT 'v3' AS column_name, SUM(v3) AS sum_value FROM your_table UNION ALL SELECT 'v4' AS column_name, SUM(v4) AS sum_value FROM your_table) AS subquery ORDER BY sum_value DESC LIMIT 1) when 'v1' then v1 when 'v2' then v2 when 'v3' then v3 when 'v4' then v4 end) v
FROM
your_table


就这样了, 动态列名真的我不会
cleanery
330 天前
据我所知, 你要么创建临时表, mysql 应该是没有动态别名的方法的
ugpu
330 天前
SELECT 'v1' as column_name, SUM(v1) as total_sum FROM your_table
UNION ALL
SELECT 'v2', SUM(v2) FROM your_table
UNION ALL
SELECT 'v3', SUM(v3) FROM your_table
UNION ALL
SELECT 'v4', SUM(v4) FROM your_table
ORDER BY total_sum DESC
LIMIT 1;


SELECT id, name, v4 FROM your_table;
JohnYehyo
330 天前
order by limit 的话不能避免 v(n)列的和相同的情况
Worldispow
330 天前
@ugpu
@JohnYehyo
感谢大佬提供的思路,差不多这样就可以了,大致思路我知道了。
catamaran
330 天前
提供一个其他的思路,就是学习成本有点高。在 clickhouse 中,可以把 mysql 的一个表映射成本地的一个表,这样就相当于你拿到了写(其他表)的权限,可以进行复杂的操作。还有一个东西你看看对你有用没,搜索“集算器”。
Worldispow
330 天前
@catamaran ck 太不灵活了,我还有其他档案数据要关联并清洗。
个人感觉 ck 的统计函数还不如 oracle 强大,特别复杂的逻辑我就直接把数据扔 oracle 里面跑了。
catamaran
330 天前
@Worldispow clickhouse 强大的地方是海量数据的查询,关联是弱项。几十亿的数据轻松搞定。关系型数据库,oracle 还是大佬。

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/1004112

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX