MySQL用循环计数行

Question

我有下表：

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

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

+-------------+--------------+----------------+------------------+------------------+
| 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       |
+-------------+--------------+----------------+------------------+------------------+

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

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为每个产品循环？

Answer 1

这是我在其他问题中发布的答案

SELECT  p2c.pid AS productNumber,
        p.name AS productName,
        COUNT(*) AS registered,
        SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()) AS inWarranty,
        SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) < CURDATE()) AS outOfWarranty,
        DATE_FORMAT( MAX( from_unixtime(purchased) ), '%d.%m.%Y') AS lastPurchased,
        DATE_FORMAT( date_add( MAX( from_unixtime(purchased) ), INTERVAL 5 YEAR), '%d.%m.%Y') AS warrantyUntil
FROM products2customers p2c
JOIN products p ON p.id = p2c.pid
GROUP BY p2c.pid
ORDER BY inWarranty DESC