У меня есть следующая таблица:
+-------------+--------------+ | 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 | +-------------+--------------+----------------+------------------+------------------+
Я попытался создать запрос для этого примера, но я не знал, как зацикливать результаты для каждого продукта, чтобы подсчитать действительный warrantys и так далее. Это то, что у меня есть на данный момент:
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 для цикла для каждого отдельного продукта?
Вот ответ, который я написал в вашем другом вопросе
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
Что вам нужно для работы, является ли гарантия действительной или истекшей и датой гарантии, поэтому вам сначала нужно создать таблицу, которая отражает это:
select product , IF( warranty >= NOW(), 1, 0 ) as valid , IF( warranty < NOW(), 1, 0 ) as expired , warranty as last from ( select product , ADDDATE( purchased, INTERVAL 5 YEAR ) as warranty from productWarranty ) w group by product ;
Это даст вам нечто подобное:
+---------+-------+---------+---------------------+ | product | valid | expired | warranty | +---------+-------+---------+---------------------+ | Hammer | 1 | 0 | 2017-01-01 00:00:00 | | Nipper | 1 | 0 | 2017-01-01 00:00:00 | | Nipper | 1 | 0 | 2017-01-01 00:00:00 | | Nipper | 1 | 0 | 2017-01-01 00:00:00 | | Saw | 1 | 0 | 2017-01-01 00:00:00 | | Saw | 0 | 1 | 2011-01-01 00:00:00 | | Saw | 1 | 0 | 2017-01-01 00:00:00 | | Saw | 1 | 0 | 2017-01-01 00:00:00 | +---------+-------+---------+---------------------+
Затем используйте агрегированные функции для фильтрации и суммирования информации, которую вы ищете:
select product , SUM( IF( warranty >= NOW(), 1, 0 ) ) as valid , SUM( IF( warranty < NOW(), 1, 0 ) ) as expired , MAX( warranty ) as last from ( select product , adddate( purchased, interval 5 year ) as warranty from productWarranty ) w group by product ;
+---------+-------+---------+---------------------+ | product | valid | expired | last | +---------+-------+---------+---------------------+ | Hammer | 1 | 0 | 2017-01-01 00:00:00 | | Nipper | 3 | 0 | 2017-01-01 00:00:00 | | Saw | 3 | 1 | 2017-01-01 00:00:00 | +---------+-------+---------+---------------------+
Лучшая нормализация ускорит этот запрос чрезвычайно, но вот пример работы с:
Во-первых, вам нужно начать с базового запроса, который будет вести результаты. В этом случае это список всех продуктов в вашей таблице:
SELECT DISTINCT product FROM productWarranty
Вышеприведенный запрос создает своего рода таблицу «temp», на которую мы можем основывать наши запросы. Если у вас есть отдельная нормализованная таблица продуктов, это будет намного лучше.
Остальные столбцы могут быть просто подзапросами, управляемыми вашей базовой таблицей:
Общее количество:
SELECT COUNT(*) FROM productWarranty WHERE product = 'Hammer'
Общие действующие гарантии:
SELECT COUNT(*) FROM productWarranty WHERE product = 'Hammer' AND purchased >= <warranty cut-off date>
Общие истекшие гарантии:
SELECT COUNT(*) FROM productWarranty WHERE product = 'Hammer' AND purchased < <warranty cut-off date>
Последняя дата покупки:
SELECT MAX( purchased ) FROM productWarranty WHERE product = 'Hammer'
Теперь, чтобы объединить все это в один запрос:
SELECT Base.product , ( SELECT COUNT(*) FROM productWarranty WHERE product = Base.product ) AS TotalCount , ( SELECT COUNT(*) FROM productWarranty WHERE product = Base.product AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE() ) AS ValidWarrantyCount , ( SELECT COUNT(*) FROM productWarranty WHERE product = Base.product AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) < CURDATE() ) AS ExpiredWarrantyCount , ( SELECT MAX( purchased ) FROM productWarranty WHERE product = Base.product ) AS LastPurchased FROM ( SELECT DISTINCT product FROM productWarranty ) AS Base