-
利用group by 和having從句查找重復(fù)數(shù)據(jù) select user_name,count(*) form user1_test group by user_name having count(*)>1 刪除重復(fù)數(shù)據(jù)，保留重復(fù)數(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查看全部
-
使用存儲(chǔ)過程生成唯一訂單號(hào)，每秒中可生成1千個(gè)查看全部
-
優(yōu)先選擇系統(tǒng)提供的序列號(hào)生成方式 mysql的auto_increment的方式如果使用事務(wù)回滾會(huì)產(chǎn)生空洞情況查看全部
-
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;查看全部
-
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查看全部
-
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 use_kills b on a.id = b.user_id;查看全部
-
使用序列化方法轉(zhuǎ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 as a cross join( select user_name, concat(mobile,',') as mobile, --在mobile結(jié)尾增加逗號(hào) length(mobile)-length(replace(mobile,',',''))+1 as size --通過mobile總長(zhǎng)度-排除逗號(hào)后的長(zhǎng)度+1計(jì)算得出總段數(shù) from user1 as b ) as b on a.id<=b.size查看全部
-
使用union all連接轉(zhuǎn)換： 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.判斷數(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 * 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ī)則將一個(gè)數(shù)據(jù)集劃分成若干個(gè)小的區(qū)域，然后針對(duì)若干個(gè)小區(qū)域進(jìn)行數(shù)據(jù)處理。 --注意:group by 是先排序后分組； --舉例子說明：如果要用到group by 一般用到的就是“每這個(gè)字” 例如說明現(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ò)查看全部
-
211·查看全部
-
通過存儲(chǔ)過程和事務(wù)等確保生成唯一序列號(hào)?？梢酝ㄟ^創(chuàng)建一張表，記錄某天的流水號(hào)最大是什么，然后通過某一個(gè)數(shù)可以疊加起來。 DECLARE v_cnt INT; DECLARE v_timestr INT; DECLARE rowcount BIGINT; SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d'); SELECT ROUND(RAND()*100,0)+1 INTO v_cnt; START TRANSACTION; UPDATE order_seq SET order_sn = order_sn + v_cnt WHERE timestr = v_timestr; IF ROW_COUNT() = 0 THEN INSERT INTO order_seq(timestr,order_sn) VALUES(v_timestr,v_cnt); END IF; SELECT CONCAT(v_timestr,LPAD(order_sn,7,0))AS order_sn FROM order_seq WHERE timestr = v_timestr; COMMIT;查看全部
-
序列化方式進(jìn)行行轉(zhuǎn)列：<br> 1.首先我們先建立一個(gè)序列表<br> 2.思路：<br> 首先我們通過cross join 將user1_equipment 和 序列表 進(jìn)行笛卡爾積，變成最終有多少條記錄 c.id 必須小于等于user1_equipment的列數(shù)<br> 再通過case語(yǔ)句，分別取c.id=1,2,3...的對(duì)應(yīng)的行的值，加上coalesce取不為空的數(shù)據(jù)<br> 最后加上對(duì)應(yīng)的列名，<br> 示例如下：<br> SELECT user_name,<br> CASE WHEN c.id = 1 THEN 'arms'<br> WHEN c.id = 2 THEN 'clothing'<br> WHEN c.id = 3 THEN 'shoe'<br> END AS equipment,<br>//生成裝備列 COALESCE(<br> CASE WHEN c.id = 1 THEN arms END<br> CASE WHEN c.id = 2 THEN clothing END<br> CASE WHEN c.id = 3 WHEN shoe END <br> ) AS eq_name<br>//coalesce取不為空的數(shù)據(jù)，那條不為空取那一條 FROM user1 a<br> JOIN user1_equipment b ON a.id=b.user_id<br> 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=user_id<br> union all<br> select user_name,'clothing' as equipment, clothing from user1 a join user1_equipment b on a.id=user_id;<br> union all<br> select user_name,'shoe' as equipment, shoe from user1 a join user1_equipment b on a.id=user_id;查看全部
-
列轉(zhuǎn)行另一種場(chǎng)景查看全部
-
列轉(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//根據(jù)逗號(hào)進(jìn)行字符串的截取 FROM tb_sequence a CROSS JOIN ( //數(shù)據(jù)表的連接 select user_name,concat(mobile,',') as mobile,//concat在字符串后面添上逗號(hào) length(mobile)-length(replace(mobile,',',''))+1 as size //顯示出逗號(hào)的個(gè)數(shù) from user1 as b) as b on a.id<=b.size查看全部
舉報(bào)
0/150
提交
取消