需求
pagehelper分页查询: 表fu_plan 有字段follow_up_status ,该字段的值可以是1 或 2 。
条件如下
1、值为2的数据在最前面,值为1的值在后面
2、值为2的数据按字段start_time ,create_time 都倒序排列
3、值为1的数据按字段start_time正序 ,create_time 倒序排列
思路
1、先分别按follow_up_status 的值查出对应的数据并排序。
2、合并两个子查询的结果
3、载根据合并的及结果 按follow_up_status 倒序排序
先上完整sql
SELECT * FROM
(SELECT fp.* FROM
fu_plan fp JOIN hm_project_customer hp on fp.archive_id = hp.health_archive_id
and hp.customer_status = 1
WHERE
fp.archive_id = 404
and fp.follow_up_status = 1
ORDER BY fp.start_time desc,fp.create_time desc
LIMIT 0,10
) as t1
UNION
(SELECT fp.* FROM
fu_plan fp JOIN hm_project_customer hp on fp.archive_id = hp.health_archive_id
and hp.customer_status = 1
WHERE
fp.archive_id = 404
and fp.follow_up_status = 2
ORDER BY fp.start_time asc,fp.create_time desc
LIMIT 0,100
)
ORDER BY follow_up_status desc;
坑1
使用 UNION 如果不将俩个查询结果分别添加括号时会报错,加了且第一,二个子查询都不加“别名” 时报错
1248 - Every derived table must have its own alias
第一,二个子查询都加了as xx ”别名“时报错
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as t2
ORDER BY follow_up_status desc' at line 27
坑2
第一加,第二个子查询不加了 ”别名“时成功查询出结果,但是排序有问题
解决办法:
在子查询中加入,limit 关键字生效