从表中选择唯一行元素 [英] Select Unique row element from a table

问题描述

declare @tb1 table
( partner1 Varchar(5),
partner2 Varchar(5)
)

insert into @tb1
values( 'A','D'),
  ('B','E'),
  ('C','F'),
  ('D','A'),
  ('E','B'),
  ('F','C')

Select * from @tb1
   --output has to be like A D ,
   --                 B E ,
   --                 C F,

   --                 & not repeated like D A ,E B, F C

查询必须是通用的，如果有3列，则不应在其他任何行中重复3列的组合，给出的示例仅用于引用

Query has to be generic if there are 3 columns then combination of 3 columns should not be repeated in any other row the example given is just for refernce

推荐答案

对数据库进行了不同的设计:

提供所有合作伙伴和ID(数字).在插入到partner1的联接表中时，partner2始终确保它们按数字顺序排列，即如果您的伙伴1和2始终将1放在第一列中(如果其伙伴2和1则将1放在第一列中，而2在其中).第二个，依此类推，直到您需要的所有列.

然后，要获得partnesr的独特组合，您需要做的是:

从表中选择不同的col1，col1.....

希望对您有所帮助.

I would have designed the db differently:

Give all partners and ID (numeric). On insert into your join table of partner1, partner2 always make sure they are in numeric order ie if you have partner 1 and 2 always put 1 in the first column if its partners 2 and 1 then you put 1 in the first col and 2 in the second and so on for all the columns you need.

Then to get the unique combinations of partnesr all you need to do is:

Select distinct col1, col1.... from table.

Hope this helps.

这对您有帮助.

http://microsubhash.blogspot.in/p/sql-server.html?showComment=1334832372531 [ ^ ]

May this help you.

http://microsubhash.blogspot.in/p/sql-server.html?showComment=1334832372531[^]

这对您有帮助

http://support.microsoft.com/kb/139444 [

This will help u

http://support.microsoft.com/kb/139444[^]

这篇关于从表中选择唯一行元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！