-
sql生成特殊序列查看全部
-
使用union all連接轉(zhuǎn)換： select user_name,'arms' as equipment, arms from user1 a join user1_equipment b on a.id=user_id union all select user_name,'clothing' as equipment, clothing from user1 a join user1_equipment b on a.id=user_id union all select user_name,'shoe' as equipment, shoe from user1 a join user1_equipment b on a.id=user_id查看全部
-
列轉(zhuǎn)行2 select user_name,'arms' as equipment, arms from user1 a join user1_equipment b on a.id=user_id union all select user_name,'clothing' as equipment, clothing from user1 a join user1_equipment b on a.id=user_id; union all select user_name,'shoe' as equipment, shoe from user1 a join user1_equipment b on a.id=user_id;查看全部
-
使用序列化方法轉(zhuǎn)換： select user_name, replace(substring(substring_index(mobile,','a.id),char_length(substring_index(mobile,',',a.id-1))+1),',','') as mobile --截取字符串 from tb_sequence as a cross join( select user_name, concat(mobile,',') as mobile, --在mobile結(jié)尾增加逗號(hào) length(mobile)-length(replace(mobile,',',''))+1 as size --通過(guò)mobile總長(zhǎng)度-排除逗號(hào)后的長(zhǎng)度+1計(jì)算得出總段數(shù) from user1 as b ) as b on a.id<=b.size查看全部
-
case語(yǔ)句實(shí)現(xiàn)列轉(zhuǎn)行 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 use_kills b on a.id = b.user_id;查看全部
-
刪除重復(fù)數(shù)據(jù)，保留id最大的一條查看全部
-
利用group by having查詢重復(fù)查看全部
-
生成特殊序列號(hào)查看全部
-
生成序列號(hào)的方法查看全部
-
使用擴(kuò)展的序列查看全部
-
使用序列行轉(zhuǎn)列查看全部
-
行轉(zhuǎn)列查看全部
-
行轉(zhuǎn)列 case查看全部
-
行轉(zhuǎn)列 cross join查看全部
-
先需要把mobile列通過(guò)分隔字符串轉(zhuǎn)成行 再通過(guò)group by having方式刪除 再通過(guò)group by concat 轉(zhuǎn)換回來(lái) 再與原表關(guān)聯(lián)更新查看全部
舉報(bào)
0/150
提交
取消