我在too中有下表(也有其他列,但没有标识列或主键列):
Oid Cid
1 a
1 b
2 f
3 c
4 f
5 a
5 b
6 f
6 g
7 f
因此,在上面的示例中,我想强调,当将Cid列值看作“对”时,下面的Oid是重复的:
Oid:
1 (1 matches Oid: 5)
2 (2 matches Oid: 4 and 7)
请注意,Oid 2匹配不包括Oid 6,因为这对6也有字母'G‘。
是否可以创建一个查询而不使用While循环来突出上面的"Oid“呢?以及数据库中还有多少个其他匹配项?我试图在数据集中找到与这两列相关的模式。提前谢谢你。
发布于 2017-02-23 10:08:04
下面是一个有用的示例--有关解释,请参见注释:
--First set up your data in a temp table
declare @oidcid table (Oid int, Cid char(1));
insert into @oidcid values
(1,'a'),
(1,'b'),
(2,'f'),
(3,'c'),
(4,'f'),
(5,'a'),
(5,'b'),
(6,'f'),
(6,'g'),
(7,'f');
--This cte gets a table with all of the cids in order, for each oid
with cte as (
select distinct Oid, (select Cid + ',' from @oidcid i2
where i2.Oid = i.Oid order by Cid
for xml path('')) Cids
from @oidcid i
)
select Oid, cte.Cids
from cte
inner join (
-- Here we get just the lists of cids that appear more than once
select Cids, Count(Oid) as OidCount
from cte group by Cids
having Count(Oid) > 1 ) as gcte on cte.Cids = gcte.Cids
-- And when we list them, we are showing the oids with duplicate cids next to each other
Order by cte.Cids
发布于 2017-02-23 00:17:42
select o1.Cid, o1.Oid, o2.Oid
, count(*) + 1 over (partition by o1.Cid) as [cnt]
from table o1
join table o2
on o1.Cid = o2.Cid
and o1.Oid < o2.Oid
order by o1.Cid, o1.Oid, o2.Oid
发布于 2017-02-23 00:34:27
也许是这样的:
WITH CTE AS
(
SELECT Cid, oid
,ROW_NUMBER() OVER (PARTITION BY cid ORDER BY cid) AS RN
,SUM(1) OVER (PARTITION BY oid) AS maxRow2
,SUM(1) OVER (PARTITION BY cid) AS maxRow
FROM oid
)
SELECT * FROM CTE WHERE maxRow != 1 AND maxRow2 = 1
ORDER BY oid
https://stackoverflow.com/questions/42403399
复制相似问题