数据库的一些常用sql语句

表中删除某些项相同并只保留ID最小
（1）

DELETE  T_Table          FROM  T_Table  t          WHERE  EXISTS(               SELECT * FROM  T_Table               WHERE ColumnA=t.ColumnA  AND ColumnB=t.ColumnB  AND  ID>t.ID)

（2）

DELETE FROM  T_Table           WHERE id not in (select min(id) from tablename group by ColumnA,ColumnB)

表中查找某些项相同的条数及其数目

SELECT ColumnA, ColumnB, count(*)FROM T_Tablegroup by ColumnA,ColumnBhaving count(*)>1

对某人某事做事统计

select name,    date_format(atime,'%y%m')as'月份',    sum(if(renwelb='扫地',1,0))as'扫地',      //if语句内嵌判断    sum(if(renwelb='擦玻璃',1,0))as'擦玻璃',    sum(if(renwelb='做饭',1,0))as'做饭'    from eee    group by name,date_format(atime,'%y%m');

获取表中11-15的数据项

SELECT top 5 * from (select top 15 * from table order by id asc) table_别名 order by id des