SQL 2005在同一查询中使用多个联接 [英] SQL 2005 using multiple join in same query

问题描述

我有一个如下查询，

I have a query as follow,

SELECT payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created 
FROM tpayment_txn tt 
JOIN tbooking tb 
  ON tb.booking_ref_id = tt.booking_ref_id 
GROUP BY tt.payment_txn_status, YEAR(tb.date_created)

这给了我类似的结果，

which gives me result like ,

+-------------------+------+--------------+
|payment_txn_status |count | date_created |
+-------------------+------+--------------+
|  210              |   68 |     2011     |
|  201              |   34 |     2012     |
|  205              |   57 |     2011     |

(受影响的8行)

我还有一个名为

(8 rows affected)

I have one more table named

payment_txn_status_ref 

的表，其中包含每个付款状态代码的说明，如下所示，

which contains description for each payment staus code as follow,

+---------------+-----------+
|payment_status |description|
+---------------+-----------+
|201            | Created   |
|202            | Progress  |
|202            | Collected |
|205            | Paid      |

因此，我想修改上述查询，以便将其用payment_txn_status_ref表中的相应描述替换payment_txn_status代码

结果应该看起来像

So I want to modify above query so that it will replace payment_txn_status code with their respective description from payment_txn_status_ref table

The result should be look like ,

+-------------------+------+--------------+
|payment_txn_status |count | date_created |
+-------------------+------+--------------+
|  Created          |   68 |     2011     |
|  Progress         |   34 |     2012     |
|  Paid             |   57 |     2011     |

等等....

and so on....

推荐答案

尝试类似的方法:

Try something like this:

SELECT ps.description AS payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created
FROM tpayment_txn tt
JOIN tbooking tb
  ON tb.booking_ref_id = tt.booking_ref_id
JOIN payment_txn_status_ref ps 
  ON payment_txn_status = payment_status
GROUP BY tt.payment_txn_status, YEAR(tb.date_created)

祝你好运！

Good luck!

问题得到解决...非常感谢



SELECT ps.payment_status_desc AS Payment_txn_status，COUNT(*)AS"count"，YEAR(tb.date_created)AS date_created
来自tpayment_txn tt
加入tbing tb
开启tb.booking_ref_id = tt.booking_ref_id
加入tpayment_txn_status_ref ps
开启tt.payment_txn_status = ps.payment_status
GROUP BY ps.payment_status_desc，YEAR(tb.date_created)

It get solved... many thanks



SELECT ps.payment_status_desc AS payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created
FROM tpayment_txn tt
JOIN tbooking tb
ON tb.booking_ref_id = tt.booking_ref_id
JOIN tpayment_txn_status_ref ps
ON tt.payment_txn_status = ps.payment_status
GROUP BY ps.payment_status_desc, YEAR(tb.date_created)

这篇关于SQL 2005在同一查询中使用多个联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！