J'ai deux tableaux de rapport différents avec les dates et le propriétaire du rapport. Je voudrais sélectionner les personnes qui ont rédigé l'un ou l'autre des rapports au moins une fois. J'ai également besoin d'un champ calculé qui montre quel numéro de rapport ils ont écrit. Le rapport 1 est prioritaire, donc si à tout moment quelqu'un a créé un rapport, la nouvelle colonne report_number devrait indiquer 1, sinon 2 (pour le rapport 2).
/* Get people from both reports */
WITH report_1_people AS (
SELECT P.full_name
FROM report_1 R1
INNER JOIN people P ON R1.author_person_id = P.person_id
WHERE P.full_name IS NOT NULL
AND P.full_name <> ''
), report_2_people AS (
SELECT P2.full_name
FROM report_2 R2
INNER JOIN people P2 ON R2.author_person_id = P2.person_id
WHERE P2.full_name IS NOT NULL
AND P2.full_name <> ''
)
SELECT
P.full_name,
CASE WHEN P.full_name IN ( /* Check if in report 1 */
SELECT full_name
FROM report_1)
THEN 1
ELSE 2
END AS report_number
FROM people P
WHERE P.full_name IS NOT NULL AND P.full_name <> ''
/* Eliminate duplicate names */
GROUP BY P.full_name
/* Filter only who either authored report 1 or report 2 */
HAVING P.full_name IN (SELECT full_name
FROM report_1_people)
OR P.full_name IN (SELECT full_name
FROM report_2_people)
Résultats souhaités: strong>
| full_name | report_number --------------------------- | John L Smith | 1 | Carl M Smelt | 2
Notez que le report_number de Jean est 1 même s'il a également rédigé un rapport 2.
Le rapport 1 et le rapport 2 ont des colonnes supplémentaires différentes, même si elles ont la même apparence ci-dessus.
Ce que j'ai essayé:
'people' table | person_id | full_name -------------------------- | 1 | John L Smith | 2 | Carl M Selt | 3 | Another Person 'report_1' table | report_1_id | author_person_id | date_entered | other_columns --------------------------------------------------------------- | 1 | 1 | 2018-01-12 | foo | 2 | 1 | 2018-02-18 | foo foo 'report_2' table | report_2_id | author_person_id | date_entered | other_columns --------------------------------------------------------------- | 1 | 1 | 2018-03-21 | bar | 2 | 1 | 2018-03-28 | bar bar | 3 | 2 | 2018-04-16 | baz | 4 | 2 | 2018-04-30 | baz baz
Remarque: Il y a un GROUP BY avec la table people car, pour une raison quelconque, il y a des doublons.
La requête a pris tellement de temps qu'elle s'est déconnectée de la base de données (plus de 24 heures), donc je pense que je fais quelque chose de mal. Existe-t-il une meilleure façon d'accomplir cette colonne calculée d'indicateur basée sur deux tables? Relativement nouveau en SQL, je me demande donc s'il y a une autre façon de penser que j'examine la logique SQL.
3 Réponses :
Vous pouvez utiliser APPLIQUER EXTERNE :
SELECT person_id, full_name, COALESCE(ca1.report_num, ca2.report_num) FROM people OUTER APPLY (SELECT TOP (1) 1 FROM report_1 WHERE author_person_id = people.person_id) AS ca1(report_num) OUTER APPLY (SELECT TOP (1) 2 FROM report_2 WHERE author_person_id = people.person_id) AS ca2(report_num)
Merci pour la suggestion! Cela renvoie beaucoup de valeurs NULL dans la nouvelle colonne, car il fait OUTER APPLY (ce qui, je crois, est comme LEFT JOIN). Cependant, je n'ai pas entendu parler de APPLY, alors je vais chercher pour voir si c'est une fonction que je devrais utiliser plus souvent.
Les IN sur les CTE sont susceptibles de le tuer.
Une autre façon est d'utiliser EXISTS pour vérifier si une personne a rédigé un rapport. Une expression CASE peut gérer la priorité.
SELECT p.full_name,
CASE
WHEN EXISTS (SELECT *
FROM report_1 r1
WHERE r1.author_person_id = p.person_id) THEN
1
WHEN EXISTS (SELECT *
FROM report_2 r2
WHERE r2.author_person_id = p.person_id) THEN
2
END report_number
FROM people p
WHERE EXISTS (SELECT *
FROM report_1 r1
WHERE r1.author_person_id = p.person_id)
OR EXISTS (SELECT *
FROM report_2 r2
WHERE r2.author_person_id = p.person_id);
Pour les performances, essayez les index sur report_1 (author_person_id) et report_2 (author_person_id) . Pour personnes , vous pouvez tester un index sur person_id (qui existe probablement déjà) ou peut-être un index composé sur person_id et full_name .
C'est bien! Je n'ai pas envisagé cette approche car je pensais qu'une sous-requête corrélée serait plus lente. Cette approche semble être la plus rapide des solutions proposées. J'ai supprimé la deuxième vérification EXIST dans la déclaration de cas puisque 2 en bas où la clause garantit qu'ils sont soit le rapport 1, soit le rapport 2.
Ceci est juste une autre façon d'obtenir le résultat.
SELECT
P.full_name,
MIN( R.Report_Number) AS report_number
FROM people P
OUTER APPLY (SELECT 1 WHERE EXISTS(SELECT * FROM report_1 R1 WHERE R1.author_person_id = P.person_id)
UNION ALL
SELECT 2 WHERE EXISTS(SELECT * FROM report_2 R2 WHERE R2.author_person_id = P.person_id)) AS R(Report_Number)
WHERE P.full_name IS NOT NULL AND P.full_name <> ''
/* Eliminate duplicate names */
GROUP BY P.full_name;
Dans les requêtes CTE, utilisez plutôt
person_id. Puis dans votreSélectionnez LEFT JOINles 2 tablesCTEvers la tablepeople. Supprimez leGroup ByetHave... Supprimez l'instructionCaseet remplacez-la par unISNULL (CTE1, CTE2) pour leReportIDVoyez ce que vous obtenez alors.La clause ayant avec des vérifications sur
P.full_name, bizarre car vous êtes déjà en train de grouper surP.full_name. Déplacez les vérifications vers la clause where. Ensuite, avoir le OU pour vérifier s'ils sont dans deux tables, peut être réécrit comme archivant avec une UNION des deux requêtes.