如何根据PostgreSQL part2中的枚举列总结所有行？

问题描述

限时删除！！

目前我正在使用这个函数触发...

<$ p $ （timeelapse）FROM（floor（timeindex）:: int）floor（timeindex）:: int timeindex，timeelapse FROM mytable WHERE pcnum = NEW.pcnum AND fnname = NEW.fnname AND timetype ='Lap'ORDER BY 1，2 DESC）别名INTO v_sumtimeelapse_fn;
如果发现那么
NEW.timeelapse：= v_sumtimeelapse_fn;
ELSE
使用MESSAGE的RAISE EXCEPTION ='没有任何前一行...';
END IF;
END IF;

因此，每当'Total'被插入到timetype列时，它总计所有timeelapse FROM mytable WHERE pcnum = NEW.pcnum AND fnname = NEW.fname AND timetype ='Lap'，但只有timeindex具有最高的百分数值（在mytable中，它们将是前三个skyblue高亮区= 00:01:00，00:03:00，00 ：04：00（1.001,2.003,3.003））并将结果放置在NEW.timeelapse中（在mytable中，它将是第一个蓝色突出显示的square = 00:08:00）

这个函数使得SUM正确，但我想添加两条规则......

1. 当'Total'被插入时间类型时，如果函数在'开始'之后没有找到'Lap'（两者都具有相同的pcnum和fnname），那么它应该显示一个错误消息'，没有任何Lap。 ..（在mytable中，它将是红色突出显示的正方形）。请注意，fnname ='bbbb'的'开始'之后没有任何'Lap'的fnname ='bbbb'。我尝试设置我的功能，使用...

     
    
   解决方案 
   要从计算行中排除早于总计您可以在附件查询中使用主键。 
   声明新变量 v_fnserial 。 
   为给定的 pcnum 和<$ c找到最后一次出现'Total'的行的 fnserial  $ c> fnname 并将值赋给 v_fnserial 。 
   在主查询中添加一个条件 fnserial> v_fnserial 。  
    
    
   当主查询返回 null 时，应该引发异常。
     IF NEW.timetype ='Total'THEN 
    SELECT fnserial INTO v_fnserial 
    FROM mytable 
    WHERE timetype ='Total'AND pcnum = NEW.pcnum AND fnname = NEW.fnname 
    ORDER BY fnserial DESC LIMIT 1; 
    
    SELECT SUM（timeelapse）FROM（
    SELECT DISTINCT ON（floor（timeindex）:: int）floor（timeindex）:: int timeindex，timeelapse 
    FROM mytable 
    WHERE fnserial> coalesce（v_fnserial，0）AND pcnum = NEW.pcnum AND fnname = NEW.fnname AND timetype ='Lap'
    ORDER BY 1，2 DESC）别名
    INTO v_sumtimeelapse_fn; 
    IF v_sumtimeelapse_fn NOTNULL THEN 
    NEW.timeelapse：= v_sumtimeelapse_fn; 
    ELSE 
   使用MESSAGE的RAISE EXCEPTION ='没有任何前一行...'; 
    END IF; 
    END IF; 
     
    
   ))Hi all, this is my table...
   Currently Im using this function trigger...
   IF NEW.timetype = 'Total' THEN
     SELECT SUM(timeelapse) FROM (SELECT DISTINCT ON (floor(timeindex)::int) floor(timeindex)::int timeindex, timeelapse FROM mytable WHERE pcnum = NEW.pcnum AND fnname = NEW.fnname AND timetype = 'Lap' ORDER BY 1, 2 DESC) alias INTO v_sumtimeelapse_fn;
       IF FOUND THEN
         NEW.timeelapse := v_sumtimeelapse_fn;
       ELSE
         RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
       END IF;
   END IF;
   So whenever 'Total' is INSERTed on the timetype column it SUMs up all timeelapse FROM mytable WHERE pcnum = NEW.pcnum AND fnname = NEW.fname AND timetype = 'Lap' but only where timeindex has the highest centesimal value (in mytable they would be the first three skyblue highighted squares = 00:01:00, 00:03:00, 00:04:00 (1.001, 2.003, 3.003)) and place the result in the NEW.timeelapse (in mytable it would be the first blue highlighted square = 00:08:00)
   The function makes the SUM correctly, but I would like to add two more rules to it...
   when 'Total' is INSERTed in timetype, if the function doesn't find any 'Lap' after a 'Start' (both with the same pcnum and fnname) then it should show an ERROR MESSAGE "'there isn't any Lap..." (in mytable it would be the red highlighted square). Note that there isn't any 'Lap' of fnname = 'bbbb' after 'Start' of fnname = 'bbbb'. I try to set my function to do so, using...
   RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...'
   but it just doesn't work, if I INSERT a 'Total' in that situation, it simple leaves the NEW.timeelapse NULL.
   The function should SUM the timeelapse with all its already set rules, but only FROM those which are below the last(descendent) 'Total' of the same pcnum and fnname (in mytable it will be the second blue highlighted square) Note that it SUM only the timeelapses that are after the last(descendet) 'Total' (in mytable it will be the fourth skyblue highlighted square).
   Thanks Advanced.
    解决方案 
   To exclude from the calculation rows earlier than last Total you can use the primary key in an accessory query.Declare new variable v_fnserial.Find a fnserial of the row with last occurence of 'Total' for given pcnum and fnname and assign the value to v_fnserial.In the main query add a condition fnserial > v_fnserial. 
   You should raise an exception when the main query returns null.
   IF NEW.timetype = 'Total' THEN
       SELECT fnserial INTO v_fnserial
       FROM mytable
       WHERE timetype = 'Total' AND pcnum = NEW.pcnum AND fnname = NEW.fnname
       ORDER BY fnserial DESC LIMIT 1;
   
       SELECT SUM(timeelapse) FROM (
           SELECT DISTINCT ON (floor(timeindex)::int) floor(timeindex)::int timeindex, timeelapse
           FROM mytable
           WHERE fnserial > coalesce(v_fnserial, 0) AND pcnum = NEW.pcnum AND fnname = NEW.fnname AND timetype = 'Lap'
           ORDER BY 1, 2 DESC) alias
       INTO v_sumtimeelapse_fn;
       IF v_sumtimeelapse_fn NOTNULL THEN
           NEW.timeelapse := v_sumtimeelapse_fn;
       ELSE
           RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
       END IF;
   END IF;
                           
   这篇关于如何根据PostgreSQL part2中的枚举列总结所有行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！
                           
   1403页，肝出来的..