Moment For Technology

A simple but not simple SQL query

Posted on Dec. 2, 2022, 2:46 p.m. by Hayley Collis
Category: The back-end Tag: leetcode

The topic

When Jane books browsing inadvertently see a database query exercises, in fact or helpless, big data, data, since the watch for a few days about programming and programmers, and the content of the home page recommendation is all about the technology, so that what, want to watch the little story emotional class articles are to search, ok. There are no short stories today, so it might be boring (databases after all).

The topic is: search for information about other students who are taking the same course as the student in "01"

First look at the two tables, and then I have sorted out the knowledge points, and finally you can solve the problem (of course, you don't have to use this solution, if you are interested, you can leave your query SQL statement below)

The database table

Students table

Student(SId,Sname,Sage,Ssex) --SId,Sname, Ssex,Ssex

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)); Insert into sc values('01', '01', '01'); Insert into sc values('02', '02', '03 '); Insert into sc values('03', '03', '03'); Insert into sc values(' 03 ', '03', '03 '); Insert into sc values(' 03 ', '03', '03 '); Insert into sc values(' 02 ', '02', '02 '); Insert into sc values(' 01', '01',' 01'); Insert into sc values(' 03 ', '03', '03', '03 '); Insert into sc values(' 03 ', '03', '03 '); Insert into sc values(' 03 ', '03', '03 '); Insert into sc values(' 03 ', '03', '03 '); Insert into sc values(' 03 ', '03', '03 ');Copy the code

League tables

SC(SId,CId, SCORE) --SId student number,CId course number, and SCORE score

Create table SC(SId varchar(10),CId varchar(10),score decimal(18,1)); insert into SC values('01' , '01' , 80); insert into SC values('01' , '02' , 90); insert into SC values('01' , '03' , 99); insert into SC values('02' , '01' , 70); insert into SC values('02' , '02' , 60); insert into SC values('02' , '03' , 80); insert into SC values('03' , '01' , 80); insert into SC values('03' , '02' , 80); insert into SC values('03' , '03' , 80); insert into SC values('04' , '01' , 50); insert into SC values('04' , '02' , 30); insert into SC values('04' , '03' , 20); insert into SC values('05' , '01' , 76); insert into SC values('05' , '02' , 87); insert into SC values('06' , '01' , 31); insert into SC values('06' , '03' , 34); insert into SC values('07' , '02' , 89); insert into SC values('07' , '03' , 98);Copy the code

advice

I suggest you spend some time thinking about how to write this SQL first, or better yet, write it directly.

knowledge

CONCAT(str1,str2)

Concat (str1,str2); concat(str1,str2); If any of the arguments are NULL, the return value is NULL. For example:

mysql select concat('hello','world','! '); +------------------------+ | concat('hello','world','! ') | +------------------------+ | helloworld! | +------------------------+ mysql select concat('hello','world',null); +------------------------+ | concat('hello','world',null) | +------------------------+ | NULL | +------------------------+Copy the code

CONCAT_WS(separator,str1,str2)

Separator is the Separator; if the Separator is NULL, the result will be NULL, but not if str1 or STR2 is NULL. For example:

mysql select concat_ws(',','hello','world','! '); +-------------------------------+ | concat_ws(',','hello','world','! ') | +-------------------------------+ | hello,world,! | +-------------------------------+ mysql select concat_ws(',','hello','world',NULL); +-------------------------------+ | concat_ws(',','hello','world',NULL) | +-------------------------------+ | hello,world| +-------------------------------+Copy the code

GROUP_CONCAT()

Separator () ¶ GROUP_CONCAT(([DISTINCT] column to join [Order BY ASC/DESC sort field] [Separator '])

mysql select group_concat(Sname) from Student group by Ssex; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + group_concat (Sname) | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | zhao, money, Sun Feng, liu yun | | Zhou Mei, nuss, Zheng Zhu, zhang SAN, li si, li si, six, seven sun | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

REPEAT()

To copy strings

mysql select repeat('ab',2);

    +----------------+
| repeat('ab',2) |
+----------------+
| abab           |
+----------------+
Copy the code

The results of

After getting familiar with the knowledge, have you ever felt that it was easy to solve the problem given at the beginning?

SELECT
	* 
FROM
	student 
WHERE
	SId IN (
SELECT
	a. SId
FROM
	( SELECT SId, GROUP_CONCAT( CId ORDER BY CId ) AS gc FROM sc GROUP BY SId ) a 
WHERE
	a.gc = ( SELECT GROUP_CONCAT( CId ORDER BY CId ) FROM sc WHERE SId = '01' ) 
	AND a.SId  '01' 
)
Copy the code

Original text author: ------ artemisia ------ pay attention to me "programmer's broken read" author, will not UI front end is not good do not forget the original heart, keep in mind the mission, for the content of the article have questions or want to in-depth exchange of learning friends can add my QQ: 2283743369

Search
About
mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.