获取行数限制值sql php [英] Get count of rows limit value sql php

问题描述

我有一个带有以下数据的sql表交易

I have an sql table trade with following data

  id| value
   1| 0.1
   2| 0.5
   3| 0.9
   4| 0.3

我该如何进行SQL查询，以便获得的条目数限制为在PHP中按ID升序总值为0.8.例如:-

How do I make an SQL query so that i get the count of entries limited to total value of 0.8 ascending by id in php. For example:-

COUNT id FROM trade WHERE SUM(value) > 0.8 by id ASC

结果应为

 3

推荐答案

您需要进行累积判断，因为您的mysql版本不支持window函数，因此该解决方案将很难阅读，因为您需要编写子查询而不是窗口函数.

You need to do cumulative judgment,due to your mysql version didn't support window function so the solution will be a little hard to read,becuase you need to write subquery instead of window function.

通过 Id 列进行累加，然后在子查询中获得 MIN(ID)，当 Id = 3 时，该值将更大比 0.8 .

make the cumulative number by Id column, then get the MIN(ID) in subquery, when Id = 3 the value will greater than 0.8.

最终使ID变小并等于 MIN(ID)，您将得到预期的结果.

finally get the Id small and equal the MIN(ID) you will get your expect result.

CREATE TABLE trade(
   id  INT,
    value FLOAT
);

INSERT INTO trade VALUES(1,0.1);
INSERT INTO trade VALUES(2,0.5);
INSERT INTO trade VALUES(3,0.9);
INSERT INTO trade VALUES(4,0.3);

查询1 :

SELECT COUNT(*)
FROM trade t1
WHERE t1.id <= (
SELECT MIN(ID) FROM (
 SELECT ID,(
    SELECT SUM(tt.value) 
    FROM trade tt
    WHERE tt.id <= t1.id  
  ) total
  FROM trade t1
) t1
where total > 0.8
)

结果 :

Results:

| COUNT(*) |
|----------|
|        3 |

这篇关于获取行数限制值sql php的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！