获取表中所有组的相互值(SQL Server)

i have a table with 3 columns

我有一个有3个列的表格。

id(primary key), masCode (masterCode) and detCode(detailCode)

id(主键)、masCode(母码)和detailCode(详细码)

i need to select detCodes that are repeated in all masCode groups

我需要选择在所有masCode组中重复的detcode

for example if i have this data:

例如，如果我有这个数据:

+----+---------+----------+
| id | masCode | detCode  |
+----+---------+----------+
|  1 | 111     |   1      |
|  2 | 111     |   5      |
|  3 | 222     |   2      |
|  4 | 222     |   5      |
|  5 | 222     |   1      |
|  6 | 223     |   5      |
|  7 | 223     |   6      |
|  8 | 223     |   1      |
+----+---------+----------+

the result will be this:

其结果将是:

+----+---------+----------+
| id | masCode | detCode  |
+----+---------+----------+
|  1 | 111     |   1      |
|  2 | 111     |   5      |
|  4 | 222     |   5      |
|  5 | 222     |   1      |
|  6 | 223     |   5      |
|  8 | 223     |   1      |
+----+---------+----------+

i think i can do this with dynamic Query, is there any better way to do this? thanks

我想我可以用dynamic Query做这个，有更好的方法吗?谢谢

4 个解决方案

#1

Here's one way.

这是方法之一。

declare @masCode int = (select count(distinct masCode) from YourTable)

select *
from yourTable
where detCode in (select detCode 
                 from YourTable 
                 group by detCode 
                 having count(detCode) = @masCode)

ONLINE DEMO

在线演示

#2

Try this:

试试这个:

;with _countedDetCodes as (
    select
        *,
        -- number of masCodes for particular detCode
        count(1) over (partition by detCode) as masCodeCnt  
    from yourTable
)
select
    Id, masCode, detCode
from _countedDetCodes
where
    masCodeCnt = (select count(distinct masCode) as [Value] from yourTable)

#3

Dynamic Queries (i.e. queries that are written as text and execute by SQL 'Execute ' instruction), are generally a bad idea.

动态查询(即以文本形式编写并执行SQL ' execute '指令的查询)通常是一个坏主意。

execute ('select * from NotExistingTable')

There are some reasons why. Your editor will not be able to help you (at least not prior to query run) to track errors in your query. When queries get complicated, this is an issue.

原因有很多。您的编辑器将不能帮助您(至少在查询运行之前)跟踪查询中的错误。当查询变得复杂时，这是一个问题。

Another problem is that sql server cannot assume anything about the query, hence - it cannot bulid optimized execution plan, which usually degrades performance.

另一个问题是，sql server不能对查询进行任何假设，因此，它不能使用优化的执行计划，这通常会降低性能。

What you are looking is fairly simple. Here is one solution.

你所看到的相当简单。这是一个解决方案。

;with nonuniques as (
select masCode  from [YourTable]
group by masCode 
having count(*) > 1
) select id, masCode, detCode from [YourTable] t
  inner join nonuniques n on n.masCode = t.masCode

#4

here is a quick and easy solution for you:

这里有一个快速简单的解决方法:

drop table if exists #temp 

create table #temp
(
id      int,
masCode int,
detCode int
)

insert into #temp values 
(1,111,1)
,(2,111,5)
,(3,222,2)
,(4,222,5)
,(5,222,1)
,(6,223,5)
,(7,223,6)
,(8,223,1)

select  *
from    #temp a 
where   a.detCode in (
        select  t.detCode
        from    #temp t
        group 
        by      t.detCode
        having(count(mascode)) > 1
        )

#1