if ($result = $db->query('SELECT id, name, price FROM product '
. 'WHERE status = "1"'
. 'ORDER BY id DESC '
. 'LIMIT ' . $count)) {
// твой код
} else {
else echo($db->error);
}
SELECT doc1Groupped.maxId, doc1Groupped.maxVersion, doc2.id
FROM (
SELECT MAX(doc1.id) as maxId, MAX(doc1.version) as maxVersion
FROM doc1
GROUP BY doc1.date, doc1.title
) as doc1Groupped
JOIN doc2 ON doc1Groupped.maxId = doc2.id
WHERE doc2.id is NULL OR doc2.history = ''
SELECT field1, field2, field3 FROM tableName as tMain
INNER JOIN (
SELECT field1, MIN(field2) as field2 FROM tableName GROUP BY field1
) as minTotals ON tMain.field1 = minTotals.field1 AND tMain.field2 = minTotals.field2
select ids.id, t1.deal_id, t2.track_id, t3.waybill_id from
(select '9014' as id) as ids
left join table_1 as t1 on ids.id = t1.item_id
left join table_2 as t2 on ids.id = t2.item_id
left join table_3 as t3 on ids.id = t3.item_id
select ids.id, t1.deal_id, t2.track_id, t3.waybill_id from
(select t1.item_id as id from table_1 union t2.item_id from table_2 union t3.item_id from table_3) as ids
left join table_1 as t1 on ids.id = t1.item_id
left join table_2 as t2 on ids.id = t2.item_id
left join table_3 as t3 on ids.id = t3.item_id
SELECT
timeStart, timeEnd, ROUND(IFNULL(seconds, 0) / 3600 * 100) as percent
FROM
(SELECT
timeStart,
timeEnd,
SUM(3600 - CASE
WHEN UNIX_TIMESTAMP(timeStarted) - UNIX_TIMESTAMP(timeStart) < 0 THEN 0
ELSE UNIX_TIMESTAMP(timeStarted) - UNIX_TIMESTAMP(timeStart) END - CASE
WHEN UNIX_TIMESTAMP(timeEnd) - UNIX_TIMESTAMP(timeStopped) < 0 THEN 0
ELSE UNIX_TIMESTAMP(timeEnd) - UNIX_TIMESTAMP(timeStopped) END) as seconds
FROM (
SELECT '2020-06-23 00:00:00' as timeStart, '2020-06-23 00:59:59' as timeEnd
UNION SELECT '2020-06-23 01:00:00', '2020-06-23 01:59:59'
UNION SELECT '2020-06-23 02:00:00', '2020-06-23 02:59:59'
UNION SELECT '2020-06-23 03:00:00', '2020-06-23 03:59:59'
UNION SELECT '2020-06-23 04:00:00', '2020-06-23 04:59:59'
UNION SELECT '2020-06-23 05:00:00', '2020-06-23 05:59:59'
UNION SELECT '2020-06-23 06:00:00', '2020-06-23 06:59:59'
// и т.д.
) as HoursSet
LEFT JOIN (
SELECT MAX(IFNULL(StartedPoints.time, '2020-06-23 00:00:00')) as timeStarted, StoppedPoints.time as timeStopped FROM `stanki` as StoppedPoints
LEFT JOIN stanki as StartedPoints
ON StoppedPoints.name = StartedPoints.name AND
StartedPoints.value = 'ACTIVE' AND
StoppedPoints.time > StartedPoints.time
WHERE StoppedPoints.name = '01' AND StoppedPoints.value IN ('STOPPED', 'UNAVAILABLE')
GROUP BY StoppedPoints.time
UNION ALL
SELECT time, '2020-06-24 00:00:00'
FROM (SELECT * FROM stanki WHERE name = '01' ORDER BY time DESC LIMIT 1) as LastRecord
WHERE value = 'ACTIVE'
) as ActiveIntervals ON ActiveIntervals.timeStopped > HoursSet.timeStart
AND ActiveIntervals.timeStarted <= HoursSet.timeEnd
GROUP BY timeStart, timeEnd) as CalculatedSeconds
// После добавления записи в банк
CREATE TRIGGER `cash_movemenets` AFTER INSERT ON `cash`
FOR EACH ROW BEGIN
INSERT INTO movemenets Set date = NEW.date, income = NEW.sum;
END
// После добавления записи в кассу
CREATE TRIGGER `bank_movemenets` AFTER INSERT ON `bank`
FOR EACH ROW BEGIN
INSERT INTO movemenets Set date = NEW.date, income = NEW.transfer;
END
SELECT pairs.startDate, pairs.endDate, pairs.endDate - pairs.startDate + 1 as count FROM
(SELECT t1.date as startDate, MIN(t3.date) as endDate FROM statistics as t1
LEFT JOIN statistics t2 ON t1.date - t2.date = 1 and t2.done = 1
LEFT JOIN statistics as t3 ON t1.date <= t3.date and t3.done = 1
LEFT JOIN statistics as t4 ON t3.date - t4.date = -1 and t4.done = 1
WHERE t1.done = 1 AND ISNULL(t2.date) AND ISNULL(t4.date)
GROUP BY t1.date) as pairs
ORDER BY pairs.startDate
select * from CharacteristicsValues where propertyId = 1 and value = 1
(
SELECT SUM(total_paid_real / conversion_rate)
FROM ps_orders o
WHERE o.id_customer = a.id_customer
AND o.id_shop IN (1)
AND o.valid = 1
) as total_spent,
SELECT SQL_NO_CACHE a.`id_customer`,
`firstname`,
`lastname`,
`email`,
a.`active` AS `active`,
`newsletter`,
`optin`,
a.date_add,
gl.name as title,
total_spents.total_spent
FROM `ps_customer` a
LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1)
LEFT JOIN (
SELECT o.id_customer, SUM(total_paid_real / conversion_rate) as total_spent
FROM ps_orders o
WHERE o.id_customer IN (SELECT id_customer FROM `ps_customer` a WHERE a.deleted = 0)
AND o.id_shop IN (1)
AND o.valid = 1
GROUP BY o.id_customer
) total_spents ON a.id_customer = total_spents.id_customer,
WHERE 1
AND a.`deleted` = 0
ORDER BY `date_add` DESC
LIMIT 0, 50