between来表示日期？

0kjbasz6 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(231)

我需要一个两个不同的值相同的列，但在不同的日期范围，这是我正在做的

SELECT `sales-store-id`,
((SUM (`gen-acute-sales-after-returns`) AS `totalGenericSales` ,
(SUM(`eth-acute-sales-after-returns`) AS `totalEthicalSales`, 
SUM(`sales-after-returns`) AS `totalSales` WHERE `date-s` BETWEEN '2018-11-01' AND '2018-11-12') 
AND
  ((SUM (`gen-acute-sales-after-returns`)) AS `totalGenericSalesPM` ,
(SUM(`eth-acute-sales-after-returns`)) AS `totalEthicalSalesPM`,
 SUM(`sales-after-returns`) AS `totalSalesPM` 
FROM `sales` WHERE `date-s` BETWEEN '2018-10-01' AND '2018-10-12') 
GROUP BY`sales-store-id`

它是工作时，为单一日期范围，但添加多个日期范围后，它给出语法错误

mysql where-clause Between

来源：https://stackoverflow.com/questions/53259419/how-to-use-multiple-where-between-for-date-in-same-column-in-mysql

2条答案

按热度按时间

3pmvbmvn1#

使用union all对不同日期范围的子查询进行分组可以实现这一点。

SELECT `sales-store-id`,
SUM(GenericSales) AS TotalGenericSales,
SUM(EthicalSales) AS TotalEthicalSales,
SUM(Sales) AS TotalSales,
SUM(GenericSalesPM) AS TotalGenericSalesPM,
SUM(EthicalSalesPM) AS TotalEthicalSalesPM,
SUM(SalesPM) AS TotalSalesPM
FROM
(
  SELECT `sales-store-id`,
  `gen-acute-sales-after-returns` AS GenericSales,
  `eth-acute-sales-after-returns` AS EthicalSales, 
  `sales-after-returns` AS Sales,
  0.0 AS GenericSalesPM, 
  0.0 AS EthicalSalesPM, 
  0.0 AS SalesPM
  FROM `sales` 
  WHERE `date-s` BETWEEN '2018-11-01' AND '2018-11-12' 

  UNION ALL

  SELECT `sales-store-id`, 0, 0, 0,
  `gen-acute-sales-after-returns`,
  `eth-acute-sales-after-returns`,
  `sales-after-returns` 
  FROM `sales`
  WHERE `date-s` BETWEEN '2018-10-01' AND '2018-10-12'
) q
GROUP BY `sales-store-id`

赞(0）回复(0）举报 2021-06-18

snz8szmq2#

您可以尝试使用条件聚合

SELECT `sales-store-id`,
SUM (case when `date-s` BETWEEN '2018-11-01' AND '2018-11-12' then `gen-acute-sales-after-returns` end) AS `totalGenericSales` ,
SUM(case when `date-s` BETWEEN '2018-11-01' AND '2018-11-12' then `eth-acute-sales-after-returns` end) AS `totalEthicalSales`, 
SUM(case when `date-s` BETWEEN '2018-11-01' AND '2018-11-12' then `sales-after-returns` end) AS `totalSales`,  
SUM (case when `date-s` BETWEEN '2018-10-01' AND '2018-10-12' then `gen-acute-sales-after-returns` end ) AS `totalGenericSalesPM` ,
SUM(case when `date-s` BETWEEN '2018-10-01' AND '2018-10-12' then `eth-acute-sales-after-returns` end) AS `totalEthicalSalesPM`,
SUM(case when `date-s` BETWEEN '2018-10-01' AND '2018-10-12' then `sales-after-returns` end) AS `totalSalesPM` 
FROM `sales` 
GROUP BY`sales-store-id`

赞(0）回复(0）举报 2021-06-18

我来回答

between来表示日期？

2条答案

相关问题

热门标签

最新问答