J'ai cette requête qui fonctionne mais ne donne que les cinq premières lignes des tables. voir mon ancienne question -> plusieurs instructions SELECT utilisant CTE
WITH cte AS ( SELECT n.name, e.value, ROW_NUMBER() OVER (PARTITION BY e.value ORDER BY e.id) AS rn from entries e LEFT JOIN nodes n on n.id=e.node_id LEFT JOIN attribs a on a.id=e.attrib_id WHERE a.name = 'LOCATION' AND e.value IN ('Wienerberg', 'Gruberstrasse') AND DATE(ts) = CURRENT_DATE ORDER BY e.id ), nums AS ( SELECT 1 rn UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) SELECT t1.name LNZ, t2.name WBG FROM nums LEFT JOIN cte t1 ON nums.rn = t1.rn LEFT JOIN cte t2 ON nums.rn = t2.rn WHERE t1.value = 'Gruberstrasse' AND t2.value = 'Wienerberg' -- AND COALESCE(t1.name, t2.name) ORDER BY nums.rn +----------------------+----------------------+ | LNZ | WBG | +----------------------+----------------------+ | AIXVAEBDBT | KUG01148_JBOSS-T6 | | OOEGKKT6 | AIXMVBMIGTA2 | | HSR5S1P8_AM | KUG01115_WSAP_HA_LPM | | AIXSTP11R3APP | AIXTESTHA2C1_HA_LPM | | HSR3S1P10_OOEGKKTEST | KUG01142_STP17PR_HA | +----------------------+----------------------+
3 Réponses :
Vous faites votre sélection parmi les nombres qui renverront toujours 5 lignes.
Sélectionnez dans cte et effectuez une jointure à gauche avec des nombres.
J'aurai besoin d'exemples d'enregistrements pour toutes les tables d'entrée pour cela.
Avant toute chose, puisque vous utilisez la version MariaDB supérieure à 10.1, vous avez de la chance. MariaDB a une telle chose appelée Storage Sequence Engine intégré. Cette chose peut vous donner autant de séquence de numérotation que vous le souhaitez. Donc, au lieu de faire:
SELECT Seq, IFNULL(GROUP_CONCAT(CASE WHEN B.value='Gruberstrasse' THEN B.Name END),0) AS 'LNZ', IFNULL(GROUP_CONCAT(CASE WHEN B.value='Wienerberg' THEN B.Name END),0) AS 'WBG' FROM seq_1_to_1000 A LEFT JOIN (SELECT n.name, e.value, ROW_NUMBER() OVER (PARTITION BY e.value ORDER BY e.id) AS rn FROM entries e LEFT JOIN nodes n ON n.id=e.node_id LEFT JOIN attribs a ON a.id=e.attrib_id WHERE a.name = 'LOCATION' AND e.value IN ('Wienerberg', 'Gruberstrasse') AND DATE(ts) = CURRENT_DATE ORDER BY e.id) B ON A.seq=B.rn GROUP BY A.seq HAVING LNZ+WBG <> 0;
Vous pouvez simplement faire:
WITH cte AS ( SELECT n.name, e.value, ROW_NUMBER() OVER (PARTITION BY e.value ORDER BY e.id) AS rn from entries e LEFT JOIN nodes n on n.id=e.node_id LEFT JOIN attribs a on a.id=e.attrib_id WHERE a.name = 'LOCATION' AND e.value IN ('Wienerberg', 'Gruberstrasse') AND DATE(ts) = CURRENT_DATE ORDER BY e.id ), nums AS ( SELECT seq AS rn FROM seq_1_to_1000 ) SELECT IFNULL(t1.name,0) LNZ, IFNULL(t2.name,0) WBG FROM nums LEFT JOIN cte t1 ON nums.rn = t1.rn LEFT JOIN cte t2 ON nums.rn = t2.rn WHERE t1.value = 'Gruberstrasse' AND t2.value = 'Wienerberg' -- AND COALESCE(t1.name, t2.name) HAVING (0) NOT IN ('LNZ','WBG') ORDER BY nums.rn;
Cela vous donnera directement une séquence de numérotation de 1 à 1000. Bien sûr , vous pouvez ajouter plus que cela et même utiliser une autre fonction que la simple exécution de nombres. Vous pouvez vous référer au lien de documentation que j'ai fourni ci-dessus.
La prochaine chose que je suppose serait, "et si les lignes ne dépassaient même pas 100?". Comme ça, vous n'avez pas besoin de changer la séquence de numérotation mais vous pouvez ajouter une autre fonction HAVING
à la fin de la requête, et peut-être quelques IFNULL
dans le SELECT
pour remplacer NULL
par une valeur.
Peut-être quelque chose comme ceci:
SELECT seq AS rn FROM seq_1_to_1000;
Essayez ceci si c'est fonctionnerait.
MODIFIER:
Voici une autre suggestion
SELECT 1 rn UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
Sans utiliser cte
:
LEFT JOIN
la séquence de numérotation directement avec la sous-requête que vous avez initialement créée pour le cte
. GROUP_CONCAT
avec l'expression CASE
dans SELECT
puis séquence de numérotation GROUP BY
- ajout de IFNULL pour renvoyer zéro et l'utiliser comme filtre dans HAVING
.
les lignes peuvent être plus de 100, donc j'ai utilisé la séquence de 1 à 1000, le problème maintenant est qu'elle génère des nombres égaux pour les deux lignes, par exemple LNZ est 31, WBG est 176 mais il n'en sort que 31 pour WBG également, même si c'est 176 .
Bon, je pense que c'est beaucoup plus simple à réaliser, mais sans aucun exemple de données solides, je prendrais un certain temps pour le faire. Laisse-moi essayer quelque chose et te recontacter un peu
En attendant, que diriez-vous de supprimer la partie HAVING (0) NOT IN ('LNZ', 'WBG')
et de réexécuter la requête, je pense que cela devrait renvoyer toutes les lignes. Ensuite, vous pouvez probablement passer à HAVING LNZ + WBG <> 0
à la place et réexécuter pour voir les résultats.
merci, très utile mais comme je l'ai dit ci-dessous, tout ce fouillis ajoute un tas de complexité et ne semble pas très pratique, d'autant plus que ces deux requêtes ne sont que des exemples. Je dois ajouter environ 15 autres clauses WHERE à cela et comme je le vois, cette construction cte n'est pas extensible à autant de requêtes (multiples points et jointures).
Il vous faut 2 CTE, pour les 2 cas de 'Gruberstrasse'
et 'Wienerberg'
puis leur appliquer un code simulé FULL JOIN (car MySql / MariaDB ne prend pas en charge une FULL JOIN):
DATE(ts) = CURRENT_DATE
Notez que l'application de la condition:
a.name = 'LOCATION'
dans la clause WHERE
fait de votre jointure LEFT une INNER JOIN
.
Si le code fonctionne, très bien, mais sinon, vous devez déplacer cette condition dans la clause ON
.
Il en va de même pour la condition:
WITH cte1 AS ( SELECT n.name, e.value, ROW_NUMBER() OVER (PARTITION BY e.value ORDER BY e.id) AS rn FROM entries e LEFT JOIN nodes n ON n.id = e.node_id LEFT JOIN attribs a ON a.id = e.attrib_id WHERE a.name = 'LOCATION' AND e.value = 'Gruberstrasse' AND DATE(ts) = CURRENT_DATE ), cte2 AS ( SELECT n.name, e.value, ROW_NUMBER() OVER (PARTITION BY e.value ORDER BY e.id) AS rn FROM entries e LEFT JOIN nodes n ON n.id = e.node_id LEFT JOIN attribs a ON a.id = e.attrib_id WHERE a.name = 'LOCATION' AND e.value = 'Wienerberg' AND DATE(ts) = CURRENT_DATE ) SELECT c1.name LNZ, c2.name WBG FROM cte1 c1 LEFT JOIN cte2 c2 ON c2.rn = c1.rn UNION SELECT c1.name LNZ, c2.name WBG FROM cte2 c2 LEFT JOIN cte1 c1 ON c2.rn = c1.rn
si ts
n'est pas une colonne de la table entrées
. P >
# 1250 - La table 'c1' de l'un des SELECTs ne peut pas être utilisée dans la clause ORDER
J'ai supprimé la clause ORDER BY. Y a-t-il une commande spécifique? Les lignes sont jointes par leurs numéros de ligne.
hmm..je dirais que la conclusion est que ce n'est pas faisable, vous êtes tous très utiles, mais tout cela semble trop compliqué et me donne de sérieux maux de tête .. merci pour votre contribution! Je vais supprimer cette question, y compris tout ce désordre.
Pourquoi pensez-vous que ce n'est pas faisable? Le seul problème est que MySQL et MariaDB ne prennent pas en charge les jointures FULL. S'ils le faisaient, le code serait plus simple.
peut-être que c'est faisable, mais comme je l'ai dit, c'est beaucoup trop compliqué pour moi. j'aime pense stupide simple. je vais ajouter une vue mysql pour chaque requête et l'utiliser dans mon application, pas tout à fait à la pointe de la technologie, mais au moins compréhensible, ce truc de côté est assez arcane
Je vois que vous utilisez MariaDB .. Quelle version?
MariaDB [aix_registry]> SELECT VERSION (); + ---------------- + | VERSION () | + ---------------- + | 10.4.8-MariaDB | + ---------------- + 1 ligne dans l'ensemble (0,000 sec)
Bien .. Alors, combien de lignes estimez-vous?
les lignes n'ont pas de nombre fixe, donc juste toutes. Je dirais que ces nombres entiers, compter le vaudou n'est même pas nécessaire.