获取不同ID的最后一行，然后显示大于零的数据 [英] Get Last Row of Different id then display data that is greater than zero

问题描述

这是我的桌子...

+----+--------+
| id | amount |
+----+--------+
| 1  | 100    |
| 1  | 50     |
| 1  | 0      |
| 2  | 500    |
| 2  | 100    |
| 3  | 300    |
| 3  | -2     |
| 4  | 400    |
| 4  | 200    |
+----+--------+

我想从中选择每个id值，该值 not 都与之相关的值为非正(即负或0)，而与之相关的值最小的amount id.

I would like to choose from it each value of id that does not have a nonpositive (i.e. negative or 0) value associated with it, and the smallest amount associated with that id.

如果我使用此代码...

If I use this code...

SELECT DISTINCT id, amount 
FROM table t 
WHERE amount = (SELECT MIN(amount) FROM table WHERE id= t.id)

...然后这些结果显示...

... then these results show...

+----+--------+
| id | amount |
+----+--------+
| 1  | 0      |
| 2  | 100    |
| 3  | -2     |
| 4  | 200    |
+----+--------+

但是我想要语句返回的是...

But what I want the statement to return is...

+----+--------+
| id | amount |
+----+--------+
| 2  | 100    |
| 4  | 200    |
+----+--------+

推荐答案

只需在查询中添加amount>0.您在查询中错过了该条件.那应该做.

Just add amount>0 in your query. You missed out that condition in your query. That should do it.

SELECT DISTINCT id, amount FROM table t 
WHERE amount = (SELECT MIN(amount) FROM table WHERE id= t.id)
and amount>0;

这篇关于获取不同ID的最后一行，然后显示大于零的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！