数据库的一些常用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_Table
group by ColumnA,ColumnB
having 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