MySQL选择具有唯一属性值的一行

I'm building a stock keeping system and decided to store each product's balance (everytime it's updated) into the following table:

我正在建立一个库存系统,并决定将每个产品的余额(每次更新时)存储到下表中:

+------------+--------------+---------+------+
| Product_id | Warehouse_id | Balance | Date |
+------------+--------------+---------+------+

Example:

Staff adds 10 pieces to product_id 123 in warehouse_id 5

工作人员在warehouse_id 5中向product_id 123添加10件

+------------+--------------+---------+-------------+
| Product_id | Warehouse_id | Balance | Date        |
+------------+--------------+---------+-------------+
| 123        | 5            | 10      | 2013-09-16  |
+------------+--------------+---------+-------------+

Staff then adds 3 pieces to product 234 in warehouse_id 5, and 5 pieces to 123 in warehouse_id 5,

然后工作人员在仓库_id 5中向产品234添加3件,在仓库5中添加5件到123件,

+------------+--------------+---------+-------------+
| Product_id | Warehouse_id | Balance | Date        |
+------------+--------------+---------+-------------+
| 123        | 5            | 10      | 2013-09-16  |
| 234        | 5            | 3       | 2013-09-18  |
| 123        | 5            | 15      | 2013-09-21  |
+------------+--------------+---------+-------------+

*Notice the date column

*注意日期栏

Now let me add a few more rows

现在让我再添加几行

+------------+--------------+---------+-------------+
| Product_id | Warehouse_id | Balance | Date        |
+------------+--------------+---------+-------------+
| 123        | 5            | 10      | 2013-09-16  |
| 234        | 5            | 3       | 2013-09-18  |
| 123        | 5            | 15      | 2013-09-21  |
| 123        | 5            | 18      | 2013-09-24  |
| 234        | 5            | 10      | 2013-09-26  |
| 123        | 5            | 22      | 2013-09-29  |
+------------+--------------+---------+-------------+

How do i run a query that would get me all products' balances as at 25th of September 2013?

在2013年9月25日,如何运行能够获得所有产品余额的查询?

That means i need the following result:

这意味着我需要以下结果:

+------------+--------------+---------+-------------+
| Product_id | Warehouse_id | Balance | Date        |
+------------+--------------+---------+-------------+
| 234        | 5            | 3       | 2013-09-18  |
| 123        | 5            | 18      | 2013-09-24  |
+------------+--------------+---------+-------------+

In short I need the latest row (by date), per product_id.

总之,我需要每个product_id最新的行(按日期)。

Any help would be greatly appreciated!

任何帮助将不胜感激!

3 个解决方案

#1

Assuming that products' balances are being maintained per warehouse you can do it like this

假设每个仓库维护产品的余额,你可以这样做

SELECT t.product_id, t.warehouse_id, t.balance, t.date
  FROM table1 t JOIN 
(
  SELECT warehouse_id, product_id, MAX(date) date
    FROM table1
   WHERE date <= '2013-09-25'
   GROUP BY warehouse_id, product_id
) q
    ON t.warehouse_id = q.warehouse_id
   AND t.product_id = q.product_id
   AND t.date = q.date

Output:

| PRODUCT_ID | WAREHOUSE_ID | BALANCE |       DATE |
|------------|--------------|---------|------------|
|        234 |            5 |       3 | 2013-09-18 |
|        123 |            5 |      18 | 2013-09-24 |

Here is SQLFiddle demo

这是SQLFiddle演示

#2

SELECT * 
FROM TABLE 
WHERE (PRODUCT_ID, DATE) IN
     (SELECT PRODUCT_ID, MAX(DATE) FROM TABLE
      WHERE DATE <= '2013-09-25'     
       GROUP BY PRODUCT_ID )

#3

Query:

SQLFIDDLEExample

  SELECT *
  FROM table1 t 
  WHERE t.`Date` = (SELECT MAX(t2.`Date`)
                  FROM Table1 t2
                  WHERE t2.`Date` <= '2013-09-25'
                  AND t2.product_id = t.product_id)

#1