SQL选择表中的某些行,以便它们总计为某个值
如何才能在下表中选择一些行,以便它们总计为某个值?SQL选择表中的某些行,以便它们总计为某个值
Table
-----
id | qty1 | qty2 | qty3 | qty4
------------------------------
1 | 0.0 | 0.0 | 10 | 20
2 | 1.5 | 0.0 | 7.5 | 18
3 | 1.0 | 2.0 | 7.5 | 18
4 | 0.0 | 0.5 | 5 | 13
比方说,前值我要的是57 ...
所以我需要从上表中选择行这样qty1 + qty2 + qty3 +每一行的qty4,直到获得那57值,并放弃其他行。在这个例子中,我会得到以下:
id | qty1 | qty2 | qty3 | qty4
------------------------------
1 | 0.0 | 0.0 | 10 | 20
2 | 1.5 | 0.0 | 7.5 | 18
因为10 + 20 + 1.5 + 7.5 + 18 = 57,所以丢弃行3 & 4 ...
现在我希望顶值是50,那么我应该得到:
id | qty1 | qty2 | qty3 | qty4
------------------------------
1 | 0.0 | 0.0 | 10 | 20
2 | 1.5 | 0.0 | 7.5 | 11
因为这些值总和为50,并从ROW2 7,qty4被冷落...... (BTW行以这种特别的方式订购,因为这是我希望说明qtys的总和的顺序......总结第一个r是无效的ow1,然后3,然后2然后4,例如...他们应该总是按顺序1,2,3,4 ...)
如果我想补充这个?我的意思是,其他两行我没有得到最后的结果。
第一种情况:
id | qty1 | qty2 | qty3 | qty4
------------------------------
3 | 1.0 | 2.0 | 7.5 | 18
4 | 0.0 | 0.5 | 5 | 13
第二种情况:
id | qty1 | qty2 | qty3 | qty4
------------------------------
2 | 0.0 | 0.0 | 0.0 | 7
3 | 1.0 | 2.0 | 7.5 | 18
4 | 0.0 | 0.5 | 5 | 13
(如果第二种情况过于复杂,如何获取如何:
id | qty1 | qty2 | qty3 | qty4
------------------------------
1 | 0.0 | 0.0 | 10 | 20
因为添加了原来qtys第2行将超过50的值,我放弃它... 在这种情况下的补充应该仅仅是:
id | qty1 | qty2 | qty3 | qty4
------------------------------
2 | 1.5 | 0.0 | 7.5 | 18
3 | 1.0 | 2.0 | 7.5 | 18
4 | 0.0 | 0.5 | 5 | 13
)
让我们这样说:如果SQL是一种宗教,我会去提供这种解决方案。 SQL并不是为了解决这类问题,所以任何解决方案都是可怕的。矿也不例外:)
set @limitValue := 50;
select id, newQty1, newQty2, newQty3, newQty4 from (
select id,
if(@limitValue - qty1 > 0, qty1, greatest(@limitValue, 0)) newQty1,
@limitValue := @limitValue - qty1 Total1,
if(@limitValue - qty2 > 0, qty2, greatest(@limitValue, 0)) newQty2,
@limitValue := @limitValue - qty2 Total2,
if(@limitValue - qty3 > 0, qty3, greatest(@limitValue, 0)) newQty3,
@limitValue := @limitValue - qty3 Total3,
if(@limitValue - qty4 > 0, qty4, greatest(@limitValue, 0)) newQty4,
@limitValue := @limitValue - qty4 Total4
from (
select id, qty1, qty2, qty3, qty4,
@rowTotal < @limitValue Useful,
@previousRowTotal := @rowTotal PreviousRowTotal,
@rowTotal := @rowTotal + qty1 + qty2 + qty3 + qty4 AllRowsTotal,
@rowTotal - @previousRowTotal CurrentRowTotal
from t,
(select @rowTotal := 0, @previousRowTotal := 0) S1
) MarkedUseful
where useful = 1
) Final
对于提供的数据,这会导致:
+----+---------+---------+---------+---------+
| ID | NEWQTY1 | NEWQTY2 | NEWQTY3 | NEWQTY4 |
+----+---------+---------+---------+---------+
| 1 | 0 | 0 | 10 | 20 |
| 2 | 1.5 | 0 | 7.5 | 11 |
+----+---------+---------+---------+---------+
和补体:
set @limitValue := 50;
select t1.id,
coalesce(t1.qty1 - newQty1, t1.qty1) newQty1,
coalesce(t1.qty2 - newQty2, t1.qty2) newQty2,
coalesce(t1.qty3 - newQty3, t1.qty3) newQty3,
coalesce(t1.qty4 - newQty4, t1.qty4) newQty4
from t t1 left join (
select id,
if(@limitValue - qty1 > 0, qty1, greatest(@limitValue, 0)) newQty1,
@limitValue := @limitValue - qty1 Total1,
if(@limitValue - qty2 > 0, qty2, greatest(@limitValue, 0)) newQty2,
@limitValue := @limitValue - qty2 Total2,
if(@limitValue - qty3 > 0, qty3, greatest(@limitValue, 0)) newQty3,
@limitValue := @limitValue - qty3 Total3,
if(@limitValue - qty4 > 0, qty4, greatest(@limitValue, 0)) newQty4,
@limitValue := @limitValue - qty4 Total4
from (
select id, qty1, qty2, qty3, qty4,
@rowTotal < @limitValue Useful,
@previousRowTotal := @rowTotal PreviousRowTotal,
@rowTotal := @rowTotal + qty1 + qty2 + qty3 + qty4 AllRowsTotal,
@rowTotal - @previousRowTotal CurrentRowTotal
from t,
(select @rowTotal := 0, @previousRowTotal := 0) S1
) MarkedUseful
where useful = 1
) Final
on t1.id = final.id
where Total1 < 0 or Total2 < 0 or Total3 < 0 or Total4 < 0 or final.id is null
对于提供的数据,这导致:
+----+---------+---------+---------+---------+
| ID | NEWQTY1 | NEWQTY2 | NEWQTY3 | NEWQTY4 |
+----+---------+---------+---------+---------+
| 2 | 0 | 0 | 0 | 7 |
| 3 | 1 | 2 | 7.5 | 18 |
| 4 | 0 | 0.5 | 5 | 13 |
+----+---------+---------+---------+---------+
享受!
只是尝试了所提供的解决方案,但对于补充案例,使用相同的样本数据,我没有得到你显示的结果,但只是从数据表中的原始4行... – 2012-03-06 15:49:55
现在它的工作原理!我不知道我上次做错了什么:)让我测试它,并将其应用于我的真实案例,我会给你一个关于赏金的答案;) – 2012-03-07 15:59:47
当然,慢慢来:) – 2012-03-07 19:54:32
括号中的简化方法是不是太糟糕了:
SELECT foo1.*
FROM foo AS foo1
JOIN foo AS foo2
ON foo2.id <= foo1.id
GROUP
BY foo1.id
HAVING SUM(foo2.qty1 + foo2.qty2 + foo2.qty3 + foo2.qty4) <= 57
;
(你没有提到表的名字,所以我foo
去)。
补是:
SELECT *
FROM foo
WHERE id NOT IN
(SELECT foo1.id
FROM foo AS foo1
JOIN foo AS foo2
ON foo2.id <= foo1.id
GROUP
BY foo1.id
HAVING SUM(foo2.qty1 + foo2.qty2 + foo2.qty3 + foo2.qty4) <= 57
)
;
非熟练的选项更棘手;这是可行的,但你会更好使用stored procedure。
让我们从问题
mysql> drop database if exists javier;
Query OK, 1 row affected (0.02 sec)
mysql> create database javier;
Query OK, 1 row affected (0.01 sec)
mysql> use javier
Database changed
mysql> create table mytable
-> (
-> id int not null auto_increment,
-> qty1 float,qty2 float,qty3 float,qty4 float,
-> primary key (id)
->);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into mytable (qty1,qty2,qty3,qty4) values
-> (0.0 , 0.0 , 10 , 20),(1.5 , 0.0 , 7.5 , 18),
-> (1.0 , 2.0 , 7.5 , 18),(0.0 , 0.5 , 5 , 13);
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from mytable;
+----+------+------+------+------+
| id | qty1 | qty2 | qty3 | qty4 |
+----+------+------+------+------+
| 1 | 0 | 0 | 10 | 20 |
| 2 | 1.5 | 0 | 7.5 | 18 |
| 3 | 1 | 2 | 7.5 | 18 |
| 4 | 0 | 0.5 | 5 | 13 |
+----+------+------+------+------+
4 rows in set (0.00 sec)
mysql>
最终查询,充分WORKS
select BBBB.* from (select id,sums FROM (select A.id,A.sums from
(select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB
where BB.id<=AA.id) sums from mytable AA order by id) A
INNER JOIN (SELECT 50 mylimit) B ON A.sums <= B.mylimit) AAA
UNION
(select A.id,A.sums from (select id,(select sum(qty1+qty2+qty3+qty4)
from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A
where A.sums=(select min(A.sums) sums from (select id,
(select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums
from mytable AA order by id) A INNER JOIN (SELECT 50 mylimit) B
ON A.sums >= B.mylimit))) AAAA JOIN mytable BBBB USING (id);
最终查询补,充分WORKS
select BBBB.* from mytable BBBB LEFT JOIN
(select id,sums FROM (select A.id,A.sums from (
select id,(select sum(qty1+qty2+qty3+qty4)
from mytable BB where BB.id<=AA.id) sums
from mytable AA order by id) A INNER JOIN
(SELECT 50 mylimit) B ON A.sums <= B.mylimit) AAA
UNION
(select A.id,A.sums from (select id,
(select sum(qty1+qty2+qty3+qty4) from mytable BB
where BB.id<=AA.id) sums from mytable AA order by id) A
where A.sums=(select min(A.sums) sums from (
select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB
where BB.id<=AA.id) sums from mytable AA order by id) A
INNER JOIN (SELECT 50 mylimit) B ON A.sums >= B.mylimit))) AAAA
USING (id) WHERE AAAA.id IS NULL;
这里加载样本数据是57
mysql> select BBBB.* from (select id,sums FROM (select A.id,A.sums from
-> (select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB
-> where BB.id<=AA.id) sums from mytable AA order by id) A
-> INNER JOIN (SELECT 57 mylimit) B ON A.sums <= B.mylimit) AAA
-> UNION
-> (select A.id,A.sums from (select id,(select sum(qty1+qty2+qty3+qty4)
-> from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A
-> where A.sums=(select min(A.sums) sums from (select id,
-> (select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums
-> from mytable AA order by id) A INNER JOIN (SELECT 57 mylimit) B
-> ON A.sums >= B.mylimit))) AAAA JOIN mytable BBBB USING (id);
+----+------+------+------+------+
| id | qty1 | qty2 | qty3 | qty4 |
+----+------+------+------+------+
| 1 | 0 | 0 | 10 | 20 |
| 2 | 1.5 | 0 | 7.5 | 18 |
+----+------+------+------+------+
2 rows in set (0.00 sec)
mysql> select BBBB.* from mytable BBBB LEFT JOIN
-> (select id,sums FROM (select A.id,A.sums from (
-> select id,(select sum(qty1+qty2+qty3+qty4)
-> from mytable BB where BB.id<=AA.id) sums
-> from mytable AA order by id) A INNER JOIN
-> (SELECT 57 mylimit) B ON A.sums <= B.mylimit) AAA
-> UNION
-> (select A.id,A.sums from (select id,
-> (select sum(qty1+qty2+qty3+qty4) from mytable BB
-> where BB.id<=AA.id) sums from mytable AA order by id) A
-> where A.sums=(select min(A.sums) sums from (
-> select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB
-> where BB.id<=AA.id) sums from mytable AA order by id) A
-> INNER JOIN (SELECT 57 mylimit) B ON A.sums >= B.mylimit))) AAAA
-> USING (id) WHERE AAAA.id IS NULL;
+----+------+------+------+------+
| id | qty1 | qty2 | qty3 | qty4 |
+----+------+------+------+------+
| 3 | 1 | 2 | 7.5 | 18 |
| 4 | 0 | 0.5 | 5 | 13 |
+----+------+------+------+------+
2 rows in set (0.00 sec)
mysql>
输出下面是50
mysql> select BBBB.* from (select id,sums FROM (select A.id,A.sums from
-> (select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB
-> where BB.id<=AA.id) sums from mytable AA order by id) A
-> INNER JOIN (SELECT 50 mylimit) B ON A.sums <= B.mylimit) AAA
-> UNION
-> (select A.id,A.sums from (select id,(select sum(qty1+qty2+qty3+qty4)
-> from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A
-> where A.sums=(select min(A.sums) sums from (select id,
-> (select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums
-> from mytable AA order by id) A INNER JOIN (SELECT 50 mylimit) B
-> ON A.sums >= B.mylimit))) AAAA JOIN mytable BBBB USING (id);
+----+------+------+------+------+
| id | qty1 | qty2 | qty3 | qty4 |
+----+------+------+------+------+
| 1 | 0 | 0 | 10 | 20 |
| 2 | 1.5 | 0 | 7.5 | 18 |
+----+------+------+------+------+
2 rows in set (0.00 sec)
mysql> select BBBB.* from mytable BBBB LEFT JOIN
-> (select id,sums FROM (select A.id,A.sums from (
-> select id,(select sum(qty1+qty2+qty3+qty4)
-> from mytable BB where BB.id<=AA.id) sums
-> from mytable AA order by id) A INNER JOIN
-> (SELECT 50 mylimit) B ON A.sums <= B.mylimit) AAA
-> UNION
-> (select A.id,A.sums from (select id,
-> (select sum(qty1+qty2+qty3+qty4) from mytable BB
-> where BB.id<=AA.id) sums from mytable AA order by id) A
-> where A.sums=(select min(A.sums) sums from (
-> select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB
-> where BB.id<=AA.id) sums from mytable AA order by id) A
-> INNER JOIN (SELECT 50 mylimit) B ON A.sums >= B.mylimit))) AAAA
-> USING (id) WHERE AAAA.id IS NULL;
+----+------+------+------+------+
| id | qty1 | qty2 | qty3 | qty4 |
+----+------+------+------+------+
| 3 | 1 | 2 | 7.5 | 18 |
| 4 | 0 | 0.5 | 5 | 13 |
+----+------+------+------+------+
2 rows in set (0.01 sec)
mysql>
输出请记住设置数量在(SELECT 50 mylimit)
子查询mylimit各两次。
请告诉我这一个...
谢谢:)但是这只是解决了我提出的简单情况,而不是复杂的情况;) – 2012-03-07 16:04:23
@Javier什么是复杂的?如果你的意思是使用其他一些数字,例如50,只需在两个查询(FINAL和FINAL COMPLEMENT)中用'(SELECT 50 mylimit)'替换'(SELECT 57 mylimit)',它们将完美工作。否则,请解释复杂的情况。 – RolandoMySQLDBA 2012-03-07 16:09:31
我会尝试用示例来解释我使用的样本数据。在mylimit = 50的情况下,查询结果应为id = 2:qty1 = 1.5,qty2 = 0.0,qty3 = 7.5,qty4 = 11。但是,您的查询给我没有id = 2的行。如您所见,qty4 = 11不是数据表中的数据,而是每行添加qty1 + qty2 + qty3 + qty4的值直到达到mylimit值的结果。也就是说,当你添加50时,就会达到:row1.qty1 + row1.qty2 + row1.qty3 + row1.qty4 then + row2.qty1 + row2.qty2 + row2.qty3 then +7从原始row2.qty4中减去= 18,这给出了你在期望的结果上看到的7个 – 2012-03-07 18:58:02
你应该调整只在init
子查询的@limit变量初始化。第一个查询输出数据达到极限,secnd查询输出其补码。
SELECT
id,
@qty1 as qty1,
@qty2 as qty2,
@qty3 as qty3,
@qty4 as qty4
FROM quantities q,
(SELECT @qty1:=0.0, @qty2:=0.0,
@qty3:=0.0, @qty4:=0.0,
@limit:=50.0) init
WHERE
IF(@limit > 0,
GREATEST(1,
IF(@limit-qty1 >=0,
@limit:=(@limit-(@qty1:=qty1)),
@qty1:[email protected] + LEAST(@limit, @limit:=0)),
IF(@limit-qty2 >=0,
@limit:=(@limit-(@qty2:=qty2)),
@qty2:[email protected] + LEAST(@limit, @limit:=0)),
IF(@limit-qty3 >=0,
@limit:=(@limit-(@qty3:=qty3)),
@qty3:[email protected] + LEAST(@limit, @limit:=0)),
IF(@limit-qty4 >=0,
@limit:=(@limit-(@qty4:=qty4)),
@qty4:[email protected] + LEAST(@limit, @limit:=0))),0)
;
补语:
SELECT
id,
IF([email protected], qty1, [email protected]) as qty1,
IF([email protected], qty2, [email protected]) as qty2,
IF([email protected], qty3, [email protected]) as qty3,
IF([email protected], qty4, [email protected]) as qty4
FROM quantities q,
(SELECT @qty1:=0.0, @qty2:=0.0,
@qty3:=0.0, @qty4:=0.0,
@limit:=50.0) init
WHERE
IF(
LEAST(
IF(@limit-qty1 >=0,
@limit:=(@limit-(@qty1:=qty1)),
@qty1:[email protected] + LEAST(@limit, @limit:=0)),
IF(@limit-qty2 >=0,
@limit:=(@limit-(@qty2:=qty2)),
@qty2:[email protected] + LEAST(@limit, @limit:=0)),
IF(@limit-qty3 >=0,
@limit:=(@limit-(@qty3:=qty3)),
@qty3:[email protected] + LEAST(@limit, @limit:=0)),
IF(@limit-qty4 >=0,
@limit:=(@limit-(@qty4:=qty4)),
@qty4:[email protected] + LEAST(@limit, @limit:=0)),
@limit), 0, 1)
;
谢谢!它可以处理样本数据。让我用我的真实数据进行测试,然后我会回答你关于赏金的事情...... – 2012-03-07 16:07:15
我已经写了很多复杂的查询,我喜欢承担一个挑战,但这是那些只是乞求你写程序代码少见的情况用你选择的语言。 – 2012-03-01 23:40:18
即使是简化的第二种情况?在帖子末尾括号中的最后一个......? – 2012-03-01 23:41:42
简化的第二种情况可作为查询使用。如果你修改你的问题来问这个问题(或者创建一个新的问题,那就要求),我可以提供帮助。 – 2012-03-01 23:54:06