MySQL用循环计数行 [英] mysql counting rows with loop

问题描述

我有下表:

+-------------+--------------+ 
| product     | purchased    |
+-------------+--------------+ 
| Hammer      | <timestamp>  |
| Nipper      | <timestamp>  |
| Saw         | <timestamp>  |
| Nipper      | <timestamp>  |
| Hammer      | <timestamp>  |
| Hammer      | <timestamp>  |
| Saw         | <timestamp>  |
| Saw         | <timestamp>  |
| Saw         | <timestamp>  |
+-------------+--------------+

我想查看有关这些产品的保修状态的摘要.保修期为自购买之日起的5年.因此，从今天起，应该可以检查产品是否在保修期内(基于购买日期).我也想现在最后购买的产品将不在保修范围内.这是一个示例表:

I would like to see a summary about the warranty status of these products. The Warranty should be 5 years from the purchase date. So with the date of today it should be possible to check if a product is under warranty or not (base on the purchase date). Also I would like to now when the last bought product will be out of warranty. Here is a example table:

+-------------+--------------+----------------+------------------+------------------+
| product     | count        | warranty valid | warranty expired | last p. warranty |
+-------------+--------------+----------------+------------------+------------------+
| Hammer      | 3            | 1              | 2                | 10.03.2015       |
| Nipper      | 2            | 2              | -                | 01.01.2014       |
| Saw         | 4            | 1              | 3                | 02.12.2013       |
+-------------+--------------+----------------+------------------+------------------+

我试图为该示例创建查询，但是我不知道如何循环显示每个产品的结果以计算有效保修等.这就是我目前所拥有的:

I tried to create the query for this example, but I didn't know how to loop the results for every product to count valid warrantys and so on. That's what I have at the moment:

SELECT
    product,
    date_format(from_unixtime(purchased), '%d.%m.%Y') AS purchaseDate,
    date_format(date_add(from_unixtime(purchased), INTERVAL 5 YEAR), '%d.%m.%Y') AS warrantyEnd,
    (
        SELECT
            COUNT(product)
        FROM
            productWarranty
    ) AS count
FROM
    productWarranty s
GROUP BY
    product

我不知道如何编写子查询，因为我需要使用WHERE product = Hammer之类的东西.但是如何告诉sql为每个产品循环?

I don't know how to write the subquery because, I need to use something like WHERE product = Hammer. But how can I tell sql to loop for every single product?

推荐答案

这是我在您的 查看全文