-
查询重复数据,保留最大ID SELECT user_name,COUNT(*) FROM user1_test GROUP BY user_name HAVING COUNT(*)>1; 删除重复数据 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查看全部
-
使用序列号进行列转行。 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;查看全部
-
行转列 ( 汇总。成绩表 )。 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查看全部
-
查询重复数据,保留最大ID SELECT user_name,COUNT(*) FROM user1_test GROUP BY user_name HAVING COUNT(*)>1; 删除重复数据 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查看全部
-
查询重复数据查看全部
-
使用序列化列转行查看全部
-
使用union行列转换查看全部
-
使用union行列转换查看全部
-
根据逗号截取字符串转换成数组形式查看全部
-
通过添加逗号计算mobile中的个数 个数=总长度-去掉逗号后的长度查看全部
-
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、聚合函数、gourp by分组查询实现行转列查看全部
举报
0/150
提交
取消