Select everything from SQL database except duplicate

debugcn 投稿 Dev

kai

I have a database that looks like this:

handshakes
-----------------------------------
|id | participant1 | participant2 |
-----------------------------------
| 1 | Thomas Miller| Max Miller   |
| 2 | Thomas Miller| Jack Miller  |
| 3 | Jack Miller  | Max Miller   |
| 4 | Max Miller   | Thomas Miller|
| 5 | Jack Miller  | Max Miller   |
-----------------------------------

It measures how many times participant1 and participant2 have shaked hands.
I want to select the amount of times total people have shaken hands (without the duplicates counted).
So in this example the output would be like this:

Thomas Miller | Max Miller
Thomas Miller | Jack Miller
Jack miller   | Max Miller
Total: 3times

Can anyone help me with the SQL Statement to do so?

forpas

With NOT EXISTS:

select id, h.participant1, h.participant2 
from handshakes h
where not exists (
  select 1 from handshakes
  where id < h.id 
  and least(participant1, participant2) = least(h.participant1, h.participant2) 
  and greatest(participant1, participant2) = greatest(h.participant1, h.participant2) 
)

This query uses the functions least() and greatest() which are supported by MySql and Postgresql.

See the demo.
Results:

| id  | participant1  | participant2 |
| --- | ------------- | ------------ |
| 1   | Thomas Miller | Max Miller   |
| 2   | Thomas Miller | Jack Miller  |
| 3   | Jack Miller   | Max Miller   |

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]