V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
jhsea3do
V2EX  ›  问与答

请教各位老大一个 SQL 逐行统计问题,感觉自己做不出来了

  •  
  •   jhsea3do · 2019-03-29 15:57:05 +08:00 · 1461 次点击
    这是一个创建于 1827 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有一个产品统计表 d1, 每总产品的 total 数量为该类型产品 型号 part1 数量加型号 part2 数量 pid,total,part1,part2 1,10,5,5 2,13,7,6

    还有一个产品 part1 的产出表 f1,记录每个批次的量产,批号为 bid,part1 为该批次的产量 比如最近 5 个批次的产量记录如下 bid,pid,part1 1,1,3 2,1,6 3,1,9 4,2,1 5,2,2

    现在每隔一段时间做一次统计,生成一个账目表 f2, 要求记录产品每个批次的数量变化, 比如统计最近 5 个批次的产量, 期望插入如下记录 bid,pid,qty,total 1,1,3,13 2,1,6,19 3,1,9,28 4,2,1,14 5,2,2,16

    目前每次统计是纯 sql 来调度的,优先考虑不用存储过程,

    我本来用 join 来插入的,但发现搞不定 f2.total 那一列,

    付 初始化语句, 数据库是 mysql57, 如果 mysql 不好做, 也请让我知道什么库能支持这种需求

    还请高手赐教

    drop table d1;
    create table d1 (
       pid     int primary key,
       total   int,
       part1  int,
       part2  int
    );
    
    drop table f1;
    create table f1 (
       bid       int   auto_increment primary key,
       pid     int,
       part1  int
    );
    
    drop table f2;
    create table f2 (
       tid       int   auto_increment primary key,
       bid     int,
       pid     int,
       qty     int,
       total   int
    );
    
    insert into d1 values (1, 10, 5, 5);
    insert into d1 values (2, 13, 7, 6);
    
    insert into f1 (pid, part1) values (1, 3);
    insert into f1 (pid, part1) values (1, 6);
    insert into f1 (pid, part1) values (1, 9);
    insert into f1 (pid, part1) values (2, 1);
    insert into f1 (pid, part1) values (2, 2);
    
    
    10 条回复    2019-04-04 16:35:25 +08:00
    jhsea3do
        1
    jhsea3do  
    OP
       2019-03-29 16:02:09 +08:00
    排版有点问题,

    ```sql
    insert into f2 (bid, pid, qty, total) values (1,1,3,13);
    insert into f2 (bid, pid, qty, total) values (2,1,6,19);
    insert into f2 (bid, pid, qty, total) values (3,1,9,28);
    insert into f2 (bid, pid, qty, total) values (4,2,1,14);
    insert into f2 (bid, pid, qty, total) values (5,2,2,16);
    ```


    mysql> select * from d1;
    +-----+-------+-------+-------+
    | pid | total | part1 | part2 |
    +-----+-------+-------+-------+
    | 1 | 10 | 5 | 5 |
    | 2 | 13 | 7 | 6 |
    +-----+-------+-------+-------+
    2 rows in set (0.00 sec)

    mysql> select * from f1;
    +-----+------+-------+
    | bid | pid | part1 |
    +-----+------+-------+
    | 1 | 1 | 3 |
    | 2 | 1 | 6 |
    | 3 | 1 | 9 |
    | 4 | 2 | 1 |
    | 5 | 2 | 2 |
    +-----+------+-------+
    5 rows in set (0.00 sec)

    mysql> select * from f2;
    +-----+------+------+------+-------+
    | tid | bid | pid | qty | total |
    +-----+------+------+------+-------+
    | 1 | 1 | 1 | 3 | 13 |
    | 2 | 2 | 1 | 6 | 19 |
    | 3 | 3 | 1 | 9 | 28 |
    | 4 | 4 | 2 | 1 | 14 |
    | 5 | 5 | 2 | 2 | 16 |
    +-----+------+------+------+-------+
    5 rows in set (0.00 sec)
    5G
        2
    5G  
       2019-03-29 16:18:37 +08:00
    我本身是很乐意给人做 SQL 的,但看见你的表述,我真的不想做阅读理解,麻烦你给你的文字加上标点符号,再告诉我你在文字中使用空格是什么意思。
    jasonyang9
        3
    jasonyang9  
       2019-03-29 16:21:52 +08:00
    f2 关系(表)中的这个 Total 属性(字段、列)与主键没有函数依赖的吧?
    jhsea3do
        4
    jhsea3do  
    OP
       2019-03-29 16:28:20 +08:00
    非常抱歉,很少在 V2EX 上发帖,我的排版是不够友好,以这个为准把

    有一个产品统计表 d1, 每总产品的 total 数量为该类型产品 型号 part1 数量加型号 part2 数量
    +-----+-------+-------+-------+
    | pid | total | part1 | part2 |
    +-----+-------+-------+-------+
    | 1 | 10 | 5 | 5 |
    | 2 | 13 | 7 | 6 |
    +-----+-------+-------+-------+

    可以如下句子初始化 d1 表

    create table d1 (
    pid int primary key,
    total int,
    part1 int,
    part2 int
    );

    insert into d1 values (1, 10, 5, 5);
    insert into d1 values (2, 13, 7, 6);


    还有一个产出表 f1,记录每个批次 part1 的量产, bid 字段为批号 , part1 字段 为该批次的产量
    比如最近 5 个批次的产量记录如下

    +-----+------+-------+
    | bid | pid | part1 |
    +-----+------+-------+
    | 1 | 1 | 3 |
    | 2 | 1 | 6 |
    | 3 | 1 | 9 |
    | 4 | 2 | 1 |
    | 5 | 2 | 2 |
    +-----+------+-------+

    可以如下句子初始化 f1 表
    create table f1 (
    bid int auto_increment primary key,
    pid int,
    part1 int
    );


    insert into f1 (pid, part1) values (1, 3);
    insert into f1 (pid, part1) values (1, 6);
    insert into f1 (pid, part1) values (1, 9);
    insert into f1 (pid, part1) values (2, 1);
    insert into f1 (pid, part1) values (2, 2);


    现在每隔一段时间做一次统计,生成一个账目表 f2, 要求记录产品每个批次的数量变化,
    比如某一次统计要统计 f1 中 5 个批次的产量, 期望插入如下记录

    +-----+------+------+------+-------+
    | tid | bid | pid | qty | total |
    +-----+------+------+------+-------+
    | 1 | 1 | 1 | 3 | 13 |
    | 2 | 2 | 1 | 6 | 19 |
    | 3 | 3 | 1 | 9 | 28 |
    | 4 | 4 | 2 | 1 | 14 |
    | 5 | 5 | 2 | 2 | 16 |
    +-----+------+------+------+-------+


    可以如下句子初始化 f2 表
    create table f2 (
    tid int auto_increment primary key,
    bid int,
    pid int,
    qty int,
    total int
    );


    注意以下的插入语句其实是我期望统计生成的数据

    insert into f2 (bid, pid, qty, total) values (1,1,3,13);
    insert into f2 (bid, pid, qty, total) values (2,1,6,19);
    insert into f2 (bid, pid, qty, total) values (3,1,9,28);
    insert into f2 (bid, pid, qty, total) values (4,2,1,14);
    insert into f2 (bid, pid, qty, total) values (5,2,2,16);

    目前每次统计是纯 sql 来调度的,优先考虑不用存储过程,
    我本来用 join 来插入的,但发现搞不定 f2.total 那一列
    jhsea3do
        5
    jhsea3do  
    OP
       2019-03-29 16:31:07 +08:00
    @jasonyang9

    嗯,f2 中的 total 要描述 该产品因为 qty 的增加,而变化的总量

    pid=1 的产品 初始数量是 5+5=10

    第 1 次变化 qty+3, 所以 total = 10 + 3 = 13

    第 2 次变化 qty+6, 所以 total = 13 + 6 = 19

    第 3 次变化 qty+9, 所以 total = 19 + 9 = 28
    jhsea3do
        6
    jhsea3do  
    OP
       2019-03-29 16:35:22 +08:00
    如果用存储过程, 我理解是用 fetch + loop 可以搞定的,主要对方希望我尽量用普通 sql
    jasonyang9
        7
    jasonyang9  
       2019-03-29 16:59:54 +08:00   ❤️ 1
    f2 关系(表)中的这个 Total 属性(字段、列)与主键没有函数依赖的吧?
    d1 关系中的 Total 属性与主键是传递依赖。
    导致的问题是,如果要修改条记录(元组)中的某个字段(属性)会影响到 N 多个其它记录或字段。。。
    这就是数据库设计不满足范式要求会出现的情况。

    以上个人理解,还请老铁们补充。。。
    ccczc
        8
    ccczc  
       2019-04-02 13:43:31 +08:00   ❤️ 1
    如果我没理解错
    sql server2012 以上版本不用存储过程可以实现
    jhsea3do
        9
    jhsea3do  
    OP
       2019-04-04 14:41:13 +08:00
    @ccczc 谢谢, 我很少用 sql server 了, 还想请教一下大概是个什么思路,比如用什么特性,函数之类的?
    ccczc
        10
    ccczc  
       2019-04-04 16:35:25 +08:00   ❤️ 1
    用到 LAG、OVER、ROWS 函数,主要计算相同批次当前行 part1 累加同批次之前行 part1 然后加上 total
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2760 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 12:05 · PVG 20:05 · LAX 05:05 · JFK 08:05
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.