-
查詢重復(fù)數(shù)據(jù),保留最大ID SELECT user_name,COUNT(*) FROM user1_test GROUP BY user_name HAVING COUNT(*)>1; 刪除重復(fù)數(shù)據(jù) 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查看全部
-
使用序列號進(jìn)行列轉(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 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查看全部
-
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;查看全部
-
行轉(zhuǎn)列 ( 匯總。成績表 )。 SELECT * FROM ( SELECT SUM(kills) AS '沙僧' FROM user1 a JOIN user_kills b ON a.id = b.user_id AND a.user_name = '沙僧' ） a cross join ( SELECT SUM(kills) AS '八戒' FROM user1 a JOIN user_kills b ON a.id = b.user_id AND a.user_name = '八戒' ）a cross join ( SELECT SUM(kills) AS '悟空' FROM user1 a JOIN user_kills b ON a.id = b.user_id AND a.user_name = '悟空' ）c查看全部
-
select user_name ,replace(substring(substring_index(mobile,',',a.id),char_length(substring_index(mobile,',',a.id-1))+1),',','') as mobile FROM tb_sequence 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查看全部
-
select * from (select sum(kills) as '豬八戒' from user1 a join user_kills b on a.id = b.user_id and a.user_name = '豬八戒') a CROSS join ( select sum(kills) as '孫悟空' from user1 a join user_kills b on a.id = b.user_id and a.user_name = '孫悟空' ) b CROSS join ( select sum(kills) as '沙僧' from user1 a join user_kills b on a.id = b.user_id and a.user_name = '沙僧' ) c 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查看全部
-
查詢重復(fù)數(shù)據(jù),保留最大ID SELECT user_name,COUNT(*) FROM user1_test GROUP BY user_name HAVING COUNT(*)>1; 刪除重復(fù)數(shù)據(jù) 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查看全部
-
查詢重復(fù)數(shù)據(jù)查看全部
-
使用序列化列轉(zhuǎn)行查看全部
-
使用union行列轉(zhuǎn)換查看全部
-
使用union行列轉(zhuǎn)換查看全部
-
根據(jù)逗號截取字符串轉(zhuǎn)換成數(shù)組形式查看全部
-
通過添加逗號計算mobile中的個數(shù) 個數(shù)=總長度-去掉逗號后的長度查看全部
-
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;查看全部
-
使用cross join、聚合函數(shù)、gourp by分組查詢實現(xiàn)行轉(zhuǎn)列查看全部
舉報
0/150
提交
取消