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

Question

我必须根据 id/rev 对该表中的项目进行求和。我需要以下结果:

ID1   SYS1    +/-x items (delevered items - returned items)
ID1   SYS2    +/-x items
ID1   SYS3    +/-x items
ID2   SYS1    +/-x items
ID2   SYS2    +/-x items
ID2   SYS3    +/-x items

但是我只需要减去该值即可计算总和。我用 CASE WHEN (content='returned x items') THEN ELSE 构建了一个列表，其中包含从 0 到 99 的所有可能性，但我不认为这是这里的方法。释放为正，返回为负

CREATE TABLE IF NOT EXISTS `docs` (
  `id` varchar(200) NOT NULL,
  `rev` varchar(200)NOT NULL,
  `content` varchar(200) NOT NULL

) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
  ('ID1', 'SYS1', 'returned 1 item'),
  ('ID2', 'SYS1', 'delivered 2 items'),
  ('ID1', 'SYS2', 'returned 41 items'),
  ('ID1', 'SYS3', 'returned 12 items'),
  ('ID2', 'SYS1', 'returned 12 items'),
  ('ID1', 'SYS1', 'delivered 11 items'),
  ('ID2', 'SYS1', 'returned 13 items'),
  ('ID1', 'SYS2', 'returned 14 items'),
  ('ID2', 'SYS2', 'delivered 11 items'),
  ('ID2', 'SYS2', 'returned 12 items'),
  ('ID1', 'SYS3', 'delivered 17 items'),
  ('ID1', 'SYS2', 'returned 11 items'),
  ('ID1', 'SYS1', 'delivered 14 items'),
  ('ID2', 'SYS1', 'returned 11 items'),
  ('ID2', 'SYS1', 'returned 14 items'),
  ('ID1', 'SYS2', 'delivered 11 items'),
  ('ID1', 'SYS1', 'returned 12 items'),
  ('ID2', 'SYS1', 'delivered 15 items'),
  ('ID1', 'SYS2', 'returned 11 items'),
  ('ID1', 'SYS1', 'delivered 14 items'),
  ('ID2', 'SYS1', 'returned 15 items'),
  ('ID1', 'SYS2', 'delivered 14 items'),
  ('ID1', 'SYS3', 'returned 18 items');

Answer 1

作为变体，您可以使用REPLACE函数，然后将CAST字符串转换为int

SELECT
  *,
  IF(content LIKE 'returned%',-1,1)*CAST(REPLACE(REPLACE(REPLACE(REPLACE(content,'delivered ',''),'returned ',''),' items',''),' item','') AS SIGNED) QtyVer1,
  IF(content LIKE 'returned%',-1,1)*SUBSTRING_INDEX(SUBSTRING_INDEX(content,' ',-2),' ',1) QtyVer2
FROM docs

您还可以尝试使用 SUBSTRING_INDEX - 查看 QtyVer2。

以及使用GROUP BY的查询

SELECT
  id,
  rev,
  SUM(IF(content LIKE 'returned%',-1,1)*SUBSTRING_INDEX(SUBSTRING_INDEX(content,' ',-2),' ',1)) Total
FROM docs
GROUP BY id,rev

SQL fiddle - http://www.sqlfiddle.com/#!9/0611f/17