php - 如果月份相同，则PHP在同一行中获取结果

如果月份相同，我将面临在同一行中获取结果的问题。我在查询中使用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

最佳答案

你有

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值将获得一行，并且数量和恢复将包含每个rcv值的第一个非空值。