-
uuu查看全部
-
并發(fā)性數(shù)據(jù)查看全部
-
#慕課最后一題 CREATE TEMPORARY TABLE im_tuser AS ( SELECT c.id, replace( substring( substring_index(c.phone, ',', a.id), char_length( substring_index(c.phone, ',', a.id - 1) ) + 1), ',', '') AS phone FROM im_sequence AS a CROSS JOIN ( SELECT b.id, concat(b.phone, ',') AS phone, length(b.phone) - length(replace(b.phone, ',', '')) + 1 AS size FROM im_userrepect AS b ) AS c ON a.id <= c.size ); 我設(shè)計(jì)的表只有id和phone，以上是列轉(zhuǎn)行存為臨時(shí)表查看全部
-
完善一下存儲(chǔ)過(guò)程，添加表 #唯一序列號(hào) 獲取當(dāng)前年月日+3位流水號(hào) #用orser_seq判斷插入最大值 DROP PROCEDURE IF EXISTS usp_seqnum; CREATE PROCEDURE usp_seqnum() BEGIN #定義變量并獲取相關(guān)值 DECLARE v_cnt INT; DECLARE v_timestr INT; SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d'); SELECT ROUND(RAND()*100,0)+1 INTO v_cnt; #新建表 DROP TABLE IF EXISTS im_orderseq; CREATE TABLE im_orderseq( timestr NVARCHAR(8) NOT NULL , ordersn INT(3) ); START TRANSACTION; #更新表的最值 UPDATE im_orderseq SET ordersn = ordersn + v_cnt WHERE timestr = v_timestr; IF ROW_COUNT() = 0 THEN #插入數(shù)據(jù) INSERT INTO im_orderseq(timestr,ordersn) VALUES(v_timestr,v_cnt); END IF; SELECT CONCAT(v_timestr,LPAD(ordersn,7,0))AS ordersn FROM im_orderseq WHERE timestr = v_timestr; COMMIT; END; CALL usp_seqnum(); SELECT * FROM im_orderseq;查看全部
-
分享一個(gè)序列表的存儲(chǔ)過(guò)程 DROP PROCEDURE IF EXISTS usp_getseqtable; CREATE PROCEDURE usp_getseqtable( IN id INT ) BEGIN DECLARE i INT; DROP TABLE IF EXISTS im_sequence; CREATE TABLE im_sequence ( id INT AUTO_INCREMENT PRIMARY KEY )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; SET i=1; WHILE i <= id DO #插入表數(shù)據(jù) INSERT INTO im_sequence VALUES (NULL); SET i = i + 1; END WHILE; END; CALL usp_getseqtable(20)查看全部
-
特殊sql生成序列號(hào)查看全部
-
列行轉(zhuǎn)換查看全部
-
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ò)一定的規(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ò)查看全部
-
更復(fù)雜的重復(fù)刪除：屬性中有相同的字段 1、想將屬性通過(guò)單列轉(zhuǎn)多行得到新的表 2、再刪除重復(fù)數(shù)據(jù) 3、再利用GROUP BY CONCAT回復(fù)成原表查看全部
-
判斷數(shù)據(jù)是否重復(fù)：GROUP BY column_name HAVING COUNT(*)>=2; 通過(guò)多表刪除 GROUP BY后，相當(dāng)于形成一個(gè)一個(gè)單獨(dú)的表查看全部
-
存儲(chǔ)過(guò)程，訂單號(hào)： 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; 知識(shí)點(diǎn)： 1、在sql語(yǔ)句中添加變量。 declare @local_variable data_type 聲明時(shí)需要指定變量的類型，可以使用SET、SELECT、SELECT...INTO對(duì)變量進(jìn)行賦值，在sql語(yǔ)句中就可以使用@local_variable來(lái)調(diào)用變量。 2、RAND()返回一個(gè)介于 0 到 1（不包括 0 和 1）之間的偽隨機(jī) float 值。 3、事務(wù) 4、ROW_COUNT()函數(shù)返回查詢語(yǔ)句執(zhí)行后，被影響的列數(shù)目 5、IF...THEN...END IF;查看全部
-
唯一序列號(hào)： 場(chǎng)景：數(shù)據(jù)庫(kù)主鍵、業(yè)務(wù)序列號(hào) 優(yōu)先選擇系統(tǒng)提供的序列號(hào)生成方式。當(dāng)可能會(huì)有空洞產(chǎn)生。 特殊情況下可使用SQL方式生成序列號(hào)，如要求有一定規(guī)律，不一定由數(shù)字構(gòu)成 系統(tǒng)生成方法： rollback數(shù)據(jù)回滾查看全部
-
使用序列化表進(jìn)行行列轉(zhuǎn)換。查看全部
-
可使用UNION來(lái)實(shí)現(xiàn)多列轉(zhuǎn)行查看全部
舉報(bào)
0/150
提交
取消