-
列轉行場景查看全部
-
使用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 總結：這種方法雖然可以轉換行列，但是遇到需要組合的數據太多編寫起來就比較繁瑣，而且查詢效率比較低！查看全部
-
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查看全部
-
方法一 簡單可以效率不高 修改也麻煩查看全部
-
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行轉列的二查看全部
-
行轉列的場景一報表統計查看全部
-
sql技巧二查看全部
-
刪除重復數據 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查看全部
-
列轉行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; ========================================== 列轉行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;查看全部
-
列轉行技巧： 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查看全部
-
使用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查看全部
-
刪除重復查看全部
-
列轉行查看全部
舉報
0/150
提交
取消