php - MySQL 排序 3 列

Question

我正在尝试对 qry_ledger_all_balance 的 MySQL 结果进行排序，它工作正常，但问题是我只能按 PaymentDate 进行排序。

我想按以下顺序排序:

1. PaymentDate 值
2. 借记值
3. 信用值

我做不到。我不知道我做错了什么。这是我的表结构示例。

+-----------------+-------+--------+---------------------+----------------------------+
|   PaymentCode   | Debit | Credit |     PaymentDate     |         Particular         |
+-----------------+-------+--------+---------------------+----------------------------+
| CTM-41700000008 | 25000 |      0 | 2017-05-15 17:27:28 | Token Money From Customer1 |
| CTM-41700000007 | 12000 |      0 | 2017-05-15 17:26:26 | Token Money From Customer2 |
| CRV-11700000166 | 15000 |      0 | 2016-05-15 17:57:01 | Customer1 Receipt Vourcher |
| EPV-21700000012 |     0 |    150 | 2017-05-15 14:23:26 | Cash Outflow               |
| EPV-21700000004 |     0 |   1110 | 2017-05-15 14:06:48 | Cash Outflow               |
| EAS-41700000001 |     0 |  10000 | 2017-05-15 12:27:47 | Employee Advance Salary    |
+-----------------+-------+--------+---------------------+----------------------------+

这是我的 PHP 查询代码

$branch_sql = "SELECT a.PaymentCode, a.Particular, a.Credit, a.Debit, 
Date(a.PaymentDate) AS PaymentDate, a.BranchID, a.CCode As RefPrint, 
a.RunningBalance, a.OpeningBalance, b.branchname FROM qry_ledger_all_balance AS a 
INNER JOIN tblbranches AS b 
ON a.BranchID = b.branchid 
WHERE DATE_FORMAT(a.PaymentDate, '%Y-%m-%d') >= '$Start_Date' AND
DATE_FORMAT(a.PaymentDate, '%Y-%m-%d') <= '$End_Date' 
ORDER BY a.PaymentDate ASC, a.Debit ASC, a.Credit ASC";

Answer 1

您的 order by 子句需要稍微聪明一些。在您的 order by 中放置一个 case 语句将允许您创建一个类似排序元素的组。

SELECT 
  a.PaymentCode, a.Particular, a.Credit, a.Debit, 
  Date(a.PaymentDate) AS PaymentDate, a.BranchID, 
  a.CCode As RefPrint, a.RunningBalance, 
  a.OpeningBalance, b.branchname 
 FROM qry_ledger_all_balance AS a 
 INNER JOIN tblbranches AS b 
   ON a.BranchID = b.branchid 
 WHERE DATE_FORMAT(a.PaymentDate, '%Y-%m-%d') 
   >= '$Start_Date' 
 AND DATE_FORMAT(a.PaymentDate, '%Y-%m-%d') 
   <= '$End_Date' 
 ORDER BY 
  date(a.PaymentDate) ASC, 
  case 
    when a.Debit > 0 
      then 0
    else 1
  end ASC, 
  case 
    when a.Debit > 0 
      then a.Debit
    else a.Credit
  end ASC