我有一個名為“已交付”的資料庫，其中包含多個欄位，例如： ID -承運人-日期-激活-總重量
我想獲得一個結果，如果總重量是同一承運人的三倍值并且它被激活 = 1
ID Carrier Activated Total Weight
324197 Filipa Carlos Alves - Pavimentos Unipessoal, AAA. 0 420
324204 Filipa Carlos Alves - Pavimentos Unipessoal, AAA. 0 820
324704 Francisca José Vieira Oliveira 0 40
325152 FINIS - Respduos Industriais Unipessoal, AAA. 1 360
325464 FINIS - Respduos Industriais Unipessoal, AAA. 1 260
325531 Francisco José Vieira Oliveira 0 200
326152 FINIS - Respduos Industriais Unipessoal, AAA. 1 20
326157 FINIS - Respduos Industriais Unipessoal, AAA. 1 120
326293 Francisca José Vieira Oliveira 0 100
326325 F. P. & Pinto, Lda. 0 700
326563 FINIS - Respduos Industriais Unipessoal, AAA. 1 240
326564 FINIS - Respduos Industriais Unipessoal, AAA. 1 120
326914 FINIS - Respduos Industriais Unipessoal, AAA. 1 80
326921 FINIS - Respduos Industriais Unipessoal, AAA. 1 120
結果應該是
ID Carrier Activated Total Weight
326157 FINIS - Respduos Industriais Unipessoal, AAA. 1 120
326564 FINIS - Respduos Industriais Unipessoal, AAA. 1 120
326921 FINIS - Respduos Industriais Unipessoal, AAA. 1 120
最好的方法是什么
uj5u.com熱心網友回復：
您可以COUNT()用作視窗函式來查找重復的行。例如：
select *
from (
select t.*, count() over(partition by carrier, total_weight) as cnt
from delivered t
where activated = 1
) x
where cnt = 3
uj5u.com熱心網友回復：
提及您使用的 RDBMS 很重要，但希望您可以使用此示例。
WITH calculated_duplicates AS (
SELECT
[ID],
[Carrier],
[Activated],
[Total Weight],
COUNT(DISTINCT [Total Weight]) OVER (PARTITION BY [Carrier]) as number_of_duplicate_weights_per_carrier
FROM [your_table]
WHERE [Activated] = 1)
SELECT
[ID],
[Carrier],
[Activated],
[Total Weight]
FROM calculated_duplicates
WHERE number_of_duplicate_weights_per_carrier = 3
uj5u.com熱心網友回復：
我相信你只需要一個相關的存在檢查：
select *
from Delivered d
where d.Activated = 1 and exists (
select * from Delivered d2
where d2.carrier = d.carrier
and d2.Activated = 1
and d2."Total Weight" = d."Total Weight" * 3
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/454327.html
標籤：sql
下一篇：如何在每個組中選擇最好的專案？