使用UNION与LIMIT的MySQL操作指南
P粉680487967
P粉680487967 2023-09-08 12:39:10
[MySQL讨论组]

所以,我手头有这个问题。

我必须从不同的表中使用内连接获取多个记录,并使用UNION或UNION ALL进行分页,下面的代码是用于获取所有记录和计数的。这是我的宿敌:

(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

我从13.2.9.3 UNION Clause中获取了这个例子,并在脚本上尝试了一下

当我尝试运行代码时,它报错了

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

对不起,我的英语很糟糕,这不是我的母语

P粉680487967
P粉680487967

全部回复(1)
P粉014293738

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
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号