sql查询所有数据并把中间数据靠前

最新推荐文章于 2025-02-19 17:59:40 发布

Koow

最新推荐文章于 2025-02-19 17:59:40 发布

阅读量769

点赞数

CC 4.0 BY-SA版权

分类专栏：备忘 mysql 文章标签：数据库 mysql sql 数据

本文链接：https://blog.youkuaiyun.com/u012934723/article/details/53185205

备忘同时被 2 个专栏收录

51 篇文章

订阅专栏

mysql

6 篇文章

订阅专栏

本文介绍了如何使用MySQL查询语句从数据库中获取所有记录，并确保中间部分的数据优先显示。这对于数据排序和分析有特殊需求的场景非常有用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql> select * from Test ORDER BY case name when 2 then 1 else 2 END,id desc;

+----+------+

| id | name |

+----+------+

| 3 | 2 |

| 4 | 1 |

| 2 | 3 |

| 1 | 4 |

+----+------+

4 rows in set

select * from(select * from lssuing l where 1=1 and state=2 order by case when lssuingType=5 then 0 when lssuingType<>5 then 1 else 2 end,borrowTime asc) a union all select * from(select * from lssuing l where 1=1 and state<>2 order by case when state=1 then 1 when state=3 then 2 when state=4 then 3 else 3 end ,borrowTime desc) b

sql="select * from("+sql+" and state=2 order by case when lssuingType=5 then 0 when lssuingType<>5 then 1 else 2 end,borrowTime asc) a union all select * from("+sql+" and state<>2 order by case when state=1 then 1 when state=3 then 2 when state=4 then 3 else 3 end ,borrowTime desc) b";

HttpServletRequest request=ServletActionContext.getRequest();

String time = request.getParameter("time");//时间

String rate = request.getParameter("rate");//利率

String order = request.getParameter("order");//排序

System.out.println("order="+order);

String sql ="select * from lssuing l where 1=1";

if(!"".equals(this.lussingType) && this.lussingType!=0){

sql =sql+ " and l.lssuingType=" + this.lussingType;

}

if(time!=null && !"".equals(time)){

if("1".equals(time)){//0~3个月

sql =sql+" and (l.periodTimeId <=3 or l.periodDayId is not null)";

}else if("2".equals(time)){//3~6个月

sql =sql+" and (l.periodTimeId <=6 and l.periodTimeId>=3)";

}else{

sql =sql+" and l.periodTimeId >=6 ";

}

ActionContext.getContext().put("time", time);

}

if(rate!=null && !"".equals(rate)){

if("1".equals(rate)){//利率为10~13

sql=sql+" and ((l.rate>=10 and l.rate<=13 and l.periodTimeId is not null) or " +

"(l.periodDayId is not null and ((l.rate *360)>=10 and (l.rate *360)<=13)))";

}else if("2".equals(rate)){

sql=sql+" and ((l.rate>=13 and l.rate<=15 and l.periodTimeId is not null) or " +

"(l.periodDayId is not null and ((l.rate *360)>=13 and (l.rate *360)<=15)))";

}else{

sql=sql+" and ((l.rate>=15 and l.periodTimeId is not null) or " +

"(l.periodDayId is not null and ((l.rate *360)>=15)))";

}

ActionContext.getContext().put("rate", rate);

}

if(order!=null && !"".equals(order)){

if(order.equals("2")){

sql=sql+" order by l.borrowTime desc";

}else if(order.equals("1")){

sql=sql+" order by -l.borrowMoney desc";

}

ActionContext.getContext().put("order", order);

}else{

//!"".equals(this.lussingType) && this.lussingType!=0

}

System.out.println("sql================================="+sql);

List lssuings = this.lssuingService.findLssuingsBySearch(sql);

int totalRecord =lssuings.size();

if(totalRecord % this.pageSize==0){

this.totalPage=totalRecord / this.pageSize;

}else {

this.totalPage=(totalRecord / this.pageSize)+1;

}

if(this.currentPage >= this.totalPage){

this.currentPage=this.totalPage;

}

if(this.currentPage <=1){

this.currentPage=1;

}

sql=sql+" limit "+(this.currentPage-1)*this.pageSize +","+this.pageSize;

System.out.println("sql="+sql);