SELECT location_id,
CASE
WHEN (location_id+1) mod 2 = 0 THEN location_id +1
ELSE location_id -1
END as ‘缺失ID’,
FORMAT((( location_id +location_id mod 2)/2),0) as “中间值”
FROM location_copy GROUP BY (( location_id +location_id mod 2)/2) HAVING COUNT(*) =1 ORDER BY location_id;
select COALESCE(medicine_id,“总数”) medicine_id ,COUNT(*) from location
where medicine_id>0 and medicine_id<20 GROUP BY medicine_id WITH ROLLUP;