目录
(1)《两张表》(年级课程两张表互相通过GradeID进行关联的)
(3)《共五张表》(年级课程两张表互相通过GradeID进行关联的-------年级表与课程表通过应该年级课程绑定表进行关联的)
一、判断表中是否存在这条数据,存在修改,不存在新增
INSERT INTO employee (ID, name, birthdate) VALUES (1001, '小明', '1999-01-01')
ON DUPLICATE KEY UPDATE name='小明', birthdate='1999-01-01'
二、查询语句生成序号(根据某一列)
select row_number() over (order by col1) as 序号,col1 as 列1, col2 as 列2 from Table1
三、STUFF 用法
我对SQLserver 中STUFF函数的理解是在sql server中将字符串中的第一个字符串某一部分字符替换成另外一部分,组成新的字符串数据。
STUFF(character_expression,Start,Length,character_expression)
参数说明:
character_expression:字符串数据,或字符串组成的表达式。
Start:开始替换的位置,start 必须大于0,否则返回NULL.
Length,替换长度.若长度为0,则是拼接,不删除任何原字符串中的内容
character_expression:具体替换的内容,若为空的话,就是删除第一个字符串中的指定位置指定长度的数据。
例:
(1)《两张表》(年级课程两张表互相通过GradeID进行关联的)
select distinct(g.GradeID),g.GradeName,g.GradeNumber,(SELECT STUFF(( SELECT ',' +convert(VARCHAR, ClassName) FROM Class c where c.GradeID = g.GradeID FOR XML PATH('')), 1, 1, '') AS UserName) as ClassName FROM Grade g left join Class c on g.GradeID = c.GradeID
效果展示:
(2)《三张表》(年级课程和年级课程绑定表)
select distinct(g.GradeID),g.GradeName,g.GradeNumber,(SELECT STUFF(( SELECT ',' +convert(VARCHAR, ClassName) FROM Class c where c.GradeID = g.GradeID FOR XML PATH('')), 1, 1, '') AS UserName) as ClassName FROM Grade g left join Class c on g.GradeID = c.GradeID
效果展示:
(3)《共五张表》(年级课程两张表互相通过GradeID进行关联的-------年级表与课程表通过应该年级课程绑定表进行关联的)
select ROW_NUMBER() over(order by a.GradeID asc) as xh,a.GradeID,a.GradeName,a.GradeNumber,
(select STUFF(( SELECT ',' + CourseTitle FROM Curriculum b1 inner join GradeCurriculumBinding b2 on b1.CurriculumID = b2.CurriculumID where b2.GradeID = a.GradeID FOR XML PATH('')), 1, 1, '')) as CourseTitle,(SELECT STUFF(( SELECT ',' +convert(VARCHAR, ClassName) FROM Class c1 where c1.GradeID = a.GradeID FOR XML PATH('')), 1, 1, ''))as ClassName from Grade a order by a.GradeID
效果展示: