mysql

You can’t specify target table ‘Person’ for update in FROM clause
不能在from子句中有更新表’Person’的操作

delete from Person  where Id 
not in  (select min(Id) 'Id' from Person group by Email ) 

解决：
在from子句下再嵌套一个子查询
注意：将子查询派生的表赋予别名

delete from Person  where Id 
not in  (select Id from (select min(Id) 'Id' from Person group by Email 
) a ) 

对DATE字段进行加减

select a.Id from Weather a,Weather b
where  a.RecordDate= date_add(b.RecordDate, interval 1 day) and 
a.Temperature > b.Temperature

• DecordDate必须遵循格式 2015-01-01

case when的用法1

update salary
set sex=(
case
when sex='m' then 'f'
when sex='f' then 'm'
end)

case when的用法2

select 
 case 
 when id%2=0 then id-1
 when id=(select max(id) from seat) and id%2=1 then id
 else id+1
 end as id
 ,student
 from seat
 order by id

分组查询最高薪水所在的行
我自己写的

select Department,Employee,Salary
from
(
    select Employee.Id, Department.Name 'Department',Employee.Name 'Employee',Salary 'Salary',DepartmentId  
    from Employee  join Department  on (Employee.DepartmentId =Department.Id)
    ) as a
where Salary in (
select max(Salary) from Employee group by DepartmentId )
order by Id

答案的

select Department,Employee,Salary
from
(
    select Employee.Id, Department.Name 'Department',Employee.Name 'Employee',Salary 'Salary',DepartmentId  
    from Employee  join Department  on (Employee.DepartmentId =Department.Id)
    ) as a
where(DepartmentId ,Salary) in (
select DepartmentId,max(Salary) from Employee group by DepartmentId )
order by Id

区别是where(DepartmentId ,Salary) in这里，我没有写DepartmentId，导致我输出的结果不对

给表加上行号
分数排名 思路
给分数去重排名

select Score from Scores group by Score order by Score desc

给去重排名的表加上行号

select 
Score,@r:=@r+1 as Rank
from
(select Score from Scores group by Score order by Score desc)as a,
(select @r:=0) b
 ) as c 

将上表和原分数表内连接并排序

select Scores.Score Score,Rank
from 
(select 
Score,@r:=@r+1 as Rank
from
(select Score from Scores group by Score order by Score desc)as a,
(select @r:=0) b
 ) as c join Scores on (c.Score=Scores.Score)
 order by Rank

limit 的用法

select  distinct Salary as getNthHighestSalary
     from Employee
     order by Salary desc
     limit P,1

limit P,1表示第P行的后一个值

用来表示连续
abc三个的id从大到小是连续的

where a.Id=b.Id-1 
and b.Id=c.Id-1

abc
bac
cba
这三条id也是连续的

(a.id=b.id-1 and b.id=c.id-1)
 or(b.id=a.id-1 and a.id=c.id-1)
 or(c.id=b.id-1 and b.id=a.id-1))