MySQL Bereiche von bis abfragen

[ichbinich]

Hallo,

folgende Tabellenstruktur(vereinfacht):

ID | Modus
----------
1  |
2  | k
3  | k
4  | k
5  |
6  | h
7  | h
8  | h
9  |
10 | h
11 | h
12 | h
13 |

jetzt möchte ich als Ergebnis haben:

Modus | vonID | bisID
-------------------
k     | 2     | 4
h     | 6     | 8
h     | 10    | 12

also alle Bereiche von ID bis ID mit aufeinanderfolgendem Modus.

Mit folgender Abfrage bekomme ich die Start- und End-IDs für Modus 'h', allerdings in aufeinanderfolgenden Zeilen.

SELECT ID FROM tab WHERE  
  ID IN (SELECT ID FROM tab WHERE Modus='h')  
  AND (  
    ID-1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  
    OR  
    ID+1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  
  )

ID
---
6
8
10
12

So - und nun hab ich keine Idee mehr, wie ich weiter machen soll/muss, um dahin zu kommen wo ich hin will. Oder geht das vielleicht gar nicht in einer Abfrage?

Bin für jeden Schubs in die richtige Richtung dankbar...

vg ichbinich

[ChrisB]

Hi,

ID | Modus

1  |
2  | k
3  | k
4  | k
5  |
6  | h
7  | h
8  | h
9  |
10 | h
11 | h
12 | h
13 |

jetzt möchte ich als Ergebnis haben:

Modus | vonID | bisID

k     | 2     | 4
h     | 6     | 8
h     | 10    | 12

Wenn es jeweils unterschiedliche Modi wären, bräuchtest du nur danach gruppieren und MIN- und MAX-Wert der ID ermitteln.

Da du MySQL aber die Aufgabe übertragen willst, "fortlaufende IDs" zu ermitteln und quasi danach zu gruppieren, wird das so trivial überhaupt nicht möglich sein.

Sehr viel simpler wäre es, in der anschließenden Verarbeitung der sortierten Daten mittels eines leicht abgewandelten Gruppenwechsels zu ermitteln, ob die IDs für einen Modus fortlaufend sind oder nicht.

MfG ChrisB

[ichbinich]

Hallo,

Sehr viel simpler wäre es, in der anschließenden Verarbeitung der sortierten Daten mittels eines leicht abgewandelten Gruppenwechsels zu ermitteln, ob die IDs für einen Modus fortlaufend sind oder nicht.

Das denke ich inzwischen auch, danke.

vg ichbinich

[Vinzenz Mai]

Hallo,

SELECT ID FROM tab WHERE

ID IN (SELECT ID FROM tab WHERE Modus='h')
  AND (
    ID-1 NOT IN (SELECT ID FROM tab WHERE Modus='h')
    OR
    ID+1 NOT IN (SELECT ID FROM tab WHERE Modus='h')
  )

  

> So - und nun hab ich keine Idee mehr, wie ich weiter machen soll/muss, um dahin zu kommen wo ich hin will. Oder geht das vielleicht gar nicht in einer Abfrage?  
  
das geht schon - aber schön oder gar effizient ist [anders](https://forum.selfhtml.org/?t=209167&m=1423210) ...  
  
1\. Um zwischen Beginn und Ende eines Modus zu unterscheiden, selektieren wir immer nur den entsprechenden Abschnitt:  
  
~~~sql
SELECT  
    ID,  
    'h' modus,  
    'beginn' typ  
FROM  
    tab  
WHERE  
    ID IN (SELECT ID FROM tab WHERE Modus='h')  
AND  
    ID-1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  
  
UNION ALL  
  
SELECT  
    ID,  
    'h',  
    'ende'  
FROM  
    tab  
WHERE  
    ID IN (SELECT ID FROM tab WHERE Modus='h')  
AND  
    ID+1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  

2. Du siehst, dass Du für jeden Modus-Wert eine solche UNION dranhängen musst:

SELECT  
    ID,  
    'h' modus,  
    'beginn' typ  
FROM  
    tab  
WHERE  
    ID IN (SELECT ID FROM tab WHERE Modus='h')  
AND  
    ID-1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  
  
UNION ALL  
  
SELECT  
    ID,  
    'h',  
    'ende'  
FROM  
    tab  
WHERE  
    ID IN (SELECT ID FROM tab WHERE Modus='h')  
AND  
    ID+1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  
  
UNION ALL  
  
SELECT  
    ID,  
    'k',  
    'beginn'  
FROM  
    tab  
WHERE  
    ID IN (SELECT ID FROM tab WHERE Modus='k')  
AND  
    ID-1 NOT IN (SELECT ID FROM tab WHERE Modus='k')  
  
UNION ALL  
  
SELECT  
    ID,  
    'k',  
    'ende'  
FROM  
    tab  
WHERE  
    ID IN (SELECT ID FROM tab WHERE Modus='k')  
AND  
    ID+1 NOT IN (SELECT ID FROM tab WHERE Modus='k')  
  
ORDER BY  
    ID  

liefert Dir:

ID | modus | typ
-------------------
 2 |    k  | beginn
 4 |    k  | ende
 6 |    h  | beginn
 8 |    h  | ende
10 |    h  | beginn
12 |    h  | ende

Mit dieser Tabelle (als tab2) erhältst Du mit folgender korrelierten Unterabfrage

SELECT  
    i1.modus,  
    i1.ID vonID,  
    (SELECT MIN(i2.ID) FROM tab2 i2 WHERE i2.ID >  i1.ID) bisID  
FROM  
    tab2 i1  
WHERE  
    i1.typ = 'beginn'  
ORDER BY  
    i1.ID  

das von Dir gewünschte Ergebnis:

modus | vonID | bisID
---------------------
   k  |     2 |    4
   h  |     6 |    8
   h  |    10 |   12

Dabei nutzen wir aus, dass sich Beginn und Ende stets abwechseln. Wir suchen daher die kleinste ID, die größer ist als unser aktueller Beginn.

Nun kannst Du noch das wüste UNION-Konstrukt anstelle von tab2 (gleich zweimal!) einbauen und Du hast *eine* Abfrage, die Dir das gewünschte Ergebnis liefert:

  
SELECT  
  grenzen.modus,  
  grenzen.ID vonID,  
  (  
    SELECT  
      MIN(g2.ID)  
    FROM (  
      SELECT  
        i1.ID,  
        'h' modus,  
        'beginn' typ  
      FROM  
        tab i1  
      WHERE  
        ID IN (SELECT ID FROM tab WHERE Modus='h')  
      AND  
        ID-1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  
  
      UNION ALL  
  
      SELECT  
        ID,  
        'h',  
        'ende'  
      FROM  
        tab  
      WHERE  
        ID IN (SELECT ID FROM tab WHERE Modus='h')  
      AND  
        ID+1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  
  
      UNION ALL  
  
      SELECT  
        ID,  
        'k' modus,  
        'beginn' typ  
      FROM  
        tab  
      WHERE  
        ID IN (SELECT ID FROM tab WHERE Modus='k')  
      AND  
        ID-1 NOT IN (SELECT ID FROM tab WHERE Modus='k')  
  
      UNION ALL  
  
      SELECT  
        ID,  
        'k',  
        'ende'  
      FROM  
        tab  
      WHERE  
        ID IN (SELECT ID FROM tab WHERE Modus='k')  
      AND  
        ID+1 NOT IN (SELECT ID FROM tab WHERE Modus='k')  
    ) AS g2  
    WHERE  
      g2.ID >  grenzen.ID) bisID  
FROM (  
    SELECT  
      i1.ID,  
      'h' modus,  
      'beginn' typ  
    FROM  
      tab i1  
    WHERE  
      ID IN (SELECT ID FROM tab WHERE Modus='h')  
    AND  
      ID-1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  
  
    UNION ALL  
  
    SELECT  
      ID,  
      'h',  
      'ende'  
    FROM  
      tab  
    WHERE  
      ID IN (SELECT ID FROM tab WHERE Modus='h')  
    AND  
      ID+1 NOT IN (SELECT ID FROM tab WHERE Modus='h')  
  
    UNION ALL  
  
    SELECT  
      ID,  
      'k' modus,  
      'beginn' typ  
    FROM  
      tab  
    WHERE  
      ID IN (SELECT ID FROM tab WHERE Modus='k')  
    AND  
      ID-1 NOT IN (SELECT ID FROM tab WHERE Modus='k')  
  
    UNION ALL  
  
    SELECT  
      ID,  
      'k',  
      'ende'  
    FROM  
      tab  
    WHERE  
      ID IN (SELECT ID FROM tab WHERE Modus='k')  
    AND  
      ID+1 NOT IN (SELECT ID FROM tab WHERE Modus='k')  
) AS grenzen  
WHERE  
  grenzen.typ = 'beginn'  
ORDER BY  
  grenzen.ID

Wenn Du die unterschiedlichen Modi zusätzlich ermitteln musst, wird's noch lustiger. Spätestens dann möchtest Du eine Stored Procedure schreiben, die ein einziges Mal durch die Ausgangstabelle (sortiert) iteriert und das Ergebnis zusammenstellt ...

Freundliche Grüße

Vinzenz

[ichbinich]

Hallo,

Wenn Du die unterschiedlichen Modi zusätzlich ermitteln musst, wird's noch lustiger. Spätestens dann möchtest Du eine Stored Procedure schreiben, die ein einziges Mal durch die Ausgangstabelle (sortiert) iteriert und das

Ergebnis zusammenstellt ...

Dank dir für deine Mühe. Ich dachte mir schon irgendwie, dass es nicht trivial wird. Werd mich dann mal mit Stored Procedures beschäftigen...

vg ichbinich