问题描述
我有下表:
+-------------+--------------+
| 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?
推荐答案
这是我在您的
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
这篇关于MySQL用循环计数行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!