亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

@MySQL---用SQL生成特殊的序列號

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---生成唯一序列號方式

優先使用系統提供的生成序列號方式
MySQL：AUTO_INCREMENT
SQLServer：INENTITY/SEQUENCE
Oracle：SEQUENCE
PgSQL：SEQUENCE

@MySQL---使用序列化實現列轉行

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方法實現列轉行

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---利用序列表轉行的數據

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語句實現行列轉換

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---使用自連接實現行轉列
1.比如成績
分別查詢出不同同學的成績，并將字段名改為同學的名字。
通過交叉連接，將不同的語句連接起來。

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.缺點：是將原來查詢的結果每一行單獨查詢出來，再進行拼接。
因此每增加一個同學就增加一個SELECT語句。并且是通過交叉連接，
要保證每個查詢的結果只能是一個，不然沒辦法通過交叉連接實現轉換。

行轉列

case when

cross join 行轉列

行轉列

我們的例子

行轉列場景2

行轉列場景1

最后的行列轉換在oracle中沒有實現

進行數據表中的行行轉換，統計表格中的數據

行轉列：cross join 利用自身連接來實現： 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、分別查詢出不同同學的成績，并將字段名改為同學的名字 2、通過交叉連接，將不同的語句連接起來 缺點：是將原來查詢的結果每一行單獨查詢出來，再進行拼接。因此每增加一個同學就增加一個SELECT語句。并且是通過交叉連接，要保證每個查詢的結果只能是一個，不然沒辦法通過交叉連接實現轉換。