id number my_id
1 1 789
1 2 645
1 3 222
1 4 544
1 5 155
简单计数结果将等于 5
SELECT COUNT(id) AS count FROM table WHERE id = 1
我怎样才能数到找到某个值?
例如:计数直到my_id为"222",计数结果 = 3
谢谢!
这取决于顺序,"直到my_id是 222"是什么意思?
您可以进行这样的查询(或相反(,但这将使用my_id
字段来确定应计算谁:
SELECT count(id) as count
FROM YourTable
WHERE id = 1 AND my_id <= 222
编辑:您的问题缺少一些关键信息,但也许您的意思是:
SELECT COUNT(t.id) as count
FROM YourTable t
WHERE t.id = 1
AND t.number <= (SELECT number FROM YourTable s
WHERE s.id = 1 and S.my_id = 222)
此查询依赖于以下事实:只有 1 条记录将与内部查询匹配。
SQLFiddle
假设数字被排序
select count(*) from t where `number`<=(select `number` from t where my_id=222)