第九堂：家事管理 - 使用INSERT INTO產生隨機排班資料 (Window Function-Ranking Function、UUID()、RAND() )

第 11 屆 iThome 鐵人賽

DAY 9

Software Development

從問題理解與活用SQL語法系列第 9 篇

第九堂：家事管理 - 使用INSERT INTO產生隨機排班資料 (Window Function-Ranking Function、UUID()、RAND() )

11th鐵人賽

通靈亡

2019-09-25 21:47:01

5403 瀏覽

分享至

一、回顧：從每張表的CREATE TABLE 活用常見的欄位限制(主鍵、外來鍵、索引、唯一)

(一)主鍵 (PRIMARY KEY)

適用情境：用來明確識別在資料當中的每一筆資料，是不同的資料。每筆資料的主鍵不能重複
主鍵常見的使用型態：

int
GUID/UUID

例如：每一個排班紀錄都有一個不重複的流水號(CleanScheduleId)

(二) 外來鍵 (FOREIGN KEY)

適用情境：用來存放來別張資料表的資料主鍵，透過JOIN合併資料
常見的外來鍵參考對象的刪除/更新規則：

連鎖反應做法 CASCADE:
來源的資料被刪除了，有關聯來源的資料也會一併刪除。
限制性做法 RESTRICT:
有關聯來源的資料如果沒有被刪除，來源的資料無法刪除
虛值化做法 SET NULL:
有關聯來源的資料如果沒有被刪除，來源的資料刪除後，有關聯來源的資料值將被修改為NULL

例如：每一筆實際做家事紀錄(CleanRecord) 都有在排班表(CleanScheduleId)對應的排班流水號 (CleanScheduleId)

(三) 索引 (INDEX)

適用情境：設定在常用來當作查詢條件的欄位
目的：藉由資料庫的演算法提高查詢速度
成本：占用額怪的磁碟空間

例如：家事排班表的做家事日期欄位(CleanDateTime)，經常被拿來當作查詢統計的條件

題目

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

根據「Orders」資料表裡面的資料，撰寫Orders的Create Table SQL，並且在每個欄位加入適當的欄位限制。

參考答案

CREATE TABLE `Orders` (
	`OrderID` int AUTO_INCREMENT PRIMARY KEY,
    `CustomerID` VARCHAR(50),
    `EmployeeID` VARCHAR(50),
    `OrderDate` DateTime,
    CONSTRAINT `FK_Orders_Customers` FOREIGN KEY (`CustomerID`) REFERENCES `Customers` (`CustomerID`),
    CONSTRAINT `FK_Orders_Employees` FOREIGN KEY (`EmployeeID`) REFERENCES `Employees` (`EmployeeID`),
    INDEX `OrderDate` (`OrderDate`)
)

二、第九堂範例：產生隨機排班資料到排班表

需求

隨機產生某個日期的家事排班資料

實作結果

1.不支援Window Function：MySQL 5.7以下 MariaDB 10.1以下

SET @rownum = 0;
SELECT UUID() AS CleanScheduleId,
		 '2019-09-25' AS CleanDateTime,
       FamilyData.FamilyId,
       @rownum:= @rownum + 1   AS CleanItem
FROM (SELECT Family.FamilyId
	  FROM Family
	  ORDER BY RAND()) AS FamilyData

2. 支援Window Function： MySQL 8.0 以上、 MariaDB 10.2

INSERT INTO CleanSchedule
SELECT UUID() AS CleanScheduleId,
	   '2019-09-25' AS CleanDateTime,
	   FamilyId,
	   Row_Number() OVER(ORDER BY FamilyID) AS CleanItem
FROM Family
ORDER BY RAND();

3. SQL Server 2012 以上

INSERT INTO CleanSchedule
SELECT NEWID() AS CleanScheduleId,
	   DATEADD(DAY, 0, '2019-08-25') AS CleanDateTime,
       FamilyID AS FamilyID,
	   ROW_NUMBER() OVER(ORDER BY NEWID()) - 1 AS CleanItem
FROM Family

三、認識 INSERT INTO 語法

