根據來自 1 列的值選擇行是否相同？

我的數據庫表的一個例子

film id | scene id | value id
-----------------------------
  0    |     0    |     7
  0    |     1    |     1
  0    |     1    |     0
  1    |     2    |     8
  1    |     2    |     3
  1    |     3    |     1
  2    |     4    |     3
  2    |     4    |     7
  2    |     5    |     2

我只想選擇值 id 等於 8 或 3 的行，前提是它們都在具有相同場景 id 的行中。

我想要的結果是：

film id | scene id | value id
-----------------------------
  1    |     2    |     8
  1    |     2    |     3

我嘗試按場景 ID 計數進行分組，但它會輸出：

film id | scene id | value id
-----------------------------
  1    |     2    |     8
  1    |     2    |     3
  2    |     4    |     3

其中最後一行被包括在內，因為場景 ID 有多個值。

要根據相同的特定列選擇值，您可以查看NTILE

將有序分區中的行分佈到指定數量的組中。這些組從一個開始編號。對於每一行，NTILE 返回該行所屬組的編號。

實際上有很多選項，包括common_table_expression

在你的例子中：

--==================================================================================
use tempdb
if object_id('tempdb..radhe','U') is not null
  drop table radhe

create table  radhe(
[Film Id] int not null
,[Scene Id] int not null
,[Value Id]int not null
)


insert into radhe ([Film Id],[Scene Id],[Value Id])
select 
  0    ,     0    ,     7
union all select 
  0    ,     1    ,     1
union all select 
  0    ,     1    ,     0
union all select 
  1    ,     2    ,     8
union all select 
  1    ,     2    ,     3
union all select 
  1    ,     3    ,     1
union all select 
  2    ,     4    ,     3
union all select 
  2    ,     4    ,     7
union all select 
  2    ,     5    ,     2



--I want to select rows 
--where the value id is equal to 8 or 3 
--only if they are both in a row with the same scene id.


--using a cte
;with t8 as (

       SELECT r.[Film Id], r.[Scene Id], r.[Value Id]
       FROM radhe r
       WHERE r.[Value Id]IN (8) 
) ,
t3 as (


       SELECT r.[Film Id], r.[Scene Id], r.[Value Id]
       FROM radhe r
       WHERE r.[Value Id]IN (3) 
)

select t8.* 
from t8
inner join t3
       on t8.[Scene Id] = t3.[Scene Id]
union all
select t3.* 
from t8
inner join t3
       on t8.[Scene Id] = t3.[Scene Id]

--====================================================================

--using NTILE
select r.[Film Id], r.[Scene Id], r.[Value Id]
from
(
SELECT 
   NTILE(2) OVER (ORDER BY [Scene Id]) [NTILE],
   *
FROM Radhe
WHERE [Value Id]IN (3,8) 
) r
where r.NTILE = 1

結果是一樣的，但性能呢？

檢查這個和這個。

引用自：https://dba.stackexchange.com/questions/266629