MYSQL 两个 SELECT 查询相减

Question

使用 MYSQL，我编写了两个由 UNION 组合而成的大型 SELECT 查询，以获取 2 行，其中第一行是当前月份的计数，第二行是上个月的计数。查询如下:

select * from 
(select count(*) as type1 from table_x where nationality_id = 23 and month(START_DATE) = month(now())) as t1,
(select count(*) as type2 from table_x where nationality_id = 24 and month(START_DATE) = month(now())) as t2,
(select count(*) as type3 from table_x where nationality_id = 25 and month(START_DATE) = month(now())) as t3,
(select count(*) as type4 from table_x where nationality_id = 26 and month(START_DATE) = month(now())) as t4
UNION
select * from 
(select count(*) as type1 from table_x where nationality_id = 23 and month(START_DATE) = month(now() - INTERVAL 1 MONTH)) as t1,
(select count(*) as type2 from table_x  where nationality_id = 24 and month(START_DATE) = month(now() - INTERVAL 1 MONTH)) as t2,
(select count(*) as type3 from table_x where nationality_id = 25 and month(START_DATE) = month(now() - INTERVAL 1 MONTH)) as t3,
(select count(*) as type4 from table_x where nationality_id = 26 and month(START_DATE) = month(now() - INTERVAL 1 MONTH)) as t4

我想添加第三行，这是第 2 行和第 1 行之间的差异。如何使用当前查询执行此操作？

Answer 1

您显然是在对当前月份和上个月进行比较。因此，我将从内部预查询聚合开始，仅获取那些 >= 上个月第一天的交易以及您要查找的国籍 ID 中的记录。

DAYOFMONTH() -1 day 的内部 date_sub() 为您提供当前月份的第一天。再减去一个月，即可得到最后一个月的第一个月。

现在您可以汇总每个国籍与当月相比的总数。该内部查询为您提供所有开始和结束计数。现在它被包装到外部，除了差异之外，您还可以获得所有计数...显然您可以分别更改列名称。

select
        PQ.*,
        PQ.ThisMonth23 - PQ.LastMonth23 = Diff23,
        PQ.ThisMonth24 - PQ.LastMonth24 = Diff24,
        PQ.ThisMonth25 - PQ.LastMonth25 = Diff25,
        PQ.ThisMonth26 - PQ.LastMonth26 = Diff26
   from
      ( select 
                sum( case when t.Nationality_id = 23 and month( t.StartDate ) = month( now()) then 1 else 0 end ) ThisMonth23,
                sum( case when t.Nationality_id = 24 and month( t.StartDate ) = month( now()) then 1 else 0 end ) ThisMonth24,
                sum( case when t.Nationality_id = 25 and month( t.StartDate ) = month( now()) then 1 else 0 end ) ThisMonth25,
                sum( case when t.Nationality_id = 26 and month( t.StartDate ) = month( now()) then 1 else 0 end ) ThisMonth26,
                sum( case when t.Nationality_id = 23 and month( t.StartDate ) != month( now()) then 1 else 0 end ) LastMonth23,
                sum( case when t.Nationality_id = 24 and month( t.StartDate ) != month( now()) then 1 else 0 end ) LastMonth24,
                sum( case when t.Nationality_id = 25 and month( t.StartDate ) != month( now()) then 1 else 0 end ) LastMonth25,
                sum( case when t.Nationality_id = 26 and month( t.StartDate ) != month( now()) then 1 else 0 end ) LastMonth26
            from 
                table_x t
            where
                   t.StartDate >= date_sub( date_sub( t.StartDate, interval DAYOFMONTH( t.StartDate ) -1 DAY ), interval 1 MONTH )
               AND t.Nationality_id IN ( 23, 24, 25, 26 )
        ) PQ

我只想补充一点，您的查询可能比您想象的要多...您正在询问所有记录，例如:一年中的一月无关，与一年中的所有记录十二月无关，因为您符合资格的所有记录都是基于在 MONTH() 上，没有 YEAR() 考虑。我只明确查询当前和上个月。