我自己不知道这一点。希望有人可以帮助我。
我有三个表:users
,cards
,referees
和users_cards
使用者:
id | userName | referee_id
----------------------------
1 | u1 | 1
2 | u2 | 1
3 | u3 | 2
裁判员:
id | refName
--------------
1 | ref1
2 | ref2
牌:
id | cardName
--------------
1 | card1
2 | card2
用户卡:
user_id | card_id | color | number
-------------------------------------------
1 | 1 | red | 123
1 | 2 | yellow | 312
2 | 2 | yellow | 523
3 | 1 | red | 344
我想要得到的结果是:
id | userName | refName | cards
1 | u1 | ref1 | card1: red (123), card2: yellow (312)
2 | u2 | ref1 | card2: yellow (523)
3 | u3 | ref2 | card1: red (344)
等等...
当用户拥有一张以上的卡时,我只能得到多行。所以我怎么能这样合并它们。
谢谢任何能帮助我的人!´
编辑:
目前我只使用LEFT JOIN
s
SELECT UserName, refName cardName, color, number,
FROM users
LEFT JOIN referees ON users.referee_id = referees.id
LEFT JOIN users_cards ON users.id = users_cards.user_id
LEFT JOIN cards ON dbo.users_cards.card_id = cards.id
SELECT users.id, UserName, refName,
cards = (
SELECT
STUFF((
SELECT ', card' + CAST(ROW_NUMBER() OVER (ORDER BY cards.id) AS VARCHAR)
+': '+ color + '(' + CAST(number AS VARCHAR) +')'
FROM
users_cards
LEFT JOIN cards ON dbo.users_cards.card_id = cards.id
WHERE users.id = users_cards.user_id
FOR XML PATH('')), 1, 1, '')
)
FROM users
LEFT JOIN referees ON users.refereeid = referees.id
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句