Mysql 学习笔记

1 组内排序取最大

select v.* from vacate v where v.id not in (
select v1.id from vacate v1
INNER JOIN vacate v2 on v1.userId=v2.userId where v1.id<v2.id and v2.userId=2
) and v.userId=2

 

之前出错因为 没有 在里面添加==2条件，在外面也要加上才行

2 备份查询

select ls.*,rs.regionname,lfs.* from (
select l.id locationid,l.lat,l.lng,l.address,l.description,l.regionid from location l where l.address like '%addres%'
) as ls
INNER JOIN(
select r.id as regionid, r.regionname from region r where FIND_IN_SET(1,r.ParentIds) or r.id=1
) as rs on ls.regionid=rs.regionid
INNER JOIN(
select lf.liftnumber,lf.brand,lf.batchno,lf.installtime,lf.description,lf.modelid,lf.planid, lf.status,lf.locationid from lift lf where lf.liftnumber like '%00%' and (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(lf.installtime))/(24*60*60*365) BETWEEN 0 and 5
) as lfs on lfs.locationid=ls.locationid
where EXISTS (
select mpt.planid,mpt.scheduletype from mtcplan mp INNER JOIN mtcplantask mpt on mp.id=mpt.planid where mpt.planid=lfs.planid and FIND_IN_SET(mpt.scheduletype,'2,6')
)

 

select ls.*,rs.regionname,GROUP_CONCAT(CONCAT(lfs.brand,lfs.liftnumber,lfs.description) SEPARATOR '@@') from (
select l.id locationid,l.lat,l.lng,l.address,l.description,l.regionid from location l where l.address like '%addres%'
) as ls
INNER JOIN(
select r.id as regionid, r.regionname from region r where FIND_IN_SET(1,r.ParentIds) or r.id=1
) as rs on ls.regionid=rs.regionid
INNER JOIN(
select lf.liftnumber,lf.brand,lf.batchno,lf.installtime,lf.description,lf.modelid,lf.planid, lf.status,lf.locationid from lift lf where lf.liftnumber like '%00%' and (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(lf.installtime))/(24*60*60*365) BETWEEN 0 and 5
) as lfs on lfs.locationid=ls.locationid
where EXISTS (
select mpt.planid,mpt.scheduletype from mtcplan mp INNER JOIN mtcplantask mpt on mp.id=mpt.planid where mpt.planid=lfs.planid and FIND_IN_SET(mpt.scheduletype,'2,6')
) group by ls.locationid

转载于:https://www.cnblogs.com/genestart/p/11225020.html