-
行列转换，cross join查看全部
-
行转列的关键是union ,列转行的关键是join查看全部
-
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,','))+1 size FROM user1 b) b ON a.id <= b.size查看全部
-
cross join查看全部
-
行专列技巧⋯⋯汇总查看全部
-
使用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.判断数据是否重复： SELECT user_name , over, count(*) FROM user1_test GROUP BY user_name, over HAVING count(*)>1 2.删除重复数据，对于相同数据保留ID最大的 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 3.将表中查询的数据再次插入到表中 INSERT INTO xmss_wh(XM_ID,WH_ID,YJ,SH,SHR,FJ_NAME,FJ_ID) SELECT XM_ID,WH_ID,YJ,SH,SHR,FJ_NAME,FJ_ID FROM xmss_wh WHERE ID=28; sql中的group by 用法解析： Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。 它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。 --注意:group by 是先排序后分组； --举例子说明：如果要用到group by 一般用到的就是“每这个字” 例如说明现在有一个这样的表：每个部门有多少人 就要用到分组的技术 select DepartmentID as '部门名称', COUNT(*) as '个数' from BasicDepartment group by DepartmentID 这个就是使用了group by +字段进行了分组，其中我们就可以理解为我们按照了部门的名称ID DepartmentID将数据集进行了分组；然后再进行各个组的统计数据分别有多少； 如果不用count(*)等类似函数 select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID 将会报错查看全部
-
用sql来生成特殊的序列号 虽然看不懂，先记下来，用的时候可以来好好研究研究查看全部
-
生成序列号 1.最好的是系统提供的序列号生成方式，比较好，但是有缺点就是可能出现序列空洞。 2.在特殊情况下可以使用sql方式生成序列号： 如一些商品编号：查看全部
-
序列化方式进行行转列： 1.首先我们先建立一个序列表 2.思路： 首先我们通过cross join 将user1_equipment 和 序列表 进行笛卡尔积，变成最终有多少条记录 c.id 必须小于等于user1_equipment的列数 再通过case语句，分别取c.id=1,2,3...的对应的行的值，加上coalesce取不为空的数据 最后加上对应的列名， 示例如下： SELECT user_name, CASE WHEN c.id = 1 THEN 'arms' WHEN c.id = 2 THEN 'clothing' WHEN c.id = 3 THEN 'shoe' END AS equipment, COALESCE( CASE WHEN c.id = 1 THEN arms END CASE WHEN c.id = 2 THEN clothing END CASE WHEN c.id = 3 WHEN shoe END ) AS eq_name FROM user1 a JOIN user1_equipment b ON a.id=b.user_id CROSS JOIN tb_sequence c WHERE c.id <= 3 ORDER BY user_name;查看全部
-
实现如图的列转行的方法： 1.使用union all 将结果集 合并 SELECT user_name,'arms' as equipment, arms FROM user1 a JOIN user1_equipment b ON a.id=b.user_id UNION ALL SELECT user_name,'clothing' AS equipment,clothing FROM user1 a JOIN user1_equipment b ON a.id=b.user_id UNION ALL SELECT user_name,'shoe' AS equipment,shoe FROM user1 a JOIN user1_equipment b ON a.id=b.user_id查看全部
-
列转行技巧： 1.首先我们加一张序列号表，所谓序列号表就是一张有一个自增字段的表，如id 1,2,3,4,5,6...... 2.使用这条sql语句，这句有点整不懂。 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,','))+1 size FROM user1 b) b ON a.id <= b.size查看全部
-
2.使用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查看全部
-
使用cross join 方式是实现行列转换： 1.统计每一个用户打怪的总数 select sum(kills) as '沙僧' from user1 a left join user_kills b on a.id = b.user_id where a.username='沙僧'; select sum(kills) as '猪八戒' from user1 a left join user_kills b on a.id = b.user_id where a.username='猪八戒'; select sum(kills) as '孙悟空' from user1 a left join user_kills b on a.id = b.user_id where a.username='孙悟空'; 2.使用cross join 进行笛卡尔积连接，将三组数据进行行转列组合 select * from (select sum(kills) as '沙僧' from user1 a left join user_j=kills b on a.id = b.user_id and a.username='沙僧') a cross join (select sum(kills) as '猪八戒' from user1 a left join user_j=kills b on a.id = b.user_id and a.username='猪八戒') b cross join (select sum(kills) as '孙悟空' from user1 a left join user_j=kills b on a.id = b.user_id and a.username='孙悟空') c 总结：这种方法虽然可以转换行列，但是遇到需要组合的数据太多编写起来就比较繁琐，而且查询效率比较低！查看全部
举报
0/150
提交
取消