第一种按照分组求和就可以了 SELECT ID, SUM(NUM) FROM tab GROUP BY ID 第二种 SELECT tab.ID, t2.NUM FROM tab LEFT JOIN (SELECT ID, SUM(NUM) AS NUM FROM tab GROUP BY ID) t2 ON tab.ID = t2.ID
====================================================================================
问题一:查询出某个字段所有不重复的数据以及求和?
问题二:查询出所有不重复的数据?
问题三:查询数据中所有某字段重复的记录?
id name address
1 a shanghai
2 a beijing
3 b hongkong
4 c hunan
5 d hubei
解决问题一:
1)、distinct
select distinct name from tablename;--求数据
select count(distinct name) from tablename;--求和
2)、group by
select name from tablename group by name;--求数据
select count(min(id)) from tablename group by name;--求和
select count(max(id)) from tablename group by name;--求和
解决问题二:
select * from tablename where id in(select min(id) from tablenamegroup by name) order by id;
select * from tablename where id in(select max(id)from tablename group by name) order by id;
解决问题三:
select * from table where name in (select name from table group by name having count(name) >1);
注:having count(name) >1 表示相同name列数据出现的次数,大于1就表示重复数据。