MySQL Forums
Forum List  »  German

Re: Select Abfrage aus Liste
Posted by: Thomas Wiedmann
Date: August 15, 2012 01:20AM

Hallo Bruno,

um doppelte Datensätze zu erzeugen musst deine Abfrage eine Art karthesisches Produkt erzeugen. Mit einem SUB-Select geht das nicht, sondern mit einem JOIN.


CREATE TABLE tab1 (
 id INT NOT NULL
);

INSERT INTO tab1 VALUES
(1), (2), (3), (5), (8 );

/* SUB-Select klappt nicht */
SELECT id
  FROM tab1
 WHERE id IN(1,2,5,8,3,2,2,1) 
ORDER BY id;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
|  8 |
+----+
5 rows in set (0.03 sec)

mysql>

/* 1. Lösung JOIN mit UNION ALL */
SELECT tab1.id
  FROM tab1
  JOIN ( SELECT 1 AS id
         UNION ALL
         SELECT 2 AS id
         UNION ALL
         SELECT 5 AS id    
         UNION ALL
         SELECT 8 AS id   
         UNION ALL
         SELECT 3 AS id   
         UNION ALL
         SELECT 2 AS id   
         UNION ALL
         SELECT 2 AS id
         UNION ALL
         SELECT 1 AS id   
       ) tab2
    ON tab1.id = tab2.id
ORDER BY tab1.id;
+----+
| id |
+----+
|  1 |
|  1 |
|  2 |
|  2 |
|  2 |
|  3 |
|  5 |
|  8 |
+----+
8 rows in set (0.00 sec)

mysql>

/* 2. Lösung Abfragemenge temporär speichern JOIN für größere Datenmenge */
CREATE TEMPORARY TABLE tab1_query (
 id INT NOT NULL
);
INSERT INTO tab1_query VALUES
(1), (2), (3), (5), (8 ), (2), (2), (1);

SELECT tab1.id
  FROM tab1
  JOIN tab1_query tab2
    ON tab1.id = tab2.id
ORDER BY tab1.id;
+----+
| id |
+----+
|  1 |
|  1 |
|  2 |
|  2 |
|  2 |
|  3 |
|  5 |
|  8 |
+----+
8 rows in set (0.00 sec)

mysql>

So sollte es stimmen..

Grüße
Thomas

Options: ReplyQuote


Subject
Views
Written By
Posted
1482
August 15, 2012 12:45AM
Re: Select Abfrage aus Liste
1226
August 15, 2012 01:20AM
807
August 15, 2012 01:37AM
850
August 16, 2012 04:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.