MySql（二）

2019-11-08 20:55:48

字体：大中小

来源：转载

供稿：网友

行列转换

Select username,kills from user1 a join user_kills b on a.id = b.id;Select username,SUM(kills) from user1 join user1 a join user_kills b on a.id=b.id;第一种方法用cross join：select * from ( select sum(kills) as '孙悟空' from user1 a join user_kills b on a.id = b.use_id and a.user_name='孙悟空') a cross join ( select sum(kills) as '猪八戒' from user1 a join user_kills b on a.id = b.use_id and a.user_name='猪八戒') b cross join( select sum(kills) as '沙僧' from user1 a join user_kills b on a.id = b.use_id and a.user_name='沙僧') c

第二种方法采用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;

列转行： 1. 创建序列表 2.

select user_name,replace(substring(substring_index(mobile,',',a_id),char_length(substring_index(mobile,',',a.id-1),',',")as mobile from tb_sequernce a cross join select user_name,concat(mobile,',') as mobile,length(mobile)-length(replace(mobile,',',"))+1 size from user1 b ) b on a.id<=b.size;

第二种列转行 3.

select user_name,'arms' as equipment, arms from user1 a join user1_equipment b on a.id=user_idunion allselect user_name,'clothing' as equipment, clothing from user1 a join user1_equipment b on a.id=user_id;union allselect user_name,'shoe' as equipment, shoe from user1 a join user1_equipment b on a.id=user_id;select username,(case when s.id=1 then 'arms'when s.id=2 then 'clothing' when s.id=3 then 'shoe' end) equipment,(case when s.id=1 then arms when s.id=2 then clothing when s.id=3 then shoe end) eq_name from t_equipment e join t_user uon e.userid = u.userid cross join t_sequence s where s.id<=3order by username

生成唯一序列号使用自增序列设置序列号 1. 使用sql编写特殊序列号 2.

DECLARE v_cnt INT;DECLARE v_timestr INT;DECLARE rowcount BIGINT;SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d');SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;START TRANSACTION;UPDATE order_seq SET order_sn = order_sn + v_cnt WHERE timestr = v_timestr;IF ROW_COUNT() = 0 THENINSERT INTO order_seq(timestr,order_sn) VALUES(v_timestr,v_cnt);END IF;SELECT CONCAT(v_timestr,LPAD(order_sn,7,0))AS order_snFROM order_seq WHERE timestr = v_timestr;COMMIT;

删除重复数据 1. 判断是否重复

select user_name ，count(*) from test group by user_name having count(*)>1删除重复数据，同时保留id最大的那条记录delete afrom user1_test a join(select user1_name count(*),max(id) as idfrom user1_testgroup by user_name having count(*) >1)b on a.user_name = b.user_namewhere a.id<b.id

较为复杂的重复数据的处理：

sql:select username,GROUP_CONCAT(phone) mobile from( select DISTINCT a.username, REPLACE(SUBSTRING(SUBSTRING_INDEX(a.mobile,',',s.id),LENGTH(SUBSTRING_INDEX(a.mobile,',',s.id-1))+1),',','') phone from t_sequence s join (select username, CONCAT(mobile,',') mobile, (LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1) count from t_user where mobile is not null) a on s.id <= a.count)as bGROUP BY username

相关视频参考慕课网http://www.imooc.com/learn/427

上一篇：数据库索引的实现原理

下一篇：给SQL查询结果加上序号