需要将这个mysql表与子字符串相加才能找到正确的值

mwecs4sa 于 2021-06-25 发布在 Mysql

关注(0)|答案(1)|浏览(301)

我坚持按id/rev对表中的项目求和。
我需要从内容字段中减去值来计算总和。传递的是加号，返回的是减号，记录有时间戳，我需要表中不同id的最后一次重置之前的总和。例如，id1的最后一次重置时间是2018-01-12 14:10，我只需要这个日期之后的3个sysx和。有可能在一个查询中得到9个和吗？
我需要以下结果：

ID1   SYS1    +/-x items (delivered items - returned items until the most reset for this ID)
ID1   SYS2    +/-x items
ID1   SYS3    +/-x items
ID2   SYS1    +/-x items
ID2   SYS2    +/-x items
ID2   SYS3    +/-x items
ID3   SYS1    +/-x items
ID3   SYS2    +/-x items
ID3   SYS3    +/-x items

CREATE TABLE docs (
  tDate DATETIME NULL,
  id varchar(200) NOT NULL,
  rev varchar(200)NOT NULL,
  content varchar(200) NOT NULL
);

INSERT INTO docs (tDate, id, rev, content) VALUES
  ('2018-01-13 12:10','ID1', 'SYS1', 'returned 3 items'),
  ('2018-01-13 12:05','ID1', 'SYS2', 'delivered items: 4'),
  ('2018-01-13 12:00','ID2', 'SYS3', 'returned 2 items'),
  ('2018-01-12 14:10','ID1', 'RESET', ''),
  ('2018-01-12 12:50','ID2', 'SYS1', 'delivered items: 1'),
  ('2018-01-12 12:40','ID3', 'SYS2', 'returned 1 item'),
  ('2018-01-12 12:30','ID3', 'SYS3', 'delivered items: 1'),
  ('2018-01-12 12:20','ID2', 'SYS1', 'delivered items: 1'),
  ('2018-01-11 12:00','ID3', 'SYS2', 'returned 1 item'),
  ('2018-01-11 12:00','ID1', 'SYS2', 'delivered items: 1'),
  ('2018-01-11 12:00','ID3', 'SYS3', 'returned 3 items'),
  ('2018-01-10 12:10','ID1', 'RESET', ''),
  ('2018-01-10 12:00','ID2', 'SYS3', 'delivered items: 1'),
  ('2018-01-10 12:00','ID3', 'SYS1', 'delivered items: 1'),
  ('2018-01-09 13:20','ID2', 'SYS2', 'delivered items: 3'),
  ('2018-01-08 14:00','ID3', 'SYS1', 'delivered items: 1'),
  ('2018-01-07 14:10','ID3', 'RESET', ''),
  ('2018-01-07 12:00','ID1', 'SYS2', 'returned 2 items'),
  ('2018-01-06 13:00','ID1', 'SYS1', 'delivered items: 1'),
  ('2018-01-05 14:00','ID2', 'SYS2', 'delivered items: 3'),
  ('2018-01-05 13:00','ID2', 'SYS3', 'delivered items: 2'),
  ('2018-01-05 12:00','ID3', 'SYS2', 'returned 1 item'),
  ('2018-01-04 17:00','ID3', 'SYS1', 'delivered items: 2'),
  ('2018-01-03 17:10','ID2', 'RESET', ''),
  ('2018-01-02 18:00','ID2', 'SYS3', 'delivered items: 2'),
  ('2018-01-01 19:00','ID3', 'SYS2', 'returned 1 item');

mysql sum select syntax

来源：https://stackoverflow.com/questions/50270353/need-to-sum-this-mysql-table-with-substrings-to-find-the-correct-value

1条答案

按热度按时间

3okqufwl1#

Solution has two part:

**first 1 is a function:**

DELIMITER $$

CREATE FUNCTION `ExtractNumber`(in_string VARCHAR(50)) 
RETURNS INT
NO SQL
BEGIN
    DECLARE ctrNumber VARCHAR(50);
    DECLARE finNumber VARCHAR(50) DEFAULT '';
    DECLARE sChar VARCHAR(1);
    DECLARE inti INTEGER DEFAULT 1;

    IF LENGTH(in_string) > 0 THEN
        WHILE(inti <= LENGTH(in_string)) DO
            SET sChar = SUBSTRING(in_string, inti, 1);
            SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9'); 
            IF ctrNumber > 0 THEN
                SET finNumber = CONCAT(finNumber, sChar);
            END IF;
            SET inti = inti + 1;
        END WHILE;
        RETURN CAST(finNumber AS UNSIGNED);
    ELSE
        RETURN 0;
    END IF;    
END$$

DELIMITER ;

**second one is output SQL:**

 SELECT s.*, SUM(
    IF(tDate<=max_tDate AND (content LIKE '%delivered%'),qty,
    IF(tDate<=max_tDate AND (content LIKE '%returned%'),-qty,0)) ) summ FROM
    (    
        SELECT docs.*, ExtractNumber(content) qty, mx.max_tDate  FROM docs 
        LEFT OUTER JOIN (SELECT id, MAX(tDate) max_tDate  FROM docs WHERE rev='RESET' GROUP BY id) mx ON(docs.id=mx.id)

        ORDER BY docs.tDate
    ) s
    GROUP BY s.id

赞(0）回复(0）举报 2021-06-25

我来回答

需要将这个mysql表与子字符串相加才能找到正确的值

1条答案

相关问题

热门标签

最新问答