按老师的ID分类后,将对应的班级ID用 “/” 拼接起来
Sqlserver
SELECT 字段1,
[随便来个名字就行] = stuff((
SELECT '/' + [年级2] FROM aa t
WHERE t.老师ID = aa.老师ID FOR xml path('')) , 1 , 1 , '')
FROM aa
GROUP BY 老师ID
语法逻辑:网上找的其他博客,写的很清楚。
STUFF参数拓展:
Mysql、Oracle
SELECT
老师ID,
group_concat(年级ID separator '/')class,
FROM table_name
GROUP BY 老师ID
SqlServer举例:
create table #a_aa_a(
teacher_id int,
teacher_name varchar(10),
teacher_class varchar(10)
)
insert into #a_aa_a VALUES (1,'张老师','一年级')
insert into #a_aa_a VALUES (1,'张老师','二年级')
insert into #a_aa_a VALUES (1,'张老师','三年级')
insert into #a_aa_a VALUES (2,'李老师','四年级')
insert into #a_aa_a VALUES (2,'李老师','五年级')
insert into #a_aa_a VALUES (2,'李老师','六年级')
insert into #a_aa_a VALUES (3,'托尼老师','幼儿一班')
insert into #a_aa_a VALUES (3,'托尼老师','幼儿二班')
insert into #a_aa_a VALUES (3,'托尼老师','幼儿三班')
Sql:
SELECT teacher_id,'班级' = stuff((
SELECT '/' + teacher_class FROM #a_aa_a t
WHERE t.teacher_id = aa.teacher_id FOR xml path('')) , 1 , 1 , '')
FROM #a_aa_a aa
GROUP BY teacher_id
结果
备注:
之前在有的博客上看到用的不是stuff函数,而是replace函数
首先,这俩个都是替换函数
区别:
官方语法:
REPLACE ( string_expression , string_pattern , string_replacement )
将string_expression 字符串中的string_pattern全部替换为string_replacement
STUFF ( character_expression , start , length , replaceWith_expression )
在character_expression字符串中,从下标为start(从1开始)长度为length的字段替换为replaceWith_expression
所以在上述案例中,如果用REPLACE替换函数,不符合需求,要用stuff()
--------------------------------------------------------------------------------------------
mysql拼接俩个字段
concat()函数
sqlserver拼接俩个字段
直接用+来拼接,有需要的符号如/、-等符号