第七色在线视频,2021少妇久久久久久久久久,亚洲欧洲精品成人久久av18,亚洲国产精品特色大片观看完整版,孙宇晨将参加特朗普的晚宴

@MySQL---用SQL生成特殊的序列號(hào)

CREATE?TABLE?order_seq(
timestr?INT?UNSIGNED,
order_sn?INT?UNSIGNED,
);DELIMITER?//CREATE?PROCEDURE?seq_no()BEGINDECLARE?v_cnt?INT?UNSIGNED;DECLARE?v_timestr?INT?UNSIGNED;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?THENINSERT?INTO?order_seq(timestr,order_sn)?VALUES(v_timestr,v_cnt);END?IF;SELECT?CONCAT(v_timestr,LPAD(order_sn,7,0))AS?order_snFROM?order_seq?WHERE?timestr?=?v_timestr;COMMIT;END//DELIMITER?;CALL?seq_no();

@MySQL---生成唯一序列號(hào)方式

優(yōu)先使用系統(tǒng)提供的生成序列號(hào)方式
MySQL：AUTO_INCREMENT
SQLServer：INENTITY/SEQUENCE
Oracle：SEQUENCE
PgSQL：SEQUENCE

@MySQL---使用序列化實(shí)現(xiàn)列轉(zhuǎ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;

@MySQL---使用UNION方法實(shí)現(xiàn)列轉(zhuǎn)行

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?ORDER?BY?user_name;

@MySQL---利用序列表轉(zhuǎn)行的數(shù)據(jù)

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,',')?AS?mobile,
LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1?size
FROM?user1?b)?b?ON?a.id<=b.size;

@MySQL---使用CASE語句實(shí)現(xià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;

@MySQL---使用自連接實(shí)現(xiàn)行轉(zhuǎn)列
1.比如成績
分別查詢出不同同學(xué)的成績，并將字段名改為同學(xué)的名字。
通過交叉連接，將不同的語句連接起來。

SELECT?*
FROM?(
?SELECT?SUM(kills)?AS?'a'
?FROM?a?INNER?JOIN?b?ON?a.name=b.user_name
?WHERE?a.name='a')?AS?a;
CROSS?JOIN(
?SELECT?SUM(kills)?AS?'b'
?FROM?a?INNER?JOIN?b?ON?a.name=b.user_name
?WHERE?a.name='b')?AS?b;
CROSS?JOIN(
?SELECT?SUM(kills)?AS?'c'
?FROM?a?INNER?JOIN?b?ON?a.name=b.user_name
?WHERE?a.name='c')?AS?c;

2.缺點(diǎn)：是將原來查詢的結(jié)果每一行單獨(dú)查詢出來，再進(jìn)行拼接。
因此每增加一個(gè)同學(xué)就增加一個(gè)SELECT語句。并且是通過交叉連接，
要保證每個(gè)查詢的結(jié)果只能是一個(gè)，不然沒辦法通過交叉連接實(shí)現(xiàn)轉(zhuǎn)換。

行轉(zhuǎn)列

case when

cross join 行轉(zhuǎn)列

行轉(zhuǎn)列

我們的例子

行轉(zhuǎn)列場景2

行轉(zhuǎn)列場景1

最后的行列轉(zhuǎn)換在oracle中沒有實(shí)現(xiàn)

進(jìn)行數(shù)據(jù)表中的行行轉(zhuǎn)換，統(tǒng)計(jì)表格中的數(shù)據(jù)

行轉(zhuǎn)列：cross join 利用自身連接來實(shí)現(xiàn)： SELECT * FROM ( SELECT SUM(KILLS) AS 'A' FROM A INNER JOIN B ON A.NAME=B.USER_NAME WHERE A.NAME='A') AS A CROSS JOIN( SELECT SUM(KILLS) AS 'B' FROM A INNER JOIN B ON A.NAME=B.USER_NAME WHERE A.NAME='B') AS B CROSS JOIN( SELECT SUM(KILLS) AS 'C' FROM A INNER JOIN B ON A.NAME=B.USER_NAME WHERE A.NAME='C')AS C; 比如成績 1、分別查詢出不同同學(xué)的成績，并將字段名改為同學(xué)的名字 2、通過交叉連接，將不同的語句連接起來 缺點(diǎn)：是將原來查詢的結(jié)果每一行單獨(dú)查詢出來，再進(jìn)行拼接。因此每增加一個(gè)同學(xué)就增加一個(gè)SELECT語句。并且是通過交叉連接，要保證每個(gè)查詢的結(jié)果只能是一個(gè)，不然沒辦法通過交叉連接實(shí)現(xiàn)轉(zhuǎn)換。