最近遇到的mysql查询的问题

1.每个用户取一条最晚数据的mysql查询方法()

select * from <tablename> where <id> in (select max(id) from <tablename> group by userid)

2.删除重复数据(只剩下一条)的方法

delete a from <tablename> a left join (select max(id) as id from <tablename> group by <columnname>)b on a.id = b.id where isnull(a.id)

下面这种方法在mysql下会报错

delete from <tablename> where id not in (select max(id) from <tablename> group by <columnname>)

错误是不能从一个表中查询数据再进行更新

3.按名字拼音顺序排序的方法

select * from <tablename> convert( name using gbk ) collate gbk_chinese_ci asc/desc