MySQL-Bedienungshandbuch mit UNION und LIMIT

Question

Also, ich habe dieses Problem.

Ich muss mehrere Datensätze aus verschiedenen Tabellen abrufen, indem ich Inner Join verwende und UNION oder UNION ALL für die Paginierung verwende. Der folgende Code wird verwendet, um alle Datensätze abzurufen und zu zählen. Das ist mein Erzfeind:

(SELECT pp.id as id, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join inn_oficial i ON i.photo_id = pp.property_id
        left join plans p ON pp.plans_id = p.id
        WHERE (pp.type = 1 or pp.type = 2) 
        $where ) 
       UNION 
        (SELECT pp.id as id, pp.*, i.model as name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join rent_oficial i ON i.photo_id = pp.property_id
        left join plans p ON pp.plans_id = p.id
        WHERE pp.type = 3
        $where) 
       UNION 
        (SELECT pp.id as id, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join fish_pay_oficial i ON i.photo_id = pp.property_id
        left join plans p ON pp.plans_id = p.id
        WHERE pp.type = 4
        $where ) 
       UNION 
        (SELECT pp.id as id, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join stores_oficial i ON i.photo_id = pp.property_id
        left join plans p ON pp.plans_id = p.id
        WHERE pp.type = 5
        $where)
       UNION
        (SELECT pp.id as id, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join transfer_oficial i ON i.photo_id = pp.property_id
        inner join plans p ON pp.plans_id = p.id
        WHERE pp.type = 6
        $where)
        GROUP BY pp.id
        ORDER BY pp.id

Ich habe dieses Beispiel aus der UNION-Klausel 13.2.9.3 übernommen und es in einem Skript ausprobiert

Als ich versuchte, den Code auszuführen, wurde ein Fehler gemeldet

在第1行的'GROUP BY id ORDER BY id LIMIT 0.25'旁边，你的SQL语法有错误

Tut mir leid, mein Englisch ist schrecklich, es ist nicht meine Muttersprache

Answer 1

I solved kskskskks, just add the pp id column as id_p and then ordered by that

(SELECT pp.id as id_p, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join inn_oficial i ON i.photo_id = pp.property_id
    left join plans p ON pp.plans_id = p.id
    WHERE (pp.type = 1 or pp.type = 2) 
    $where ) 
   UNION 
    (SELECT pp.id as id_p, pp.*, i.model as name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join rent_oficial i ON i.photo_id = pp.property_id
    left join plans p ON pp.plans_id = p.id
    WHERE pp.type = 3
    $where) 
   UNION 
    (SELECT pp.id as id_p, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join fish_pay_oficial i ON i.photo_id = pp.property_id
    left join plans p ON pp.plans_id = p.id
    WHERE pp.type = 4
    $where ) 
   UNION 
    (SELECT pp.id as id_p, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join stores_oficial i ON i.photo_id = pp.property_id
    left join plans p ON pp.plans_id = p.id
    WHERE pp.type = 5
    $where)
   UNION
    (SELECT pp.id as id_p, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join transfer_oficial i ON i.photo_id = pp.property_id
    inner join plans p ON pp.plans_id = p.id
    WHERE pp.type = 6
    $where)
    ORDER BY id_p $pagination