J'essaie de comprendre comment coder une requête SQL qui identifiera un groupe dans une table qui contient la même valeur dans les enregistrements du groupe sur plusieurs colonnes.
Par exemple, étant donné l'exemple de tableau suivant:
Je cherche à identifier dans ce cas les noms pour lesquels tous les essais avaient les mêmes points de départ et d'arrivée. Seuls James et Frank remplissent ces critères selon les données. Je suis ne cherche pas à filtrer et à obtenir une sortie de requête qui me donne simplement tous les groupes avec "oui" ou "non". Je veux que la requête affiche la table entière avec le groupe étiqueté de manière appropriée. Comment procéder?
Ci-dessous, tout ce que j'ai obtenu. Je vois que lorsque je regroupe d'ici là, les groupes qui affichent toutes les valeurs identiques dans les colonnes d'intérêt se réduisent en un seul groupe. Mais si je lance un nouveau groupe dans lequel il y en a plusieurs lignes (essais) où chaque essai peut avoir les mêmes points de début et de fin, mais les différents essais pour le groupe ne sont pas les mêmes points, alors en fait le groupe devrait donner «non» même si chaque essai individuel montre les mêmes points de départ et d'arrivée. / p>
CREATE TABLE Trial
(Name nvarchar(100),
Trial nvarchar(10),
Start nvarchar(100),
[End] nvarchar(100))
INSERT INTO Trial
VALUES
('Julie', 'A', 'Ranch St', 'Ranch St'),
('Julie', 'B', 'Ranch St', 'North Blvd'),
('James', 'A', 'Deer Rd', 'Deer Rd'),
('James', 'B', 'Deer Rd', 'Deer Rd'),
('Emily', 'A', 'Oak St', 'Oak St'),
('Emily', 'B', 'Oak St', 'Elm Rd'),
('Frank', 'A', 'Main Ave', 'Main Ave'),
('Frank', 'B', 'Main Ave', 'Main Ave'),
('Frank', 'C', 'Main Ave', 'Main Ave'),
('Jeff', 'A', 'Main Ave', 'Main Ave'),
('Jeff', 'B', 'Oak St', 'Oak St')
SELECT
a.[Name]
,CASE
WHEN a.[Start] = a.[END] THEN 'Yes' ELSE 'No'
END AS 'TrialChk'
FROM Trial AS a
GROUP BY a.[Name]
,a.[Start]
,a.[End]
ORDER BY Name ASC;
Je n'ai pas l'essai de Jeff dans mon exemple de tableau ci-dessus, mais Jeff donnera "oui" pour chaque essai, mais puisque tous les points ne sont pas e La sortie du groupe doit être "non".
4 Réponses :
Vous pouvez essayer la fonction fenêtrée Row_Number () comme indiqué ci-dessous:
Create table MyTable (Name varchar(50),
Trial Char(1),
Start Varchar(20),
[End] Varchar(20))
Insert into MyTable Values
('Julie', 'A', 'Ranch St', 'Ranch St'),
('Julie', 'B', 'Ranch St', 'North Blvd'),
('James', 'A', 'Deer Rd', 'Deer Rd'),
('James', 'B', 'Deer Rd', 'Deer Rd')
;with cte as (Select name
,trial
,start
,[End]
,Row_number() Over(Partition By [Start], [End] order by [Start], [End]) as SrNo
From MyTable
)
Select
name, trial, start, [End], 'Yes' as [Expected Output]
from cte where name in ( Select name from cte where SrNo > 1)
union all
Select
name, trial, start, [End], 'No' as [Expected Output]
from cte where name not in (Select name from cte where SrNo > 1)
order by name
Vérifier la requête donnée
DECLARE @Counter int
DECLaRE @CMS_Query varchar(MAX)
DECLARE @total_Row int
DECLARE Cur_1 CURSOR FOR select Name from Trial group by Name
Create Table #MainTrial(
[Name] varchar(MAX),
Trial varchar(MAX),
[Start] varchar(MAX),
[END] varchar(MAX),
[TrialChk] varchar(3)
)
OPEN Cur_1
DECLARE @Name varchar(MAX)
FETCH NEXT FROM Cur_1 INTO @Name
WHILE (@@FETCH_STATUS = 0)
Begin
SELECT a.[Name],Trial,Start,[End],CASE WHEN a.[Start] = a.[END] THEN 'Yes' ELSE 'No'END AS 'TrialChk' into #Trial1 FROM Trial AS a where Name = @Name
select @total_Row = count(*) from #Trial1
select @Counter = COUNT(*) from #Trial1 where TrialChk = 'Yes'
IF @total_Row != @Counter
BEGIN
UPDATE #Trial1 SET TrialChk = 'No' where [Name] = @Name
END
insert into #MainTrial select * from #Trial1
DROP Table #Trial1
FETCH NEXT FROM Cur_1 INTO @Name
END
CLOSE Cur_1
DEALLOCATE Cur_1
select * from #MainTrial
drop table #MainTrial
Utilisez simplement case avec les fonctions de fenêtre:
select t.*,
(case when min(start) over (partition by name) = max(start) over (partition by name) and
min(end) over (partition by name) = max(end) over (partition by name) and
start = end
then 'Yes' else 'No'
end) as flag
from trial t;
Voici un violon db .
Remarque: end est un nom très pauvre pour une colonne car il s'agit d'un mot-clé SQL.
Si vous GROUP BY Name et comptez le nombre distinct de Start s et End , comme le fait cette requête:
> Name | Trial | Start | End | Result > :---- | :---- | :------- | :--------- | :----- > Emily | A | Oak St | Oak St | No > Emily | B | Oak St | Elm Rd | No > Frank | A | Main Ave | Main Ave | Yes > Frank | B | Main Ave | Main Ave | Yes > Frank | C | Main Ave | Main Ave | Yes > James | A | Deer Rd | Deer Rd | Yes > James | B | Deer Rd | Deer Rd | Yes > Jeff | A | Main Ave | Main Ave | No > Jeff | B | Oak St | Oak St | No > Julie | A | Ranch St | Ranch St | No > Julie | B | Ranch St | North Blvd | No
vous obtenez ces résultats:
SELECT t.*, CASE WHEN c.start_counter = 1 AND c.end_counter = 1 THEN 'Yes' ELSE 'No' END Result FROM Trial t INNER JOIN ( SELECT Name, COUNT(DISTINCT Start) start_counter, COUNT(DISTINCT [End]) end_counter FROM Trial GROUP BY Name ) c ON c.Name = t.Name
Vous pouvez donc joindre la requête ci-dessus à votre table et définir 'Oui' où les 2 compteurs sont tous deux égaux à 1, sinon définissez 'No':
> Name | start_counter | end_counter > :---- | ------------: | ----------: > Emily | 1 | 2 > Frank | 1 | 1 > James | 1 | 1 > Jeff | 2 | 2 > Julie | 1 | 2
Voir le démo .
Résultats:
SELECT Name, COUNT(DISTINCT Start) start_counter, COUNT(DISTINCT [End]) end_counter FROM Trial GROUP BY Name