行列轉換
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
新聞熱點
疑難解答
