mysql - 如何获取按日期范围列出在不同行中的mysql结果？

Question

我很难在页面上正确显示数据。我正在运行一个账单报告查询，该报告搜索所有未结帐单，我想将它们显示在一个页面上，并按余额的年龄显示未结余额。因此，如果客户 ABC 在过去一年有 10 张发票，其中一些有未清余额，我想以 0-30/30-60/60-90/和 90 天以上的列显示这些余额。这是我所拥有的...

    $sql17 = "SELECT $mysql_billing.primary_key, $mysql_billing.a_name, $mysql_billing.invoicedate, $mysql_billing.finaltotal, $mysql_billing_dates.paidtotal,
 ($mysql_billing.finaltotal - $mysql_billing_dates.paidtotal) AS total
 FROM
   (SELECT $mysql_billing.primary_key, $mysql_billing.a_name, $mysql_billing.login_username, $mysql_billing.invoicedate,
   SUM($mysql_billing.custotal) AS finaltotal 
   FROM $mysql_billing where $today >= invoicedate AND $start_search_prev180_date <= invoicedate AND custotal != payments GROUP BY login_username) $mysql_billing 
 LEFT JOIN 
   (SELECT $mysql_billing_dates.id, $mysql_billing_dates.username, 
   SUM($mysql_billing_dates.amount) AS paidtotal 
   FROM $mysql_billing_dates where complete != 'yes' GROUP BY username) $mysql_billing_dates 
 ON ($mysql_billing.login_username = $mysql_billing_dates.username) 
 GROUP BY a_name ORDER BY a_name ASC";

这似乎打印出了结果，但是如何根据多个日期变量将结果放入不同的列中？我必须执行 4 次单独的查询吗？

Answer 1

您的 SQL Fiddle 很接近，但您的一些联接不正确。

SELECT 
  billing.a_name, 
  billing.finaltotal, 
  billing_dates.paidtotal,
  (billing.finaltotal - billing_dates.paidtotal) AS total,
   billing.TimePeriod
FROM
   (SELECT 
      a_name, 
      login_username, 
      SUM(custotal) AS finaltotal,
      CASE 
        WHEN invoicedate > DATE_SUB(CURDATE(),INTERVAL 30 DAY) THEN '30'
        WHEN invoicedate > DATE_SUB(CURDATE(),INTERVAL 60 DAY) THEN '60'
        WHEN invoicedate > DATE_SUB(CURDATE(),INTERVAL 90 DAY) THEN '90'
        ELSE '90+'
      END AS TimePeriod

    FROM billing 
    where CURDATE() >= invoicedate 
      AND CAST(20120601 AS DATETIME) <= invoicedate 
    GROUP BY login_username) billing 
 LEFT JOIN 
   (SELECT 
      username, 
      SUM(amount) AS paidtotal 
    FROM billing_dates
    GROUP BY username) billing_dates 
   ON (billing.a_name = billing_dates.username) 
ORDER BY a_name ASC;

这是更新后的Fiddle .