SELECT `o`.`ID`, IFNULL(`op`.`VALUE`, 0) AS `prepayment`, IFNULL(`ef`.`VALUE`, 0) AS `payment`
FROM `b_sale_order` AS `o`
LEFT JOIN (
SELECT `order_id`, SUM(`VALUE`) AS `VALUE`
FROM `d_sale_order_prepayment`
GROUP BY `order_id`
) AS `op` ON `op`.`order_id` = `o`.`ID`
LEFT JOIN (
SELECT `order_id`, SUM(`VALUE`) AS `VALUE`
FROM `d_sale_order_excess_fare`
GROUP BY `order_id`
) AS `ef` ON `ef`.`order_id` = `o`.`ID`SET @id = 0;
UPDATE `test` SET `lock` = 1 WHERE @id := `id` AND `lock` = 0 ORDER BY `time` LIMIT 1;
SELECT @id;SELECT `t1`.`item_id`
FROM `table` AS `t1`
JOIN `table` AS `t2` USING(`item_id`)
JOIN `table` AS `t3` USING(`item_id`)
WHERE `t1`.`property_id` = 2 AND `t1`.`value_id` = 3
AND `t2`.`property_id` = 2 AND `t2`.`value_id` = 5
AND `t3`.`property_id` = 1 AND `t3`.`value_id` = 4SELECT *
FROM (
SELECT DISTINCT `date_create`
FROM `ordres`
UNION SELECT `date_modified`
FROM `ordres`
WHERE `status` IN (2, 3)
) AS `date`
LEFT JOIN (
SELECT COUNT(*) AS `count`, `date_create`
FROM `ordres`
GROUP BY `date_create`
) AS `all` ON `all`.`date_create` = `date`.`date_create`
LEFT JOIN (
SELECT COUNT(*) AS `count`, `date_modified`
FROM `ordres`
WHERE `status` = 2
GROUP BY `date_modified`
) AS `succ` ON `succ`.`date_modified` = `date`.`date_create`
LEFT JOIN (
SELECT COUNT(*) AS `count`, `date_modified`
FROM `ordres`
WHERE `status` = 3
GROUP BY `date_modified`
) AS `canc` ON `canc`.`date_modified` = `date`.`date_create`SELECT `t3`.`id_member`, `t3`.`month`, `t3`.`count`
FROM (
SELECT `month`, MAX(`count`) AS `maxcount`
FROM (
SELECT DATE_FORMAT(FROM_UNIXTIME(`poster_time`),'%Y-%m') AS `month`,
COUNT(*) AS `count`
FROM `messages`
GROUP BY `id_member`, `month`
) AS `t1`
GROUP BY `month`
) AS `t2`
JOIN (
SELECT DATE_FORMAT(FROM_UNIXTIME(`poster_time`),'%Y-%m') AS `month`,
COUNT(*) AS `count`, `id_member`
FROM `messages`
GROUP BY `id_member`, `month`
) AS `t3` ON `t3`.`month` = `t2`.`month`
AND `t3`.`count` = `t2`.`maxcount`
ORDER BY `t3`.`month`SELECT `a`.`name`, IFNULL(`d`.`count`, 0) AS `count`
FROM `authors` AS `a`
LEFT JOIN (
SELECT `author`, COUNT(*) AS `count`
FROM `docs`
GROUP BY `author`
) AS `d` ON `d`.`author` = `a`.`id`
UNION (SELECT CONCAT('Неизвестный ', IFNULL(`d`.`author`, '')), `d`.`count`
FROM `authors` AS `a`
RIGHT JOIN (
SELECT `author`, COUNT(*) AS `count`
FROM `docs`
GROUP BY `author`
) AS `d` ON `d`.`author` = `a`.`id`
WHERE `a`.`id` IS NULL)
ORDER BY `count` DESC