在多个列中查询相同的内容?

I have a table where there are some scores for pairs.

我有一张桌子，里面有一些成对的分数。

Like this

像这样

P1    P2    Score   Date
John  Mark  43      2011-01-01
Sinan Ash   53      2011-02-03
...
John  Suzie 34      2011-10-10
Ash   Sinan 54      2011-11-11
sinan suzie 42      2011-12-12
...

So what I want is to get all the scores for Sinan and his partner. What I am trying to get is something llike:

所以我想要的是得到西南和他的搭档的所有分数。我想要的是

partner - score
ash       53
ash       54
suzie     42

I'm trying to do it with te query below. Is there a better way to query than

我正在尝试使用下面的te查询。有更好的查询方法吗

select * from table WHERE P1 = 'sinan' OR P2 = 'sinan'

Is this efficient? Maybe there is a better way to store the data in the first place. Any suggestions?

这是有效的吗?也许有更好的方法来存储数据。有什么建议吗?

2 个解决方案

#1

The real trick is alternating the partner between P1 and P2. The simplest approach might be:

真正的诀窍是在P1和P2之间交替。最简单的办法可能是:

SELECT P2 AS partner, Score
    FROM table
    WHERE P1 = 'sinan'
UNION ALL
SELECT P1 AS partner, Score
    FROM table
    WHERE P2 = 'sinan'

#2

Here's an example using the CASE statement

这里有一个使用CASE语句的例子

SELECT CASE WHEN P1 = 'Sinan' THEN P2 ELSE P1 END AS Partner, Score
FROM ScoreTable
WHERE P1 = 'Sinan' OR P2 = 'Sinan'

#1