mysql按行循环,MySQL用循环计数行

I have the following table:

+-------------+--------------+

| product | purchased |

+-------------+--------------+

| Hammer | |

| Nipper | |

| Saw | |

| Nipper | |

| Hammer | |

| Hammer | |

| Saw | |

| Saw | |

| Saw | |

+-------------+--------------+

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

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?

解决方案

Here's the answer I posted in your other question

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