-
bedin; --启动事务 insert into 表名 values (); --向表中插入数据 rollback; --回滚事务查看全部
-
序列号<br> 使用场景：主键、业务主键<br> 生成方法：各个数据库都有各自的支持，如下图，要能保证唯一性就可以认为是一个正确的序列号。当然，也可以人为的来生成，不过唯一性就需要自己来保证了。所以，讲者建议使用系统提供的方式来生成。需要注意的是，使用MySQL系统本身的序列化方式存在产生空洞的情况。查看全部
-
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 then 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;查看全部
-
列转行3 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 then 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;查看全部
-
列转行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; 使用union all连接转换： 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查看全部
-
实现如图的列转行的方法： 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查看全部
-
创建系列表： create table tb_sequence(id int auto_increment not null,primary key(id)); 产生系列好： insert into tb_sequence values(),(),(),(),(),();查看全部
-
向表中添加字段：alter table 表名 add column 字段名 字段类型; 如：alter table test1 add column mobile varchar(100); 使用序列化方法转换： 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结尾增加逗号 length(mobile)-length(replace(mobile,',',''))+1 as size --通过mobile总长度-排除逗号后的长度+1计算得出总段数 from user1 as b ) as b on a.id<=b.size查看全部
-
2.使用case语句进行行列转换<br> select sum(case when user_name='孙悟空' then kills end) as '孙悟空',<br> sum(case when user_name='猪八戒' then kills end) as '猪八戒',<br> sum(case when user_name='沙僧' then kills end) as '沙僧'<br> 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.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查看全部
-
sql开发技巧之行转列的应用场景和基础数据查看全部
-
行转列场景：如报表统计，汇总显示等查看全部
-
使用序列表方式列转行查看全部
-
union列转行查看全部
-
sql生成订单号的一种方式查看全部
举报
0/150
提交
取消