V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
Worldispow
V2EX  ›  数据库

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

  •  
  •   Worldispow · 316 天前 · 1817 次点击
    这是一个创建于 316 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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.

    14 条回复    2023-12-28 18:26:53 +08:00
    googlefans
        1
    googlefans  
       316 天前   ❤️ 1
    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
        2
    Worldispow  
    OP
       316 天前
    @googlefans 大佬。5.7 好像没有 with as 的语法。。。
    googlefans
        3
    googlefans  
       316 天前   ❤️ 1
    你可以通过创建一个临时表来存储每列的总和,然后从这个临时表中查询最大值。
    fluter
        4
    fluter  
       316 天前   ❤️ 1
    大体思路是一楼的,你可以建临时表啊、create table tmp_001 as ....,这个表主要是算 v1-v100 每列的和,然后再用 tmp_001 中求出 max 和最大的这列,得到临时表 tmp_002 。后面看你是跑一次,还是要每天自动跑。知道 v 几这列是最大的。然后你的数据加一行,全是 v 几的值。再用你的表筛选 v 几的这列值等于这个 v 几的值。具体的没操作,不知道能不能实现。
    stone666
        5
    stone666  
       316 天前   ❤️ 1
    为什么非得一条 sql 就查出来呢
    Worldispow
        6
    Worldispow  
    OP
       316 天前
    @googlefans
    @fluter
    @stone666
    我不是开发人员,是做业务分析用的。每次分析的内容不是很固定,因为是生产环境数据库只有只读权限(为了不必要的麻烦,读写权限的账号我一般不登录),所以尽量想减少数据库的操作,看能不能一条 sql 直接出来。
    cleanery
        7
    cleanery  
       316 天前   ❤️ 1
    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
        8
    cleanery  
       316 天前   ❤️ 1
    据我所知, 你要么创建临时表, mysql 应该是没有动态别名的方法的
    ugpu
        9
    ugpu  
       316 天前
    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
        10
    JohnYehyo  
       316 天前
    order by limit 的话不能避免 v(n)列的和相同的情况
    Worldispow
        11
    Worldispow  
    OP
       316 天前
    @ugpu
    @JohnYehyo
    感谢大佬提供的思路,差不多这样就可以了,大致思路我知道了。
    catamaran
        12
    catamaran  
       316 天前
    提供一个其他的思路,就是学习成本有点高。在 clickhouse 中,可以把 mysql 的一个表映射成本地的一个表,这样就相当于你拿到了写(其他表)的权限,可以进行复杂的操作。还有一个东西你看看对你有用没,搜索“集算器”。
    Worldispow
        13
    Worldispow  
    OP
       316 天前
    @catamaran ck 太不灵活了,我还有其他档案数据要关联并清洗。
    个人感觉 ck 的统计函数还不如 oracle 强大,特别复杂的逻辑我就直接把数据扔 oracle 里面跑了。
    catamaran
        14
    catamaran  
       316 天前
    @Worldispow clickhouse 强大的地方是海量数据的查询,关联是弱项。几十亿的数据轻松搞定。关系型数据库,oracle 还是大佬。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2690 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 12:20 · PVG 20:20 · LAX 04:20 · JFK 07:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.