正好想写一条删除重复语句并保留一条数据的SQL,网上查了一部分资料写的很详细,但还是在这里写下自己的理解,以遍后续学习 。如下:
表字段和数据:
SQL语句:
DELETE FROM `user` WHERE id NOT IN(SELECT * FROM(SELECT id FROM `user` GROUP BY username)AS b) 临时表
理解:
先从里面的SQL开始看
1、SELECT id FROM `user` GROUP BY username 根据名字分组查询出每组的ID。
2、SELECT * FROM(SELECT id FROM `user` GROUP BY username) AS b 这句话中有2个疑问点,
第一、为什么要套这样一个select?因为 更新数据时使用了查询,而查询的数据又做更新的条件,mysql不支持这种方式
如果不套上这个select查询,那么将会报1093 - You can't specify target table 'user' for update in FROM clause错误。
第二、这句话中一定要取别名,不然会报1248 - Every derived table must have its own alias 错误
3、结合上面的分析来看一下整个的SQL语句理解,先将分组的ID查出来,然后删除USER表中ID 不在分组ID中的数据,那么就实现效果了。
delete from 表名 where ID not in (select * from (select id from 表名 group by 分组的列名) 别名)
效果如下:
print('MySQL数据库正在连接,请稍等。。。。。。')
db= pymysql.connect('27.255.79.21','user','*******','db_qishu',charset="utf8")
#db = pymysql.connect('127.0.0.1', 'root', 'root', 'db_qishu', charset="utf8")
cursor= db.cursor()
print('MySQL数据库已经连接!!!!!!')
#sql = "SELECT Novel_url,count(Novel_url) as count FROM qishu_books_sort08 GROUP BY Novel_url HAVING count(Novel_url)>1 ORDER BY count ASC;"
#sql = "select Novel_url,count(*) as count from qishu_books_sort08 group by Novel_url having count>1;" #120704
#sql = "select Novel_ID,Novel_title from qishu_books_sort08 where Novel_url in (select Novel_url from qishu_books_sort08 group by Novel_url having count(Novel_url)>1) ORDER BY Novel_ID ASC;"
#sql = "SELECT Novel_url as count FROM qishu_books_sort011 GROUP BY Novel_url ORDER BY count ASC;"
#sql = "select Novel_ID from qishu_books_sort08 group by Novel_url having count(Novel_url) > 1;"
sql= "SELECT Novel_ID FROM `qishu_books_sort08` GROUP BY Novel_url ORDER BY Novel_ID ASC;"
cursor.execute(sql)
TXT_list= cursor.fetchall()
print(TXT_list)
print(len(TXT_list))
mysql可以根据字段进行排序
其中,DESC表示降序,ASC表示升序
order by 字段名 DESC;按照字段名降序排序
order by 字段名 ASC;按照字段名升序排序