新增指定值到指定資料表的指定欄位

INSERT INTO 指定的資料表名稱 (欄位1, 欄位2, 欄位3, ...)
VALUES (欄位1的值, 欄位2的值, 欄位3的值, ...);

2.將查詢的結果新增到指定資料表

INSERT INTO table2 (欄位1, 欄位2, 欄位3, ...)
SELECT 欄位1的值, 欄位2的值, 欄位3的值, ...
FROM table1
WHERE condition;

四、不使用Window Function的思維

將 SELECT 的資料新增到某個資料表：Insert INTO SELECT

INSERT INTO CleanSchedule
SELECT ...

排序亂數，讓每次的FamilyId前後順序都不一樣：Order By + RAND() 或 Order by + UUID()

SQL Server 是產生GUID的 NEWID()，MySQL是產生UUID的 UUID()
[解釋] uuid 與 guid 的差異
http://blog.caesarchi.com/2014/03/06/-e8-a7-a3-e9-87-8b-uuid--e8-88-87-guid--e7-9a-84-e5-b7-ae-e7-95-b0/

FROM (SELECT Family.FamilyId
	  FROM Family
	  ORDER BY RAND()) AS FamilyData

第一欄使用UUID()產生家事排班編號，並將剛才取得打亂的成員資料給予1~6的家事項目編號

SELECT UUID(),
		 '2019-09-25' AS CleanDate,
       FamilyData.FamilyId,
       @rownum:= @rownum + 1   AS CleanItem

五、使用Window Function 的思維

從Family取得所有的家庭成員資料後，接著使用Row_Number() OVER(ORDER BY RAND()) 子句，對每個家庭成員給予亂數的家事分工項目。

SELECT UUID() AS CleanScheduleId,
	   '2019-09-25' AS CleanDateTime,
	   FamilyId,
	   Row_Number() OVER(ORDER BY RAND()) AS CleanItem
FROM Family

六、延伸閱讀：認識Window Function

(一) Window Function 的語法結構

<function> OVER (        [PARTITION BY clause]
                         [ORDER BY clause]
                         [ROWS or RANGE clause])

(二) Window Function 的用途

在資料庫中，「分析函式」是計算一組資料列匯總值的函式。分析函式會對一組輸入資料列計算函式來傳回每個資料列的單一值，而匯總函式則是會傳回一組資料列的單一匯總值，這是兩者之間的差異。

分析函式可以透過簡要的方式來表示複雜的分析運算作業，是一種功能十分強大的機制，而且可以提供有效率的評估方式，若以其他方式執行評估，會涉及耗費大量資源的 Self-JOIN (自我連結)，或者必須在 SQL 查詢之外進行運算。

分析函式在 SQL 標準及某些商用資料庫中又稱為「(分析) 窗型函式」。這是因為分析函式會對一組資料列進行評估，這稱為 window 或 window frame。在其他某些資料庫中，它們可能稱為「線上分析處理」(OLAP) 函式。

SQL Window Function(一)：概述、Ranking Function
https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts?hl=zh-tw#window-clause
[SQL]T-SQL 的 Window Function 中使用與 OVER 的搭配
https://louis176127.pixnet.net/blog/post/201580879-sql-window-function%28%E4%B8%80%29%EF%BC%9A%E6%A6%82%E8%BF%B0%E3%80%81ranking-function

七、相關情境練習

使用資料表：

課堂八的四張家事管理資料表

題目：

使用Insert Into SELECT 產生實際做家事的資料

範本資料：

