php - 从 2 个 mysql 表中获取并显示按月记录

Question

我有 2 个 mysql 表 -
a) 承租人
b) 承租人付款

使用这两个表，我想生成特定年份所有承租人的按月记录。

我的“承租人”表是这样的:

+----+-------+--------+
| id | name  | amount |
+----+-------+--------+
|  1 | Amit  |   5000 |
|  2 | Sumit |   6000 |
|  3 | Rahul |   4000 |
|  4 | Pooja |   7000 |
|  5 | Raja  |   5000 |
+----+-------+--------+

我的“rentee_ payment”表是这样的:

+----+-----------+------------+----------------+
| id | lessee_id |    date    | collected(Y/N) |
+----+-----------+------------+----------------+
|  1 |         1 | 2016-07-05 | Y              |
|  2 |         2 | 2016-07-08 | Y              |
|  3 |         3 | 2016-07-05 | N              |
|  4 |         1 | 2016-08-05 | Y              |
|  5 |         2 | 2016-08-08 | Y              |
|  6 |         1 | 2016-09-05 | Y              |
|  7 |         2 | 2016-09-05 | Y              |
|  8 |         3 | 2016-09-08 | Y              |
|  9 |         1 | 2016-10-05 | N              |
| 10 |         2 | 2016-10-08 | Y              |
| 11 |         3 | 2016-10-07 | Y              |
| 12 |         4 | 2016-10-05 | Y              |
+----+-----------+------------+----------------+

以及我想在 html 表格中显示的按月记录:

+--------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| Lessee | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
+--------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| Amit   | -   | -   | -   | -   | -   | -   | Y   | Y   | Y   | N   | -   | -   |
| Sumit  | -   | -   | -   | -   | -   | -   | Y   | Y   | Y   | Y   | -   | -   |
| Rahul  | -   | -   | -   | -   | -   | -   | N   | -   | Y   | Y   | -   | -   |
| Pooja  | -   | -   | -   | -   | -   | -   | -   | -   | -   | Y   | -   | -   |
| Raja   | -   | -   | -   | -   | -   | -   | -   | -   | -   | -   | -   | -   |
+--------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+

问题是我无法以上述表格格式显示记录。

Answer 1

尝试这个 mysql 查询:

SELECT 
a.name, 
if(MAX(Jan), if(MAX(Jan) = 1, 'Y', 'N'), '-') AS Jan,
if(MAX(Feb), if(MAX(Feb) = 1, 'Y', 'N'), '-') AS Feb,
if(MAX(Mar), if(MAX(Mar) = 1, 'Y', 'N'), '-') AS Mar,
if(MAX(Apr), if(MAX(Apr) = 1, 'Y', 'N'), '-') AS Apr,
if(MAX(May), if(MAX(May) = 1, 'Y', 'N'), '-') AS May,
if(MAX(Jun), if(MAX(Jun) = 1, 'Y', 'N'), '-') AS Jun,
if(MAX(Jul), if(MAX(Jul) = 1, 'Y', 'N'), '-') AS Jul,
if(MAX(Aug), if(MAX(Aug) = 1, 'Y', 'N'), '-') AS Aug,
if(MAX(Sep), if(MAX(Sep) = 1, 'Y', 'N'), '-') AS Sep,
if(MAX(Oct), if(MAX(Oct) = 1, 'Y', 'N'), '-') AS Oct,
if(MAX(Nov), if(MAX(Nov) = 1, 'Y', 'N'), '-') AS Nov,
if(MAX(`Dec`), if(MAX(`Dec`) = 1, 'Y', 'N'), '-') AS `Dec`
FROM (SELECT
    lessee.id,
    lessee.name,
    if(month(lessee_payment.date) = 1, if(collected = 'Y', 1, 2), 0)  AS Jan,
    if(month(lessee_payment.date) = 2, if(collected = 'Y', 1, 2), 0)  AS Feb,
    if(month(lessee_payment.date) = 3, if(collected = 'Y', 1, 2), 0)  AS Mar,
    if(month(lessee_payment.date) = 4, if(collected = 'Y', 1, 2), 0)  AS Apr,
    if(month(lessee_payment.date) = 5, if(collected = 'Y', 1, 2), 0)  AS May,
    if(month(lessee_payment.date) = 6, if(collected = 'Y', 1, 2), 0)  AS Jun,
    if(month(lessee_payment.date) = 7, if(collected = 'Y', 1, 2), 0)  AS Jul,
    if(month(lessee_payment.date) = 8, if(collected = 'Y', 1, 2), 0)  AS Aug,
    if(month(lessee_payment.date) = 9, if(collected = 'Y', 1, 2), 0)  AS Sep,
    if(month(lessee_payment.date) = 10, if(collected = 'Y', 1, 2), 0) AS Oct,
    if(month(lessee_payment.date) = 11, if(collected = 'Y', 1, 2), 0) AS Nov,
    if(month(lessee_payment.date) = 12, if(collected = 'Y', 1, 2), 0) AS `Dec`
  FROM lessee 
  LEFT JOIN lessee_payment
  ON lessee_payment.lessee_id=lessee.id
  GROUP BY lessee.id, month(date)) AS a GROUP BY a.id