sql开发技巧2

1，行转列

使用case 实现行转列
例如： select sum(case when user_name=“孙悟空” then kills end) as “孙悟空”,
sum(case when user_name=“猪八戒” then kills end) as “猪八戒”,
sum(case when user_name=“沙僧” then kills end) as “沙僧”
from user1 a join user_kills b on a.id=b.user_id;

2,生成唯一序列号
需要用到的场景有 1)数据库主键 2)如发票号，车票号，订单号等

3，如何删除重复的数据

delete a from user1_test a join(select user_name,count(),max(id) as id from user1_test
group by user_name Having count() > 1 ) b on a.user_name = b.user_name where a.id < b.id