mysql : select aggregated values in between dates

Question

我有 mysql 查询(由 zf2 生成)，它看起来像

SELECT `subt`.`from` AS `from`, SUM(ordered) AS `sum_ordered`, 
SUM(income) AS `sum_income`, SUM(sales_by_payment) AS 
`sum_sales_by_payment`
FROM (
SELECT `w`.`brand` AS `brand`, `w`.`article` AS `article`, 
`w`.`subject` AS `subject`, `w`.`size` AS `size`, `w`.`from` AS             
`from`, (
SELECT SUM(IFNULL(income,0)) AS `income`) AS `income`, (
SELECT SUM(IFNULL(income_sum_price,0)) AS `income_sum_price`) AS 
`income_sum_price`, (
SELECT SUM(IFNULL(ordered,0)) AS `ordered`) AS `ordered`, (
SELECT SUM(IFNULL(ordered_sum_price,0)) AS `ordered_sum_price`) AS 
`ordered_sum_price`, (
SELECT SUM(IFNULL(ordered_max_by_day,0)) AS `ordered_max_by_day`) AS 
`ordered_max_by_day`, (
SELECT SUM(IFNULL(return_before_payment,0)) AS 
`return_before_payment`) AS `return_before_payment`, (
SELECT SUM(IFNULL(return_before_payment_sum_price,0)) AS 
`return_before_payment_sum_price`) AS 
`return_before_payment_sum_price`, (
SELECT SUM(IFNULL(sales_by_payment,0)) AS `sales_by_payment`) AS 
`sales_by_payment`, (
SELECT SUM(IFNULL(sales_by_payment_sum_price,0)) AS 
`sales_by_payment_sum_price`) AS `sales_by_payment_sum_price`, (
SELECT SUM(IFNULL(`return`,0)) AS ```return```) AS `return`, (
SELECT SUM(IFNULL(`return_sum_price`,0)) AS ```return_sum_price```) 
AS `return_sum_price`, (
SELECT SUM(IFNULL(`stock`,0)) AS ```stock```) AS `stock`, (
SELECT SUM(IF (sales_by_payment IS NOT NULL, 
sales_by_payment_sum_price / sales_by_payment, NULL)) AS 
`sale_price`) AS `sale_price`, (
SELECT SUM(income_sum_price * 0.62) AS `our_percent`) AS 
`our_percent`, (
SELECT SUM(IFNULL(income_sum_price,0) * 0.25) AS 
`cost_price_income`) AS `cost_price_select`, (
SELECT SUM(IFNULL(sales_by_payment_sum_price,0) - 
(IFNULL(income_sum_price,0) * 0.25)) AS `profit`) AS `profit`, (
 SELECT SUM(IF(income IS NOT NULL, (sales_by_payment / income) * 
 100, 0)) AS `sales_from_income`) AS `sales_from_income`, (
 SELECT (SUM(sales_by_payment) / SUM(return_before_payment)) * 100 AS 
 `returns_buyout`) AS `returns_buyout`, (
 SELECT SUM(return_before_payment) / SUM(ordered) * 100 AS `returns`) AS 
`returns`, (
SELECT SUM(sales_by_payment) / SUM(ordered) * 100 AS `buyout`) AS 
`buyout`, (
SELECT SUM(IFNULL(income,0) - IFNULL(sales_by_payment,0) + 
IFNULL(`return`,0)) AS `total`) AS `total`, (
SELECT (SUM(IFNULL(income,0)) - SUM(IFNULL(stock,0)) - 
SUM(IFNULL(`sales_by_payment`,0)) + SUM(IFNULL(`return`,0))) AS 
`in_the_way`) AS `in_the_way`
FROM `wildberries` AS `w`
WHERE `from` >= '2016-02-01' AND `end` <= '2017-05-04'
GROUP BY `article`
ORDER BY `article` DESC
LIMIT 20 OFFSET 0) AS `subt`
GROUP BY DAY(`from`)
ORDER BY `from` ASC

如果日期范围在一年之间，则一切正常。但如果我使用不同的年份，它只返回一行。谁能给我建议 - 为什么会发生这种情况以及我应该如何修改查询？

表格方案是

CREATE TABLE `wildberries` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`brand` VARCHAR(50) NULL DEFAULT NULL,
`subject` VARCHAR(50) NULL DEFAULT NULL,
`article` VARCHAR(50) NULL DEFAULT NULL,
`size` VARCHAR(50) NULL DEFAULT NULL,
`income` VARCHAR(50) NULL DEFAULT NULL,
`income_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`ordered` VARCHAR(50) NULL DEFAULT NULL,
`ordered_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`ordered_max_by_day` VARCHAR(50) NULL DEFAULT NULL,
`return_before_payment` VARCHAR(50) NULL DEFAULT NULL,
`return_before_payment_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`sales_by_payment` VARCHAR(50) NULL DEFAULT NULL,
`sales_by_payment_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`return` VARCHAR(50) NULL DEFAULT NULL,
`return_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`stock` VARCHAR(50) NULL DEFAULT NULL,
`source_file` VARCHAR(50) NULL DEFAULT NULL,
`from` DATE NULL DEFAULT NULL,
`end` DATE NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `article` (`article`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Answer 1

在子查询中，您按article列进行分组，但选择了from列。您无需告诉 DBMS 您想查看文章的哪一篇。根据 SQL 标准，这是无效的，但 MySQL 忽略了这一点，并为您提供任意选择的文章摘录之一。

因此，对于前 20 篇文章，您选择的日期相当随机。

在您的主查询中，您按这些日期的天数部分进行分组。这可能会导致 1 到 20 行，并且基于巧合(即当时记录如何恰好存储在磁盘上以及 DBMS 如何检索它们)。