使用临时变量累计求和
我们用的示例表
id | create_time | create_by | material_id | share_click |
---|
1 | 2021-01-20 02:18:23 | 11 | 100 | share |
2 | 2021-01-20 02:19:15 | 11 | 200 | share |
3 | 2021-01-20 02:20:10 | 11 | 100 | click |
4 | 2021-01-20 02:18:08 | 11 | 200 | share |
5 | 2021-01-20 02:18:32 | 11 | 100 | click |
码代码
可以直接写在mybatis mapper里面的方式去使用变量
select id, create_time, create_by, material_id, share_click ,
@sum := @sum + 1 as rownumber , (@sumScore := @sumScore + @sum) as sum_score ,
if((@sumScore ) % 2 = 0 , '偶数','非偶数') as 是否偶数
from material_share_click ,
(select @sum := 0 , @sumScore := 0) as temp__
查询结果
id | create_time | create_by | material_id | share_click | rownumber | sum_score | 是否偶数 |
---|
1 | 2021-01-20 02:18:23 | 11 | 100 | share | 1 | 1 | 非偶数 |
2 | 2021-01-20 02:19:15 | 11 | 200 | share | 2 | 3 | 非偶数 |
3 | 2021-01-20 02:20:10 | 11 | 100 | click | 3 | 6 | 偶数 |
4 | 2021-01-20 02:18:08 | 11 | 200 | share | 4 | 10 | 偶数 |
5 | 2021-01-20 02:18:32 | 11 | 100 | click | 5 | 15 | 非偶数 |
相同的ID,取最大或者最小的那个 (相同列值的记录中再根据条件取其中最大或最新一条)
示例用的表
id | create_time | create_by | material_id | share_click |
---|
1 | 2021-01-20 02:18:23 | 11 | 100 | share |
2 | 2021-01-20 02:19:15 | 11 | 200 | share |
3 | 2021-01-20 02:20:10 | 11 | 100 | click |
4 | 2021-01-20 02:18:08 | 11 | 200 | share |
5 | 2021-01-20 02:18:32 | 11 | 100 | click |
码代码
有点儿窗口函数那意思哈
select id, create_time, create_by, material_id, share_click
from material_share_click
where not exists(
select *
from material_share_click bb
where material_share_click.material_id = bb.material_id
and material_share_click.create_time < bb.create_time
)
查询结果
id | create_time | create_by | material_id | share_click |
---|
2 | 2021-01-20 02:19:15 | 11 | 200 | share |
3 | 2021-01-20 02:20:10 | 11 | 100 | click |
case when
case 订单状态
when 100 then '未开始'
when 200 then if(支付状态= 1, '已支付', '未支付')
when 300 then
case 配送方式
when '快递' then
if(是否签收= 1, '已收货',
if(物流单号 != '' or 物流单号 is null, '未发货', '已发货'))
when '自提' then if(issign = 1, '已使用', '未使用')
end
when 400 then '已完成'
end as 实际订单状态
经纬度求距离并排序
公式1
SELECT *,
(st_distance(
point(longitude,latitude),
point( 121.441345 , 31.176867 ))*111195/1000
) as juli
FROM store
ORDER BY juli ASC
公式2
SELECT *,
ROUND(6378.138*2*ASIN(SQRT(POW(SIN((${lat} *PI()/180-latitude *PI()/180)/2),2)+COS(${lat} *PI()/180)*COS(latitude *PI()/180)*POW(SIN((${lng} *PI()/180- longitude*PI()/180)/2),2)))*1000
) as distance
FROM lbsTable
order by distance asc