如果月份相同，PHP 将得到相同行的结果

Question

如果月份相同，我面临着在同一行中获得结果的问题。我在查询中使用 UNION ALL 。任何人都可以建议如何使用查询来根据我的要求获取结果。

我正在使用以下代码:

SELECT 
   SUM(amount) AS amount,
   DATE_FORMAT(rcvdate,'%M %Y') as rcv,
   NULL AS recovery 
FROM `advertisercv` 
GROUP BY DATE_FORMAT(rcvdate, '%M %Y')
UNION ALL 
SELECT 
   NULL,
   DATE_FORMAT(date,'%M %Y') AS rcv,
   SUM(amount) 
FROM `paymentsrec` 
GROUP BY DATE_FORMAT(date, '%M %Y') 
ORDER BY rcv

Answer 1

你有

SELECT
  SUM(amount) as amount,
  DATE_FORMAT(rcvdate,'%M %Y') as rcv,
  null as recovery 
FROM `advertisercv` 
GROUP BY DATE_FORMAT(rcvdate, '%M %Y')
UNION all 
SELECT 
  null,
  DATE_FORMAT(date,'%M %Y') as rcv,
  SUM(amount) 
FROM `paymentsrec`
GROUP BY DATE_FORMAT(date, '%M %Y') 
order by rcv

为了使其更易于使用，我强烈建议为每个子选择的列指定相同的名称:

SELECT
  SUM(amount) as amount,
  DATE_FORMAT(rcvdate,'%M %Y') as rcv,
  null as recovery 
FROM `advertisercv` 
GROUP BY DATE_FORMAT(rcvdate, '%M %Y')
UNION all 
SELECT 
  null as amount,
  DATE_FORMAT(date,'%M %Y') as rcv,
  SUM(amount) as recovery
FROM `paymentsrec`
GROUP BY DATE_FORMAT(date, '%M %Y') 
order by rcv

现在您可以看到您将获得包含 amount、rcv、recovery 的多行。您希望这些行按 rcv 分组:

SELECT * FROM (
  SELECT
    SUM(amount) as amount,
    DATE_FORMAT(rcvdate,'%M %Y') as rcv,
    null as recovery 
  FROM `advertisercv` 
  GROUP BY DATE_FORMAT(rcvdate, '%M %Y')
  UNION all 
  SELECT 
    null as amount,
    DATE_FORMAT(date,'%M %Y') as rcv,
    SUM(amount) as recovery
  FROM `paymentsrec`
  GROUP BY DATE_FORMAT(date, '%M %Y') 
  order by rcv
) GROUP BY rcv

但是，* 不是群运算符。当多行具有相同的 rcv 时我们该怎么办？你可以将它们相加。您可以获得最大值或最小值。从您表达问题的方式来看，您似乎想要合并 - 第一个非空值。

SELECT
  rcv,
  COALESCE(amount) as amount,
  COALESCE(recover) as recovery
FROM (
  SELECT
    SUM(amount) as amount,
    DATE_FORMAT(rcvdate,'%M %Y') as rcv,
    null as recovery 
  FROM `advertisercv` 
  GROUP BY DATE_FORMAT(rcvdate, '%M %Y')
  UNION all 
  SELECT 
    null as amount,
    DATE_FORMAT(date,'%M %Y') as rcv,
    SUM(amount) as recovery
  FROM `paymentsrec`
  GROUP BY DATE_FORMAT(date, '%M %Y') 
  order by rcv
) GROUP BY rcv

现在，您将获得每个 rcv 值一行，并且 amount 和 recovery 都将包含每个 rcv 值的第一个非空值。