-
行列轉(zhuǎn)換，cross join查看全部
-
行轉(zhuǎn)列的關(guān)鍵是union ,列轉(zhuǎn)行的關(guān)鍵是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語(yǔ)句進(jìn)行行列轉(zhuǎn)換 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查看全部
-
這種重復(fù)數(shù)據(jù)如何刪除？查看全部
-
1.判斷數(shù)據(jù)是否重復(fù)： SELECT user_name , over, count(*) FROM user1_test GROUP BY user_name, over HAVING count(*)>1 2.刪除重復(fù)數(shù)據(jù)，對(duì)于相同數(shù)據(jù)保留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.將表中查詢的數(shù)據(jù)再次插入到表中 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語(yǔ)句從英文的字面意義上理解就是“根據(jù)(by)一定的規(guī)則進(jìn)行分組(Group)”。 它的作用是通過(guò)一定的規(guī)則將一個(gè)數(shù)據(jù)集劃分成若干個(gè)小的區(qū)域，然后針對(duì)若干個(gè)小區(qū)域進(jìn)行數(shù)據(jù)處理。 --注意:group by 是先排序后分組； --舉例子說(shuō)明：如果要用到group by 一般用到的就是“每這個(gè)字” 例如說(shuō)明現(xiàn)在有一個(gè)這樣的表：每個(gè)部門有多少人 就要用到分組的技術(shù) select DepartmentID as '部門名稱', COUNT(*) as '個(gè)數(shù)' from BasicDepartment group by DepartmentID 這個(gè)就是使用了group by +字段進(jìn)行了分組，其中我們就可以理解為我們按照了部門的名稱ID DepartmentID將數(shù)據(jù)集進(jìn)行了分組；然后再進(jìn)行各個(gè)組的統(tǒng)計(jì)數(shù)據(jù)分別有多少； 如果不用count(*)等類似函數(shù) select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID 將會(huì)報(bào)錯(cuò)查看全部
-
用sql來(lái)生成特殊的序列號(hào) 雖然看不懂，先記下來(lái)，用的時(shí)候可以來(lái)好好研究研究查看全部
-
生成序列號(hào) 1.最好的是系統(tǒng)提供的序列號(hào)生成方式，比較好，但是有缺點(diǎn)就是可能出現(xiàn)序列空洞。 2.在特殊情況下可以使用sql方式生成序列號(hào)： 如一些商品編號(hào)：查看全部
-
序列化方式進(jìn)行行轉(zhuǎn)列： 1.首先我們先建立一個(gè)序列表 2.思路： 首先我們通過(guò)cross join 將user1_equipment 和 序列表 進(jìn)行笛卡爾積，變成最終有多少條記錄 c.id 必須小于等于user1_equipment的列數(shù) 再通過(guò)case語(yǔ)句，分別取c.id=1,2,3...的對(duì)應(yīng)的行的值，加上coalesce取不為空的數(shù)據(jù) 最后加上對(duì)應(yīng)的列名， 示例如下： 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;查看全部
-
實(shí)現(xiàn)如圖的列轉(zhuǎn)行的方法： 1.使用union all 將結(jié)果集 合并 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查看全部
-
列轉(zhuǎn)行技巧： 1.首先我們加一張序列號(hào)表，所謂序列號(hào)表就是一張有一個(gè)自增字段的表，如id 1,2,3,4,5,6...... 2.使用這條sql語(yǔ)句，這句有點(diǎn)整不懂。 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語(yǔ)句進(jìn)行行列轉(zhuǎn)換 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 方式是實(shí)現(xiàn)行列轉(zhuǎn)換： 1.統(tǒng)計(jì)每一個(gè)用戶打怪的總數(shù) 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 進(jìn)行笛卡爾積連接，將三組數(shù)據(jù)進(jìn)行行轉(zhuǎn)列組合 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 總結(jié)：這種方法雖然可以轉(zhuǎn)換行列，但是遇到需要組合的數(shù)據(jù)太多編寫起來(lái)就比較繁瑣，而且查詢效率比較低！查看全部
舉報(bào)
0/150
提交
取消