php - mysql 计数数量列并返回位置与行

Question

我有一张 table :

ID int
category int
quantity int
timestamp timestamp

我想选择 id="id#"并返回一个“位置”，它是同一类别中具有较早时间戳的每一行的数量总和。

谢谢指点!

Answer 1

试试这个:

SELECT sum(quantity) FROM TABLE WHERE category='...' AND timestamp < some_timestamp

如果要通过id选择:

SELECT sum(quantity) FROM mytable
WHERE category IN (SELECT category FROM mytable WHERE id=some_id)
AND timestamp <= some_timestamp

更新

要使用行本身的时间戳，您可以这样做:

SELECT sum(quantity) FROM mytable
WHERE category IN (SELECT category FROM mytable WHERE id=some_id)
AND timestamp <= (SELECT timestamp FROM mytable WHERE id=some_id)

.. 或使用 Adam 的回答中的自连接(也许这不是矫枉过正，毕竟.. :))

更新 - 替代解决方案

这似乎也很好..

SELECT t1.id, sum(t2.quantity), t1.timestamp, t1.category
FROM mytable t1 INNER JOIN mytable AS t2 ON t1.category=t2.category
WHERE t1.id=some_id_here AND t2.timestamp <= t1.timestamp;

更新

已更改 <进入<= , 否则将不计算所选商品的数量!