mysql - 将行转换为列 - 从同一个表查询

Question

我有两个查询从同一个表中获取数据。第一个查询获取状态为“已支付”的数据，而第二个查询获取当月的发票。

第一个查询(获取以前的已付发票):

SELECT 
date as 'datePd', invAmt as 'invAmtPd', refNum as 'refNumPd'

FROM inv

WHERE cst = "Customer"
AND status = "Paid"

输出是:

    |   datePd   |  invAmtPd    |   refNumPd    |
    | 11-20-2016 |  1500        |   0001        |
    | 12-20-2016 |  5500        |   0002        |   

第二个查询(获取当月的最新发票)

SELECT 
date as dateCur, refNum as 'refNumCur', invAmt as 'invAmtCur'

FROM inv

WHERE cst = "Customer" AND YEAR(date) = "2017" AND MONTH(date) = "01"

输出是:

    |   dateCur      |  refNumCur    |  invAmtCur   |
    |   2017-01-05   |  0003         |  2500        |

我正在尝试实现此输出，其中它将显示前一个查询的列和当前查询的数据。

    |   datePd   |  invAmtPd    |   refNumPd    |   refNumCur    |  invAmtCur   |
    | 11-20-2016 |  1500        |   0001        |   0003         |  2500        |
    | 12-20-2016 |  5500        |   0002        |                |              |

实现此输出的最佳方式是什么？我尝试使用 UNION 但这只是导致将第二个查询的结果附加到底部......

注意:之前支付的金额是去年的，最新的是2017年的，如果可以的话最好也输出之前的。

Answer 1

像这样的事情怎么样:

SELECT past.date as 'datePd', past.invAmt as 'invAmtPd', past.refNum as 'refNumPd', 
present.date as dateCur, present.refNum as 'refNumCur', present.invAmt as 'invAmtCur'
FROM inv as past
LEFT JOIN inv as present ON (past.cst = present.cst)
WHERE past.cst = "cust1" 
AND past.status = "Paid"
AND YEAR(present.date) = "2017" AND MONTH(present.date) = "01"

结果是这样的:

datePd     invAmtPd         refNPd  dateCur             refNCur invAmtCur
2016-11-20 00:00:00 1500    0001    2017-01-05 00:00:00 2500    0003
2016-11-20 00:00:00 5500    0002    2017-01-05 00:00:00 2500    0003
2017-01-05 00:00:00 0003    2500    2017-01-05 00:00:00 2500    0003