如何在sqlserver2008中的表中排除相同的组合 [英] How do I exclude the same combination in the table in sqlserver2008

问题描述

我有一张桌子，里面有某些数字组合

MyTable：

col1 col2 priority

1 2 1

1 3 2

1 4 3

2 3 2

2 4 3

2 1 1

3 6 1

3 5 3

3 1 2

so..on

我需要从mytable中排除{（1,2）或（2,1）}和{（1,3）或（3,1）}的组合

I have a table where there are certain combinations of numbers
MyTable:
col1 col2 priority
1 2 1
1 3 2
1 4 3
2 3 2
2 4 3
2 1 1
3 6 1
3 5 3
3 1 2
so..on
And i need to exclude the combination of {(1,2) or (2,1)} and {(1,3) or (3,1)} from mytable

But I am not able to exclude the duplicate combination of the numbers.
Please suggest me a better way to exclude duplicate combinations like 
once (1,2) combination has appeared in mytable, then it should not appear again as (2,1) combination.
How can it be excluded.Please suggest.
Thanks in Advance.

What I have tried:

I have tried using ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY Col1,col3 DESC)

推荐答案

您可以在查询中添加一个列，其中乘以 col1 中的值， col2 ，然后使用 OVER PARTITION 或 DISTINCT 那些没有相同乘法结果的行。

You can add a column to your query where you multiply the values from col1 and col2, then use OVER PARTITION or DISTINCT to get only those rows which don't have the same multiplication result.

我可能过于简单了，但为什么不在添加新行之前查询组合？

I may be oversimplifying this but why not just query for the combo before adding a new row?

declare @n1 int = 2
declare @n2 int = 1
declare @p int = 21

IF NOT EXISTS (SELECT 1 FROM MyTable 
	WHERE (col1 = @n1 AND col2 = @n2) 
	   OR (col1 = @n2 AND col2 = @n1))
BEGIN
	INSERT INTO MyTable VALUES (@n1,@n2,@p)
END
ELSE
BEGIN
	UPDATE MyTable SET [priority]=@p
		WHERE (col1 = @n1 AND col2 = @n2) 
	   OR (col1 = @n2 AND col2 = @n1)
	-- I've updated. Don't know what you want to do here
END

这篇关于如何在sqlserver2008中的表中排除相同的组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！