RuoYI的列表分页查询

(1)：RuoYI的列表分页查询

 TableDataInfo<OrderVo> queryPageList(){ }

  

(2)：返回json字符串形式

@GetMapping("/list")
public TableDataInfo list(YshopAccount yshopAccount) {

startPage();
List<YshopAccount> list = iYshopAccountService.queryList(yshopAccount);
return getDataTable(list);

}

select class_id from table where length(class_id) <![CDATA[ <= ]]> '13' and class_id!='0'

to_char()函数
1： select count(*) from logisticsdata_2022 where to_char(receivedate,'yyyy-MM-dd') like '%2022-04%'

to_date()函数

技术原理:

   date_trunc() 截断日期函数,完成定时时间语法

实现案例:

select to_char(date_trunc('quarter',add_date),'yyyy-MM') quarter from dr_information group by date_trunc('quarter',add_date) order by quarter desc

select date_trunc('month',now()) +interval '12 h';
//每月1号 12点

select date_trunc('month',now()) + interval '15 d 9 h 30 min';
//每月15号9点半

select date_trunc('day',now()) + interval '9 h';
//每天9点
select date_trunc('day',now()) + interval '7 d';
//每周的今天
select date_trunc('weak',now()) + interval '1d 1minute';
//每周的周二第一分钟

select date_trunc('h',now()) + interval '30 minute';
//每小时

select date_trunc('minute',now()) + interval '30 second';
//每分钟

select date_trunc('minute',now()) + interval '30 minute 30 second';
//每30分钟

select date_trunc('quarter',now()) + interval '15 d 15 h 15 minute 30 second';
//本季度的第15天,15小时 15分 30秒

select date_trunc('quarter',now() ) - interval '1 h';
//每个季度最后一天的晚上11点

select date_trunc('quarter',now() + interval '3 month') - interval '1 h';
//每个季度的最后一天的晚上的11点(从下个季度开始算起)

select id, count() from table a where 1=1 group by id having count()>2

- 阅读剩余部分 -

select product_id,survey_date+interval '1 DAYS'

 select * from (

    select *,

    case when last_week_avg_price=0 then 0 else (this_week_avg_price-last_week_avg_price)/last_week_avg_price*100 end  ringvalue,
    (select sum(cast(this_day_trading as double)) from dr_information_items  where good_id=v.good_id and date_week=to_char(to_date(#{surveyDateWeek},'yyyy-MM-dd')+interval '-7 DAYS', 'yyyy-mm-dd') group by good_id,date_week) lastWeekTrading
    from
    (

    SELECT
    min(dr.id) id,
    items.cateid AS class_code,
    min(items.good_name) AS class_name,
    min(items.good_id) AS good_id,
    min(items.product) AS product,
    sum(items.this_day_count_price) AS this_week_price,
    sum(cast(items.this_day_trading as double)) AS this_day_trading,
    case when sum(cast(items.this_day_trading as double))=0 then avg(items.last_week_avg_price)  else  sum(items.this_day_count_price)/sum(cast(items.this_day_trading as double)) end this_week_avg_price ,
    avg(items.last_week_avg_price) AS last_week_avg_price,
    min(items.unit) AS unit,
    max(u.user_name) shop_number
    FROM dr_information_items items
    left JOIN dr_information dr ON dr.id = items.infomationid
    left JOIN sys_user u on LTrim(RTrim(items.create_by))=cast(LTrim(RTrim(u.user_name)) as text)
    WHERE  items.date_week IS NOT NULL AND dr.status =#{status}
    <if test="marketId != null  and marketId != ''">
        and dr.market_id=#{marketId}
    </if>
    <if test="surveyDateWeek != null">
        and to_date(items.date_week,'YYYY-MM-DD')=#{surveyDateWeek}
    </if>
    GROUP BY items.date_week, items.cateid
    ) v
    ) t where 1=1
    <if test="classCode != null">
        and LEFT(class_code,4)=#{classCode}
    </if>
    <if test="ringvalue != null">
        and ringvalue <![CDATA[ >= ]]> #{ringvalue}
    </if>

    UNION

    select  id,class_id class_code,name class_name,'' good_id,'' product,null this_week_price,null this_day_trading,null this_week_avg_price,null last_week_avg_price,'' unit,'' shop_number,null ringvalue,null lastWeekTrading

    from dr_lybase_class where  LEFT(class_id,4)=#{classCode}  and LENGTH(class_id)=8 and  LEFT(class_id,8)  in (select LEFT(cateid,8) from   dr_information_items
     where  to_date(date_week,'YYYY-MM-DD')=#{surveyDateWeek})

    order by class_code