1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
|
;WITH Actions AS(--jeu d'essai
SELECT 1 AS id, 'Act1' AS libelle
UNION ALL SELECT 2, 'act2'
UNION ALL SELECT 3, 'act3'
),
Articles AS (--jeu d'essai
SELECT 1 AS id, 'ar1' AS libelle
UNION ALL SELECT 2, 'ar2'
UNION ALL SELECT 3, 'ar3'
UNION ALL SELECT 4, 'ar4'
UNION ALL SELECT 5, 'ar5'
UNION ALL SELECT 6, 'ar6'
UNION ALL SELECT 7, 'ar7'
UNION ALL SELECT 8, 'ar8'
UNION ALL SELECT 9, 'ar9'
UNION ALL SELECT 10, 'ar10'
UNION ALL SELECT 11, 'ar11'
UNION ALL SELECT 12, 'ar12'
UNION ALL SELECT 13, 'ar13'
),
ArticlesSimilaires AS (--jeu d'essai
SELECT 1 AS id, 1 AS id_groupe, 1 AS id_article
UNION ALL SELECT 2,1,9
UNION ALL SELECT 3,1,10
UNION ALL SELECT 4,2,2
UNION ALL SELECT 5,2,12
UNION ALL SELECT 6,3,13
),
regroupementAA AS (--jeu d'essai
SELECT 1 AS id, 1 AS id_action, 1 AS id_article
UNION ALL SELECT 2,1,3
UNION ALL SELECT 3,2,2
UNION ALL SELECT 4,2,3
UNION ALL SELECT 5,3,3
UNION ALL SELECT 6,3,3
),
Liste AS (--jeu d'essai => liste des artices saisis
SELECT 'ar3' AS Ar
UNION ALL SELECT 'ar2'
--UNION ALL SELECT 'ar3'
--...
),
ListeRN AS (-- numerotation des articles
SELECT
A1.id AS id_article,
id_groupe,
ROW_NUMBER() OVER (ORDER BY A1.id) AS RN
FROM Liste
INNER JOIN Articles A1
ON A1.libelle = Liste.Ar
LEFT OUTER JOIN ArticlesSimilaires S1
ON S1.id_article = A1.id
),
ListeSimilaire AS (-- liste des articles "compatibles"
SELECT
COALESCE(A2.id, L.id_article) AS id_article,
L.RN AS RN
FROM ListeRN L
LEFT OUTER JOIN ArticlesSimilaires S2
ON S2.id_groupe = L.id_groupe
LEFT OUTER JOIN Articles A2
ON A2.id = S2.id_article
),
RegroupementRN AS ( --numerotation des articles pour chaque action
SELECT
id_action, id_article,
ROW_NUMBER() OVER (PARTITION BY id_action ORDER BY id_article) AS RN,
COUNT(*) OVER (PARTITION BY id_action) AS NbArticlesAction
FROM regroupementAA
),
Matching AS (--matching entre la liste saisie et les actions
SELECT DISTINCT
id_action,
NbArticlesAction,
R.RN AS ArticleActionRN,
LS.RN AS ArticleListeRN
FROM regroupementRN R
INNER JOIN ListeSimilaire LS
ON R.id_article = LS.id_article
)
SELECT id_action
FROM Matching
WHERE NbArticlesAction = (SELECT COUNT(*) FROM Liste) --autant d'articles pour l'action que dans la liste
GROUP BY
id_action,
NbArticlesAction
HAVING
COUNT(DISTINCT ArticleActionRN) = NbArticlesAction --Chaque Article de l'action a une correspondance
AND COUNT(DISTINCT ArticleListeRN) = (SELECT COUNT(*) FROM Liste) --chaque Article de la liste a une correspondance |
Partager