/* 家庭成員資料表 */
-- 傾印  表格 ithome2019_familydb.family 結構
CREATE TABLE `Family` (
  `FamilyId` varchar(100) NOT NULL,
  `FamilyName` varchar(10) DEFAULT NULL,
  `FamilySex` varchar(10) DEFAULT NULL,
  `BirthDate` datetime DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `Family` (`FamilyId`, `FamilyName`, `FamilySex`, `BirthDate`, `PhoneNumber`) VALUES
	('34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '泰肝', '女生', '2007-10-11 00:00:00', ''),
	('3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '泰熱', '男生', '2001-12-10 00:00:00', '0934567890'),
	('91b18f1f-4ef8-4066-97c4-28daea585db5', '泰胖', '女生', '2003-05-13 00:00:00', ''),
	('91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '泰瘦', '女生', '1985-07-10 00:00:00', '0944623456'),
	('bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '泰冷', '男生', '1995-01-23 00:00:00', '0977654258'),
	('de8cb5db-2061-4d35-a662-ba5f528fadba', '泰賢', '男生', '1977-02-03 00:00:00', '0944589456');
    
/* 家事項目清單 */
CREATE TABLE `CleanItemList` (
  `CleanItem` varchar(50) NOT NULL,
  `ItemName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CleanItem`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `CleanItemList` (`CleanItem`, `ItemName`) VALUES
	('0', '打掃'),
	('1', '拖地'),
	('2', '洗碗'),
	('3', '倒垃圾');

/* 家事排班一覽表 */
CREATE TABLE `CleanSchedule` (
  `CleanScheduleId` varchar(100) NOT NULL,
  `CleanDateTime` datetime DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `CleanItem` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CleanScheduleId`),
  KEY `FK_cleanschedule_family` (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `CleanSchedule` (`CleanScheduleId`, `CleanDateTime`, `FamilyId`, `CleanItem`) VALUES
	('0208B1E1-4F72-4545-8722-E220894526BB', '2019-08-17 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('046188FB-B13C-45B9-9EA0-0A998B24263A', '2019-08-13 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '5'),
	('0C7CF82D-D75A-452A-9775-39A47A0E1F08', '2019-08-15 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '5'),
	('10DCAA5C-5E44-416D-A758-1846DFA72837', '2019-08-20 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('150A422A-3A65-4404-9C9A-61A820C13D17', '2019-08-23 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '1'),
	('1D0CCF2A-B5F9-4A3D-B701-328B26CFB522', '2019-08-22 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '5'),
	('2049F029-0CAA-4249-99C4-5F647B09526D', '2019-08-20 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('2B7E17DA-E144-4832-B6B9-A54C1EE4FA49', '2019-08-15 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('2D682CA3-B900-41B0-9AD9-5611296DFBE5', '2019-08-18 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('34931A84-85E7-4236-B1C4-01D190EEFE27', '2019-08-16 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '0'),
	('34D5124E-21FE-4A74-BD25-1190B063D0EE', '2019-08-14 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '3'),
	('37F40275-D8DD-43A2-B37E-2DB649A8BA3B', '2019-08-22 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '3'),
	('384343F7-AC7E-4C71-BFC9-9D5AFCE58113', '2019-08-19 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '4'),
	('3A37A6D9-8ED8-4DF2-BB67-B71560DA1D34', '2019-08-14 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('3CC296C1-172B-4EBB-81C0-67B1A849B978', '2019-08-14 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('3FDF9E95-82EB-4A91-811F-870DA2C0F788', '2019-08-13 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('43483F07-889A-4BA8-B0D5-149FBCD270F8', '2019-08-14 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '5'),
	('4508A7DA-A570-4918-86DF-591686233A9C', '2019-08-15 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('4B4E212B-C40B-4233-B3B3-3531D6FE7915', '2019-08-18 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('56AA47E8-E0E0-4224-8EA9-96950B6B7AC5', '2019-08-15 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('5B29E024-BB11-4336-ADD7-60443851F599', '2019-08-23 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '0'),
	('5CD82BE7-E711-4BB1-B1C5-7D729CAAB02D', '2019-08-20 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '0'),
	('5CFD5FD4-1999-4EBA-B31B-5A2BD1B8C0E5', '2019-08-19 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '3'),
	('5EF225E5-DE30-46A0-AFF9-2A9DA7D5AF1E', '2019-08-15 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('6698F721-F6E6-4C58-93E9-1EC39FC3A50D', '2019-08-13 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '4'),
	('685D2679-4C17-4FB9-9FB8-EC6F856DA121', '2019-08-17 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('695A1809-37DE-416E-970E-DF43857ADC14', '2019-08-20 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '5'),
	('70AD02FA-9FD7-4AB6-B22C-D3D33F48CD54', '2019-08-13 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '3'),
	('71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', '2019-08-16 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '1'),
	('74A6DFBE-FFE8-4013-9C7B-E17640D36475', '2019-08-21 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '4'),
	('7BE1E533-FDE3-40EA-9A4F-E7638BA1E168', '2019-08-18 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('7E54D4BD-3DD2-4696-8DBC-BCCB0FD71EE2', '2019-08-17 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '1'),
	('83595DE1-12FE-453D-9DDD-6ED7D0A355F4', '2019-08-18 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('83B129E4-E37F-4F1F-B013-C2B2CECC8181', '2019-08-20 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('843D27D9-CB11-446B-AF65-563C641D872B', '2019-08-16 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '3'),
	('8C1F882A-A36B-4023-B311-25D7EC1F8EA8', '2019-08-17 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '3'),
	('905AEEEF-2750-4934-9CE5-87416027F09B', '2019-08-22 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '1'),
	('98ADC0FA-508E-4117-8075-4567995A8C45', '2019-08-19 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '1'),
	('9B1B4243-2C42-4268-A4FB-A5FE95DF57E6', '2019-08-21 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '1'),
	('9B3946B3-C184-4EFE-B005-568D930101FD', '2019-08-14 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '4'),
	('9EFD2E6E-15E9-48CE-B8C9-75033D75AD00', '2019-08-14 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('A1596044-F368-46CB-9D2F-8C286C51BEF9', '2019-08-22 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '4'),
	('A25F668E-5E12-4E15-A71D-9ACBC51200A5', '2019-08-13 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '1'),
	('A47EA3A8-3EAC-4171-92B2-B91B5478C957', '2019-08-21 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2'),
	('ABA175B5-2865-4396-8B0A-39E889A8FAE0', '2019-08-21 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '0'),
	('AE3D7002-EB24-4105-ABB3-FB7ABB9E175A', '2019-08-19 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '0'),
	('BE41D9A4-358B-41E6-A016-9829701E2DCF', '2019-08-17 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '5'),
	('C0F392D2-0461-4589-88A9-7E4C3E24C79E', '2019-08-19 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('C23CAA9C-8C63-4F74-8251-1A0773B18104', '2019-08-23 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2'),
	('C464A299-5EF7-4999-B4F2-578B20BBB524', '2019-08-23 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '5'),
	('C6727911-38B7-40F8-9C4F-730F652A67D0', '2019-08-22 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('C78F99F6-37B6-4FD5-AD04-D586BF032D1C', '2019-08-16 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('CDEC8205-C50F-4688-BF10-3600A2689753', '2019-08-21 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '5'),
	('CE8F3CFB-5959-451B-94B9-95CFBE77DAFB', '2019-08-19 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '5'),
	('CEC240B4-7988-479F-BEEE-7D39999067B3', '2019-08-17 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '0'),
	('D420FFDD-6BAE-435F-85AE-57D917EA63ED', '2019-08-18 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '3'),
	('D60541EA-5A4E-46F9-8322-BDE6A0E5380D', '2019-08-21 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '3'),
	('E2EC6280-307F-4E47-BA9E-0A08EF4602DD', '2019-08-23 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '3'),
	('EF29679A-4952-4F37-AE3F-8F2A14A5A0FF', '2019-08-13 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('F7B09E72-69BA-4D00-A157-5E36DD1ED4CE', '2019-08-15 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('F802E6A4-5F10-4985-BFD2-0D5C4435EF6A', '2019-08-18 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('F80F67C5-6849-4FF7-B2C5-739268BD51BF', '2019-08-23 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '4'),
	('FA20D45B-9B64-4C61-9148-270FF13CF38E', '2019-08-20 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '4'),
	('FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '2019-08-16 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('FA796E8C-5B52-46DF-8910-F23080B9ECA7', '2019-08-22 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '0'),
	('FF257219-DB3E-4CBB-8E63-C5A85B09950A', '2019-08-16 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '4;');


/* 實際做家事紀錄表 */
CREATE TABLE `CleanRecord` (
  `CleanRecordId` varchar(100) NOT NULL,
  `CleanScheduleId` varchar(100) DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `StartTime` datetime DEFAULT NULL,
  `EndTime` datetime DEFAULT NULL,
  PRIMARY KEY (`CleanRecordId`),
  KEY `FK_cleanrecord_cleanschedule` (`CleanScheduleId`),
  KEY `FK_cleanrecord_family` (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `CleanRecord` (`CleanRecordId`, `CleanScheduleId`, `FamilyId`, `StartTime`, `EndTime`) VALUES
	('1BBFDF7D-D064-4091-925B-0541C27BD321', '5CD82BE7-E711-4BB1-B1C5-7D729CAAB02D', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-20 18:12:00', '2019-08-20 19:02:00'),
	('38F23254-67F5-4AB5-A8DE-929501AB802E', '150A422A-3A65-4404-9C9A-61A820C13D17', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-23 18:52:00', '2019-08-23 20:19:00'),
	('457B8655-44A1-4F59-8B45-7DB0FD2E1A8E', 'AE3D7002-EB24-4105-ABB3-FB7ABB9E175A', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-19 18:30:00', '2019-08-19 19:32:00'),
	('4911A9BD-BE90-4174-990B-A72498678A92', '8C1F882A-A36B-4023-B311-25D7EC1F8EA8', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-17 18:09:00', '2019-08-17 19:56:00'),
	('49292BD5-27D1-40E8-8A64-73F6A99211C9', '2049F029-0CAA-4249-99C4-5F647B09526D', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-20 18:21:00', '2019-08-20 19:12:00'),
	('4A182CCE-4267-4451-A456-3A0B4CD3ED36', '2B7E17DA-E144-4832-B6B9-A54C1EE4FA49', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-15 18:04:00', '2019-08-15 19:34:00'),
	('4E10D641-A5E9-4FCF-A2EF-5B1896A091D2', '56AA47E8-E0E0-4224-8EA9-96950B6B7AC5', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-15 18:43:00', '2019-08-15 19:23:00'),
	('5E4206B9-1078-41FF-BF54-A2920A37D843', 'C6727911-38B7-40F8-9C4F-730F652A67D0', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-22 18:59:00', '2019-08-22 20:21:00'),
	('65B732DE-AE07-45E6-8D14-C356E1BDEBE9', '3CC296C1-172B-4EBB-81C0-67B1A849B978', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-14 18:10:00', '2019-08-14 19:40:00'),
	('675FFDEF-1000-4507-87A6-8DAEDD2247BD', 'D60541EA-5A4E-46F9-8322-BDE6A0E5380D', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-21 18:01:00', '2019-08-21 19:43:00'),
	('6E2CB5C0-FDD3-4ABA-B38B-BC4915A458EE', '37F40275-D8DD-43A2-B37E-2DB649A8BA3B', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-22 18:14:00', '2019-08-22 20:19:00'),
	('7353332C-2E99-48CB-900F-BDE2B7FE39B6', '83B129E4-E37F-4F1F-B013-C2B2CECC8181', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-20 18:29:00', '2019-08-20 19:57:00'),
	('79D7DF92-8A68-4AF1-B5FE-7733EEB495E6', '905AEEEF-2750-4934-9CE5-87416027F09B', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-22 18:44:00', '2019-08-22 19:21:00'),
	('7D4085F9-614D-41FB-AE16-1442E13DEACD', '3A37A6D9-8ED8-4DF2-BB67-B71560DA1D34', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-14 18:31:00', '2019-08-14 19:25:00'),
	('8A284F82-BB85-4B10-A56A-4B07B79A2D80', 'D420FFDD-6BAE-435F-85AE-57D917EA63ED', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-18 18:37:00', '2019-08-18 20:21:00'),
	('8FBCF266-C104-4113-B3DB-461ACAD3048B', '843D27D9-CB11-446B-AF65-563C641D872B', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-16 18:38:00', '2019-08-16 19:40:00'),
	('A7960AAC-6F6D-4B58-94BD-2253253C572D', 'E2EC6280-307F-4E47-BA9E-0A08EF4602DD', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-23 18:10:00', '2019-08-23 20:16:00'),
	('A8D86DAE-4135-4090-9D57-04F2D585DD61', '98ADC0FA-508E-4117-8075-4567995A8C45', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-19 18:09:00', '2019-08-19 20:11:00'),
	('ADF35E1E-1760-4012-98CF-CE0B5751C096', 'A25F668E-5E12-4E15-A71D-9ACBC51200A5', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-13 18:09:00', '2019-08-13 19:16:00'),
	('B56F7CC2-EC30-4EB0-9969-5058FDE390AC', 'F7B09E72-69BA-4D00-A157-5E36DD1ED4CE', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-15 18:15:00', '2019-08-15 19:02:00'),
	('BA54CEF9-55EC-449B-BA32-E48AA84EAFD1', '10DCAA5C-5E44-416D-A758-1846DFA72837', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-20 18:03:00', '2019-08-20 19:51:00'),
	('DAF64DBF-0B83-40CF-A616-D7DD8A538C7C', '9B1B4243-2C42-4268-A4FB-A5FE95DF57E6', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-21 18:41:00', '2019-08-21 20:19:00'),
	('DB564B2A-F025-43D9-8D27-8E14E717FB4C', '2D682CA3-B900-41B0-9AD9-5611296DFBE5', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-18 18:47:00', '2019-08-18 20:07:00'),
	('DE5FA643-1485-4CC5-B913-82CC5A989929', '9EFD2E6E-15E9-48CE-B8C9-75033D75AD00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-14 18:48:00', '2019-08-14 20:12:00'),
	('E5BE6337-DDEB-4107-A5A6-FAD1F3DD4422', 'FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-16 18:52:00', '2019-08-16 19:36:00'),
	('E8A0FFAB-BADD-4DCD-8278-F32BD0C06922', '7E54D4BD-3DD2-4696-8DBC-BCCB0FD71EE2', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-17 18:45:00', '2019-08-17 19:59:00'),
	('EA538BCF-FE8E-45A8-9BEF-7C08ABBFCB60', '71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-16 18:27:00', '2019-08-16 19:49:00');

第八堂：家事管理 - 從每張表的CREATE TABLE 活用常見的欄位限制(主鍵、外來鍵、索引、唯一)

第十堂：家事管理 - 使用Update修改休息的成員排班 (UPDATE + WHERE IN 相同的表格)

系列文

從問題理解與活用SQL語法共 30 篇

RSS系列文訂閱系列文

56 人訂閱

完整目錄

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

1064 組

團體組數

40 組

累計文章數

22189 篇

完賽人數

602 人

15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js

IT邦幫忙

從問題理解與活用SQL語法系列 第 9 篇

第九堂：家事管理 - 使用INSERT INTO產生隨機排班資料 (Window Function-Ranking Function、UUID()、RAND() )

一、回顧：從每張表的CREATE TABLE 活用常見的欄位限制(主鍵、外來鍵、索引、唯一)

(一)主鍵 (PRIMARY KEY)

(二) 外來鍵 (FOREIGN KEY)

(三) 索引 (INDEX)

題目

參考答案

二、第九堂範例：產生隨機排班資料到排班表

需求

實作結果

1.不支援Window Function：MySQL 5.7以下 MariaDB 10.1以下

2. 支援Window Function： MySQL 8.0 以上、 MariaDB 10.2

3. SQL Server 2012 以上

三、認識 INSERT INTO 語法

四、不使用Window Function的思維

五、使用Window Function 的思維

六、 延伸閱讀：認識Window Function

(一) Window Function 的語法結構

(二) Window Function 的用途

七、 相關情境練習

使用資料表：

題目：

範本資料：

尚未有邦友留言

標記使用者

從問題理解與活用SQL語法系列第 9 篇

六、延伸閱讀：認識Window Function

七、相關情境練習