Mysql简单使用

#搜索商品库存，售罄时间并按照库存降序排列
select id,stock,show_stock,sold_out_time as 售罄时间 from t_gd_goods where id in (591,665,950,297,330,424,664) ORDER BY show_stock desc

统计范围在60天内的写法：
sold_out_time >=date_sub(curdate(), INTERVAL 60 DAY) and sold_out_time <SYSDATE()

1、查询表中某字段值，如查询学生表中姓名=hapasm

select * from student where nam='hapasm'

2、查询物料表中，查询出所有物料代码重复的数据

select * from t_gd_material a where  (a.material_sn) in (select material_sn from t_gd_material GROUP BY material_sn having count(*)>1)

3、查询出所有数据进行分组之后，和重复数据的重复次数的查询数据，先列下：

4、查找表中多余的重复记录，重复记录是根据单个字段（protocol_detail_id ）来判断，并降序排列

select * from t_ex_order_detail where protocol_detail_id in 

(select protocol_detail_id from t_ex_order_detail group by protocol_detail_id having count(protocol_detail_id) > 1)
order by protocol_detail_id desc

5、删除表中多余的重复记录，重复记录是根据单个字段（protocol_detail_id）来判断，只留有rowid最小的记录
delete from t_ex_order_detail
where protocol_detail_id in (select protocol_detail_id from t_ex_order_detail group by protocol_detail_id having count(protocol_detail_id) > 1)
and rowid not in (select min(rowid) from t_ex_order_detail group by protocol_detail_id having count(protocol_detail_id )>1)

6、查找表中多余的重复记录（多个字段） 
select * from t_ex_order_detail a 
where (a.protocol_detail_id,a.id) in (select protocol_detail_id,id from t_ex_order_detail GROUP BY protocol_detail_id,id HAVING count(*)>1)

7、查询重复

select id,protocol_detail_id from t_gd_goods where protocol_detail_id in (select protocol_detail_id  from t_gd_goods group by protocol_detail_id having count(protocol_detail_id)>1)

8、取订单明细表中的quantity字段*协议明细表中的unit_price_notax，并累计总和

9、通过设置全局变量，删除商品