问题:
id | sender | receiver
-------------------------------
1 | 1 | 12 <- case 1
2 | 12 | 1 <- case 2
3 |...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
id | sender | receiver
-------------------------------
1 | 1 | 12 <- case 1
2 | 12 | 1 <- case 2
3 | 1 | 2
4 | 3 | 1
I have a table with the above content, both sender
and receiver
are composite keys, meaning I can not have them twice in the same position but how can I make sure that sender or receiver are not repeated interchanged as marked in case 1 and 2 above.
回答1:
You can use computed columns along with a unique index:
create table mytable
(
id int not null auto_increment,
sender int not null,
receiver int not null,
key1 int as (least(sender,receiver)),
key2 int as (greatest(sender,receiver)),
primary key (id)
);
create unique index idx_mytable_unique on mytable(key1, key2);
Then
insert into mytable(sender, receiver) values (1,12);
-> 1 row inserted.
Then
insert into mytable(sender, receiver) values (12,1);
-> Duplicate entry '1-12' for key 'idx_mytable_unique'
Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a21e48498ff6c10007451109f8e07166
回答2:
This should work:
select distinct
case when sender > receiver then sender else receiver end as sender,
case when sender > receiver then receiver else sender end as receiver
from MyTable
回答3:
use cross join
select distinct a.*
from cte a cross join cte b
where a.sender<b.sender
回答4:
One option would be to use a before insert trigger to prevent this from happening in the first place. The other option would be to do nothing, and instead just handle this in your reporting. For example, if you wanted to find all distinct pairs of senders and receivers you could use LEAST
and GREATEST
:
SELECT DISTINCT
LEAST(sender, receiver) AS sender,
GREATEST(sender, receiver) AS receiver
FROM yourTable;
If your intention might be to retain only the first pair coming in a certain order, then you can add a timestamp column to your table and then aggregate:
SELECT
LEAST(t1.sender, t1.receiver) AS sender,
GREATEST(t1.sender, t1.receiver) AS receiver
FROM yourTable t1
INNER JOIN
(
SELECT
LEAST(sender, receiver) AS sender,
GREATEST(sender, receiver) AS receiver,
MIN(ts) AS min_ts
FROM yourTable
GROUP BY
LEAST(sender, receiver),
GREATEST(sender, receiver)
) t2
ON LEAST(t1.sender, t1.receiver) = t2.sender AND
GREATEST(t1.sender, t1.receiver) = t2.receiver AND
t1.ts = t2.min_ts;