A SQL statement about the query-CodePudding

Table course:
Id the title
1 mathematical
2 the art
3 sports
4 the martial arts

Table student:
Id name courselist
Zhang SAN 1, 2, 1
2 li si 3
3 fifty 2 and 4
Zhao four six 3, 4

I want to query results, please the great god show avatar
Id name coursestr
1 zhang SAN mathematics, fine arts
2 li si sports
3 fifty and fine arts, sports, martial arts
Zhao four six sports, martial arts

CodePudding user response:

 
The CREATE TABLE t_course (id INT, title VARCHAR (50)) 
INSERT INTO t_course 
SELECT 1, 'mathematics' UNION ALL 
SELECT 2, 'art' UNION ALL 
SELECT 3, 'sports' UNION ALL 
SELECT 4, 'martial arts' 

CREATE TABLE t_student (INT id, name VARCHAR (50), courselist VARCHAR (50)) 
INSERT INTO t_student 
SELECT 1, 'zhang', '1', 2 'UNION ALL 
SELECT 2, 'bill', '3' UNION ALL 
SELECT 3, 'Cathy', '2' 4 'UNION ALL 
SELECT 4, 'zhao 6', '3, 4' 

SELECT 
T1. Id, t1. The name, 
Courselist=the SUBSTRING (t1) courselist, t2. Number, CHARINDEX (', ', t1. The courselist + ', ', t2. Number) - t2. Number) 
INTO # T1 
The FROM t_student t1, master.. Spt_values t2 
WHERE t2. Type='P' AND CHARINDEX (', ', ', '+ t1 courselist, t2. Number)=t2. Number 
GROUP BY t1. Id, t1. The name, t1. Courselist, 
The SUBSTRING (t1) courselist, t2. Number, CHARINDEX (', ', t1. Courselist + ', ', t2. Number) - t2. Number) 

SELECT t. *, tc title 
INTO # T2 
The FROM # T1 t 
LEFT the JOIN t_course tc ON tc ourselist=tc id 

SELECT 
Id, name, 
Coursestr=(SELECT STUFF (SELECT ', '+ title FROM # T2 WHERE id=T2. Id FOR XML PATH ('')), 1, 1, ')) 
The FROM # T2 T2 
GROUP BY id, name 

# DROP TABLE T1 
DROP TABLE # T2 


Id name coursestr 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 zhang SAN mathematics, fine arts 
2 li si sports 
3 fifty and fine arts, sports, martial arts 
Zhao four six sports, martial arts 

(4 rows affected)