在查询上应用自联接 [英] Apply Self join on Query

问题描述

游戏桌

Game_ID      日期

1           20-03-2015



游戏会员表

Game_ID会员IDID

1            1

1           2



会员表

Mem_ID Mem_Name

1     ;       阿里

2           Kamran



如何在一行中获得这样的输出

Game_ID       日期        会员2        会员1

1           20-03-2015          阿里           Kamran

Game Table
Game_ID        Date
1            20-03-2015

Game Member Table
Game_ID Member_ID
1            1
1            2

Member Table
Mem_ID Mem_Name
1            Ali
2            Kamran

How to get output like that in one Row
Game_ID        Date          Member2          Member1
1            20-03-2015            Ali            Kamran

推荐答案

试试这个：

Try this:

SELECT Game_ID, [1], [2]
FROM (
  SELECT gt.Game_ID, gm.Member_ID, mt.Mem_Name
  FROM [Game Table] gt
    LEFT JOIN [Game Member Table] gm ON gt.Game_ID = gm.Game_ID
    LEFT JOIN [Member Table] mt ON gm.Member_ID = mt.Mem_ID
) AS DT
PIVOT(MAX(Mem_Name) FOR Member_ID IN([1], [2])) AS PT 

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