MySQL - 尝试为每个不同的日期返回一行；显示现金、信用和总计

Question

我试图将每个唯一的日期合并到一行中。我得到的是现金总额作为 1 行返回，然后信用总额作为另一行返回。如何将这些行合并为按日期分组的单行？

请参阅下面我的查询:

SELECT 
DISTINCT orders.service_date,

/* CASH TOTAL */
CASE WHEN
customer.payment_type = "Cash"
THEN

CONCAT('$',
(SELECT   

SUM(
    ((CASE
    WHEN
      orders.service= 'MT'
        THEN
          cast(customer.mtrate as DECIMAL(10,2))
    WHEN
      orders.service= 'MTF'
        THEN
          cast(customer.mtfrate as DECIMAL(10,2))
    WHEN
      orders.service= 'MTB'
        THEN
          cast(customer.mtbrate as DECIMAL(10,2))
  END) + cast(((orders.cu + orders.pw + orders.r + orders.lr + orders.misc) * 0.80) as DECIMAL(10,2)))))) 

  ELSE 
  " - "   
  END as Cash,


  /* CREDIT TOTAL */
  CASE WHEN
customer.payment_type = "Credit"
THEN

CONCAT('$',
(SELECT   

SUM(
    ((CASE
    WHEN
      orders.service= 'MT'
        THEN
          cast(customer.mtrate as DECIMAL(10,2))
    WHEN
      orders.service= 'MTF'
        THEN
          cast(customer.mtfrate as DECIMAL(10,2))
    WHEN
      orders.service= 'MTB'
        THEN
          cast(customer.mtbrate as DECIMAL(10,2))
  END) + cast(((orders.cu + orders.pw + orders.r + orders.lr + orders.misc) * 0.80) as DECIMAL(10,2)))))) 

  ELSE 
  " - "   
  END as Credit,

  /* GRAND TOTAL */

  CONCAT('$',
(SELECT   

SUM(
    ((CASE
    WHEN
      orders.service= 'MT'
        THEN
          cast(customer.mtrate as DECIMAL(10,2))
    WHEN
      orders.service= 'MTF'
        THEN
          cast(customer.mtfrate as DECIMAL(10,2))
    WHEN
      orders.service= 'MTB'
        THEN
          cast(customer.mtbrate as DECIMAL(10,2))
  END) + cast(((orders.cu + orders.pw + orders.r + orders.lr + orders.misc) * 0.80) as DECIMAL(10,2)))))) as Total

FROM finalproject.orders

INNER JOIN finalproject.customer
USING(customerid)

GROUP BY customer.payment_type, orders.service_date
ORDER BY orders.service_date ASC

返回的是:

大多数查询你可以忽略——算术等等......我是MySQL新手......任何建议都值得赞赏。谢谢!

Answer 1

你很接近。仅按 service_date 分组，因为您希望每个 service_date 一个结果行。然后将 payment_type 包含在条件聚合中，即在 SUM 中。

SELECT
  o.service_date,
  SUM(CASE WHEN c.payment_type = 'Cash' THEN
        CASE o.service
          WHEN 'MT'  THEN CAST(c.mtrate AS DECIMAL(10,2))
          WHEN 'MTF' THEN CAST(c.mtfrate AS DECIMAL(10,2))
          WHEN 'MTB' THEN CAST(c.mtbrate AS DECIMAL(10,2))
        END + 
        CAST(((o.cu + o.pw + o.r + o.lr + o.misc) * 0.80) AS DECIMAL(10,2))
      END
  ) AS cash,
  SUM(CASE WHEN c.payment_type = 'Credit' THEN
        CASE o.service
          WHEN 'MT'  THEN CAST(c.mtrate AS DECIMAL(10,2))
          WHEN 'MTF' THEN CAST(c.mtfrate AS DECIMAL(10,2))
          WHEN 'MTB' THEN CAST(c.mtbrate AS DECIMAL(10,2))
        END + 
        CAST(((o.cu + o.pw + o.r + o.lr + o.misc) * 0.80) AS DECIMAL(10,2))
      END
  ) AS credit,
  SUM(CASE o.service
        WHEN 'MT'  THEN CAST(c.mtrate AS DECIMAL(10,2))
        WHEN 'MTF' THEN CAST(c.mtfrate AS DECIMAL(10,2))
        WHEN 'MTB' THEN CAST(c.mtbrate AS DECIMAL(10,2))
      END + 
      CAST(((o.cu + o.pw + o.r + o.lr + o.misc) * 0.80) AS DECIMAL(10,2))
  ) AS total
FROM finalproject.orders o
JOIN finalproject.customer c USING (customerid)
GROUP BY o.service_date
ORDER BY o.service_date;