-
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
提交
取消