SQL MAKE_SET 函数

一、SQL MAKE_SET 函数 语法

SQL语言中的 MAKE_SET 字符串函数从多个值的集合中返回给定位的值。

在 SQL 中,我们可以对表的列、字符串和字符使用 MAKE_SET 函数。

语法1:

SELECT MAKE_SET(bits, Column_Name1, column_Name2, Column_Name3,……. Column_NameN) AS Alias_Name FROM Table_Name;  

在这种语法中,我们将 MAKE_SET 函数与现有的 SQL 表一起使用。在这里,我们必须定义要在其上执行 MAKE_SET 函数的表的名称和列。

语法2:

SELECT MAKE_SET(bits, "String1", "String2", "String3", …… "StringN") AS Alias_Name;  

语法3:

SELECT MAKE_SET(bits, "Character_1", "character_2", "Character_3", ……., "Character_N") AS Alias_Name; 

二、SQL MAKE_SET 函数 示例

示例 1:以下查询使用带有字符串列表的 MAKE_SET 函数:

SELECT MAKE_SET( 1, ''H'', ''I'', ''A'', ''P'', ''Q'', ''S'', ''R'', ''T'', ''V'', ''M'' ) AS Value_at1st_bit;

输出结果为:

Value_at1st_bit
H

示例 2:以下查询在第 1 位和第 4 位搜索字符串:

SELECT MAKE_SET(1 | 4, ''New'', ''Delhi'', ''is'', ''the'', ''Capital'', ''of'', ''India'') AS Value_at1st_4th_bit;  

输出结果为:

Value_at1st_4th_bit
New, is

示例 3:以下查询显示给定集合中第二位的值:

SELECT MAKE_SET( 2, ''H'', ''I'', ''V'', ''M'' ) AS Value_at2nd_bit;  

输出结果为:

Value_at2nd_bit
I

示例 4:以下查询显示给定集合中第二位和第四位的值:

SELECT MAKE_SET( 2 | 4, ''H'', ''I'', ''A'', ''P'', ''Q'', ''S'', ''R'', ''T'', ''V'', ''M'' ) AS Value_at2_4_bit;  

输出结果为:

Value_at2_4_bit
I, A

示例 5:此示例对结构化查询语言中的表使用 MAKE_SET 函数。

在第五个示例中,我们将创建新表,通过该表我们将使用表值执行 MAKE_SET 函数:

以下块显示了在 SQL 中创建新表的语法:

CREATE TABLE Name_of_New_Table  
(  
First_Column_of_table Data Type (character_size of First Column),    
Second_Column_of_table Data Type (character_size of the Second column ),    
Third_Column_of_table Data Type (character_size of the Third column),    
........,    
Last_Column_of_table Data Type (character_size of the Last column)  
);   

以下 CREATE 语句创建Fresher_Marks表:

CREATE TABLE Fresher_Marks  
(  
Fresher_ID INT PRIMARY KEY,    
First_Name VARCHAR (100),    
Middle_Name Varchar(120),  
Last_Name VARCHAR (200),   
City Varchar(120),  
Aptitude_Marks INT,   
Reasoning_Marks INT,   
Technical_Marks INT,  
Percentage INT  
);  

下面的 INSERT 语句在Fresher_Marks表中插入带有标记和详细信息的新生记录:

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (501, Vinay, Roy, Gupta, Lucknow, 85, 92, 78, 85 );  
  
INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (502, Monu, Roy, Singhania, Chandigarh, 54, 68, 98, 88 );  
  
INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (504, Ravi, Roy, Kumar, Lucknow, 71, 82, 69, 71 );  
  
INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (507, Shyam, Roy, Sharma, Delhi, 85, 90, 68, 78 );  
  
INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (510, Abhay, Kumar, Gupta, Chandigarh, 45, 68, 82, 78);  
  
INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (509, Riya, Roy, Sharma, Delhi, 68, 90, 69, 91 );  
  
INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (505, Vishal, Kumar, Sharma, Mumbai, 75, 65, 88, 75 );  

以下 SELECT 语句显示上述Fresher_Marks表的插入记录:

SELECT * FROM Fresher_Marks;   

输出结果为:

Fresher_ID First_Name Middle_Name Last_Name City Aptitude_Marks Reasoning_Marks Technical_Marks Percentage
501 Vinay Roy Gupta Lucknow 85 92 78 85
502 Monu Roy Singhania Chandigarh 54 68 98 88
504 Ravi Roy Kumar Lucknow 71 82 69 71
507 Shyam Roy Sharma Delhi 85 90 68 78
510 Abhay Kumar Gupta Chandigarh 45 68 82 78
509 Riya Roy Sharma Delhi 68 90 69 91
505 Vishal Kumar Sharma Mumbai 75 65 88 75

查询 1:以下 SELECT 查询将 MAKE_SET 函数与上述 Fresher_Marks 表的 First_Name、Middle_Name、Last_Name 列一起使用:

SELECT First_Name, Middle_Name, Last_Name, MAKE_SET(2, First_Name, Middle_Name, Last_Name) AS Value_at_2bit FROM Fresher_Marks;

此查询显示上表中每个新生的中间名的值。

输出结果为:

First_Name Middle_Name Last_Name Value_at_2bit
Vinay Roy Gupta Roy
Monu Roy Singhania Roy
Ravi Roy Kumar Roy
Shyam Roy Sharma Roy
Abhay Kumar Gupta Kumar
Riya Roy Sharma Roy
Vishal Kumar Sharma Kumar

查询 2:以下 SELECT 查询将 MAKE_SET 函数与上述 Fresher_Marks 表中 Fresher_ID 大于 502 的学生的 Aptitude_Marks、Reasoning_Marks 和 Technical_Marks 列一起使用:

SELECT Fresher_ID, Aptitude_Marks, Reasoning_Marks, Technical_Marks, MAKE_SET(4, Aptitude_Marks, Reasoning_Marks, Technical_Marks) AS Value_at_4bit FROM Fresher_Marks WHERE Fresher_ID > 502;  

输出结果为:

Fresher_ID Aptitude_Marks Reasoning_Marks Technical_Marks Value_at_4bit
504 71 82 69 69
507 85 90 68 68
510 45 68 82 82
509 68 90 69 69
505 75 65 88 88

查询 3:以下 SELECT 查询将 MAKE_SET 函数与上述 Fresher_Marks 表的 First_Name、Middle_Name、Last_Name 列一起使用:

SELECT First_Name, Middle_Name, Last_Name, MAKE_SET(1 | 3, First_Name, Middle_Name, Last_Name) AS Value_at_1_3bit FROM Fresher_Marks;  

此查询显示上表中每个新生的中间名的值。

输出结果为:

First_Name Middle_Name Last_Name Value_at_1_3bit
Vinay Roy Gupta Vinay, Roy
Monu Roy Singhania Monu, Roy
Ravi Roy Kumar Ravi, Roy
Shyam Roy Sharma Shyam, Roy
Abhay Kumar Gupta Abhay, Kumar
Riya Roy Sharma Riya, Roy
Vishal Kumar Sharma Vishal, Kumar

热门文章

优秀文章