mysql查询-查找不在多个范围内的总和(evergreen)

rwqw0loc 于 2021-06-18 发布在 Mysql

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

我需要从mysql常绿周期（total-some prmo periods）获取一些数据；

SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND YEAR(trxDate) = '2018'

总计：462

SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2017-03-03' AND trxDate <= '2017-09-12'

SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-09-11' AND trxDate <= '2019-01-03'

SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-07-01' AND trxDate <= '2018-09-04'

218

SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-08-01' AND trxDate <= '2018-08-18'

SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-07-01' AND trxDate <= '2018-07-31'

SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-06-15' AND trxDate <= '2018-06-30'

2
现在我需要获取total-（all periods）我的查询是

SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND  1 AND (trxDate < '2018-09-11' OR trxDate > '2018-10-25')  AND (trxDate < '2018-07-01' OR trxDate > '2018-09-04')  AND (trxDate < '2018-08-01' OR trxDate > '2018-08-18')  AND (trxDate < '2018-07-01' OR trxDate > '2018-07-31')  AND (trxDate < '2018-06-15' OR trxDate > '2018-06-30')

我想要的输出是
462-0-5-218-4-4-2=229但我得到237
当我对1或2个日期范围进行查询时，它会显示很好的结果，但总的来说不会。
我的问题是什么。

mysql Database

来源：https://stackoverflow.com/questions/52989128/mysql-query-find-sum-not-in-multiple-range-evergreen

2条答案

按热度按时间

lp0sw83n1#

为什么不把所有的查询合并在一起得到最终的总和呢？

SELECT SUM(tUniquePageViews) FROM
(
  SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND YEAR(trxDate) = '2018' 
  union all
  SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2017-03-03' AND trxDate <= '2017-09-12' 
  union all
  SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-09-11' AND trxDate <= '2019-01-03' 
  union all
  SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-07-01' AND trxDate <= '2018-09-04' 
  union all
  SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-08-01' AND trxDate <= '2018-08-18'
  union all
  SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-07-01' AND trxDate <= '2018-07-31'
  union all
  SELECT SUM(view) AS tUniquePageViews FROM store_report_per_day WHERE tpi = 'SE0541' AND trxDate >= '2018-06-15' AND trxDate <= '2018-06-30' 
) a;

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

bvjxkvbb2#

您可以尝试使用条件聚合

SELECT 
    SUM(case when YEAR(trxDate) = '2018' then view end)- 
    SUM(case when trxDate >= '2017-03-03' AND trxDate <= '2017-09-12' then view end) - 
    SUM(case when trxDate >= '2018-09-11' AND trxDate <= '2019-01-03' then view end)-
    SUM(case when trxDate >= '2018-07-01' AND trxDate <= '2018-09-04' then view end)-
    SUM(case when trxDate >= '2018-08-01' AND trxDate <= '2018-08-18' then view end)-
    SUM(case when trxDate >= '2018-07-01' AND trxDate <= '2018-07-31' then view end)-
    SUM(case when trxDate >= '2018-06-15' AND trxDate <= '2018-06-30' then view end)
    AS tUniquePageViews 
    FROM store_report_per_day WHERE tpi = 'SE0541'

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

我来回答

mysql查询-查找不在多个范围内的总和(evergreen)

2条答案

相关问题

热门标签

最新问答