MYSQL变量限制和子查询

Franck Barlet • 5 年前 • 1505 次点击

我对MySQL14.14发行版5.5.35上的mysql查询有一个问题:

我有一张桌子叫 空白表 有3列 date , value 和 id_patient .

CREATE TABLE `mytable` 
( `date` DATE NOT NULL 
, `id_patient` INT(11) NOT NULL 
, `value` INT(3) NULL DEFAULT NULL 
);

INSERT INTO `mytable` (`date`, `id_patient`, `value`) VALUES 
('2019-11-17', '87321', '6'), 
('2019-11-18', '87321', '1'), 
('2019-11-19', '87321', '2'), 
('2019-11-20', '87321', NULL), 
('2019-11-21', '87321', '5'), 
('2019-11-22', '87321', '8'), 
('2019-11-23', '87321', NULL), 
('2019-11-24', '87321', '3'), 
('2019-11-25', '87321', '4'), 
('2019-11-26', '87321', '6'), 
('2019-11-27', '87321', '1'), 
('2019-11-28', '87321', '10');

对于每一行,我需要知道前4个值的和不为空。

SELECT @date:=date, value, 

(     SELECT SUM(value)
      FROM mytable 
      WHERE date<@date 
      AND id_patient=87321 
      AND value IS NOT NULL 
      ORDER BY date DESC LIMIT 0,4 ) somme  

FROM mytable 
WHERE id_patient=87321

它不起作用。不管有没有结果都一样 ORDER BY date DESC LIMIT 0,4 以及选中当前行之前的每一行。

有人知道为什么吗?

有一个预期结果的例子:

+-------------+--------------------+--------+
| @date:=date | value              | somme  |
+-------------+--------------------+--------+
| 2019-11-17  |                  6 |   NULL | SUM OF 0 previous values not null
| 2019-11-18  |                  1 |      6 | SUM OF 1 previous values not null
| 2019-11-19  |                  2 |      7 | SUM OF 2 previous values not null
| 2019-11-20  |               NULL |      9 | SUM OF 3 previous values not null
| 2019-11-21  |                  5 |      9 | SUM OF 4 previous values not null
| 2019-11-22  |                  8 |     14 | SUM OF 4 previous values not null
| 2019-11-23  |               NULL |     16 | SUM OF 4 previous values not null
| 2019-11-24  |                  3 |     16 | SUM OF 4 previous values not null
| 2019-11-25  |                  4 |     18 | SUM OF 4 previous values not null
| 2019-11-26  |                  6 |     20 | SUM OF 4 previous values not null
| 2019-11-27  |                  1 |     21 | SUM OF 4 previous values not null
| 2019-11-28  |                 10 |     14 | SUM OF 4 previous values not null
+-------------+--------------------+--------+

谢谢你的帮助:)

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/52869

1505 次点击

文章 [ 2 ] | 最新文章 5 年前

• 1 楼

Ed Bangga 5 年前

这个 sum() 已经发生在你之前的每一排了 limit 结果。 Limit 在应用 求和() 使用另一个函数 subquery .

SELECT @date:=date, value, 
    (SELECT sum(value) from 
        (SELECT value
          FROM mytable 
          WHERE date<@date 
          AND id_patient=87321 
          AND value IS NOT NULL 
          ORDER BY date DESC LIMIT 0,4))
FROM mytable 
WHERE date<@date 
    AND id_patient=87321 
    AND value IS NOT NULL 
ORDER BY date DESC LIMIT 0,4

• 2 楼

forpas 5 年前

使用自连接和聚合:

select m.date, m.id_patient, m.value,
  sum(mm.value) somme
from mytable m left join mytable mm
on mm.id_patient = m.id_patient and mm.value is not null and mm.date < m.date 
and (
  select count(*) from mytable
  where id_patient = m.id_patient and value is not null 
  and date >= mm.date and date < m.date 
) <= 4
where m.id_patient = '87321'
group by m.date, m.id_patient, m.value

见 demo .
结果:

| date       | id_patient | value | somme |
| ---------- | ---------- | ----- | ----- |
| 2019-11-17 | 87321      | 6     |       |
| 2019-11-18 | 87321      | 1     | 6     |
| 2019-11-19 | 87321      | 2     | 7     |
| 2019-11-20 | 87321      |       | 9     |
| 2019-11-21 | 87321      | 5     | 9     |
| 2019-11-22 | 87321      | 8     | 14    |
| 2019-11-23 | 87321      |       | 16    |
| 2019-11-24 | 87321      | 3     | 16    |
| 2019-11-25 | 87321      | 4     | 18    |
| 2019-11-26 | 87321      | 6     | 20    |
| 2019-11-27 | 87321      | 1     | 21    |
| 2019-11-28 | 87321      | 10    | 14    |

登录后回复