group by 和 distinct 的区别
SELECT fs.card_id, fs. NAME, fs.email, fs.phone_num, fs.weixin_num, fs.permission, fs.open_id FROM fs_card fs WHERE fs.open_id IS NOT NULL GROUP BY fs.name
//group by 是 按照 name 进行分组 , 结果显示名字不重复的一组数据。如果存在两条名字相同的数据,则只取出第一条数据。
SELECT fs.card_id, fs. NAME, fs.email, fs.phone_num, fs.weixin_num, fs.permission, fs.open_id FROM fs_card fs WHERE fs.open_id IS NOT NULL GROUP BY fs.name having COUNT(fs.card_id)= 1
//加上count (fs.card_id)=1 条件后会把 具有相同名字的数据 去除(默认这两条数据的card_id 不同)
SELECT fs.card_id, fs. NAME, fs.email, fs.phone_num, fs.weixin_num, fs.permission, fs.open_id FROM fs_card fs WHERE fs.open_id IS NOT NULL GROUP BY fs.name having COUNT(fs.card_id)>= 1
//查询结果和 不加 having 条件 相同。
SELECT distinct fs.card_id, fs. NAME, fs.email, fs.phone_num, fs.weixin_num, fs.permission, fs.open_id FROM fs_card fs WHERE fs.open_id IS NOT NULL GROUP BY fs.name having COUNT(fs.card_id)= 1
//distinct 的作用域是 所有的查询字段, 是对 该条查询结果数据 进行 去重复(比较的是结果集,即每条记录),而不是对某个字段。如果上面的名字相同的两条数据如果有查询的某个字段不同,则按照不同结果处理