工作中用到的sqlserver语句

SQLSERVER无排序生成序号

    --实现方式：ROW_NUMBER()
    SELECT RowID=(ROW_NUMBER() OVER(ORDER BY(SELECT 0))) FROM 表名

case when的使用方法

Case具有两种格式。简单Case函数和Case搜索函数。

 第一种 格式 : 简单Case函数 :

格式说明    
　　　　case 列名
　　　　when   条件值1   then  选项1
　　　　when   条件值2    then  选项2.......
　　　　else     默认值      end

eg:
　　　　select 
　　　　case 　　job_level
　　　　when     '1'     then    '1111'
　　　　when　  '2'     then    '1111'
　　　　when　  '3'     then    '1111'
　　　　else       'eee' end
　　　　from     dbo.employee

 第二种  格式 :Case搜索函数
 
格式说明    

　　　　case  
　　　　when  列名= 条件值1   then  选项1
　　　　when  列名=条件值2    then  选项2.......
　　　　else    默认值 end

eg:
　　　　update  employee
　　　　set         e_wage =
　　　　case
　　　　when   job_level = '1'    then e_wage*1.97
　　　　when   job_level = '2'   then e_wage*1.07
　　　　when   job_level = '3'   then e_wage*1.06
　　　　else     e_wage*1.05
　　　　end
--用身份证号字段判断 男女：　　　
（18位）：
update 表名  set sex= CASE when   Substring(身份证号字段,17,1) %2 = 1 then 1 else 0 END
（18和15位）：
CASE when case when len(s_sfzhm)=15 then Substring(s_sfzhm,15,1) else Substring(s_sfzhm,17,1) END %2 = 1 then '男' else '女' END
--更多案例：https://www.cnblogs.com/zhuyeshen/p/10917397.html

INSERT SELECT语句

1.此语句的作用是将SELECT语句的结果插入表中,可实现数据迁移
2.语法:insert into 插入的表名(字段1,字段2,字段3) select 被查询的字段1,被查询的字段2 from 被查询的表名;
3.先查看需要插入的表的所有数据

sqlserver 把SELECT结果集中一列的所有的值 用逗号隔开放进一个字段内

SELECT  STUFF(( SELECT  ',' +convert(VARCHAR, USER_NAME) FROM TE_USER FOR XML PATH('')), 1, 1, '') AS UserName
--语句原理：https://www.cnblogs.com/blazeZzz/p/7359575.html

创建临时表（简单）

1:
CREATE TABLE #t1 (
[userid] varchar(10) NULL ,
[Num] varchar(10) NULL
)
insert into  #t1 (userid,num) values('张三','888')

2:
declare @a table (年龄 int,身高 int,学历 varchar(1))
insert into @a
select 12,170,'A' union all
select 11,150,'B' union all
select 10,160,'C'
--> 测试数据: @b
declare @b table (年龄1 int,年龄2 int,身高1 int,身高2 int,学历 varchar(1))
insert into @b
select 12,13,140,145,'B' union all
select 11,13,170,180,'a' union all
select 12,13,170,180,'A'

select * from @a
select * from @b

IF ELSE

不能用在SELECT中，只能是块，比如:

IF …

BEGIN

…（代码块）

END

ELSE （注意这里没有ELSE IF，要实现只能在下面的块中用IF判断）

BEGIN

…（代码块）

END

列：

    declare @num int --定义变量
    set @num=1 --赋值变量
    if(@num>10)
    begin
    select * from 表1
    end
    else
    begin
    if(@num<0)
    select Top(20) * from 表2
    else
    print @num
    end

创建变量循环一个表（用于多条数据插入和传值查询）

--drop table #t3
CREATE TABLE #t3 (
[userid] varchar(10) NULL ,
[cNum] varchar(10) NULL
)
--drop table #t4
CREATE TABLE #t4 (
[userid] varchar(10) NULL ,
[course] varchar(255) NULL
)

--根据临时表循环处理
declare @userid varchar(10)
declare @subjectID varchar(10)
declare @Studykindid varchar(10)
declare @CourseStr  varchar(255)
declare @coursename varchar(255)

declare rs0 cursor for

select subjectID,Studykindid,userid
from #t2 

open rs0
fetch next from rs0 into @subjectID,@Studykindid,@userid
while @@fetch_status=0
begin

	--统考已通过门数	
	--enableCourseStr=getEnableCourseIDs(Studykindid,SubjectID) 
	--TkeNum=getTkCourseidNum(enableCourseStr,UserID)
	if(@Studykindid <> 'a01' and  @Studykindid <> 'a02' )
	begin
		select @CourseStr	= Case when courseIDs2 <> ''  then  courseIDs2  else courseIDs end  from 
		 (select courseIDs,
				(select courseIDs from GJTK_StuCourseLimited  where studykindid= l.studykindid 
						and subjectID = @subjectID
				) as courseIDs2
			from GJTK_StuCourseLimited l
			where studyKindID = @Studykindid  and (subjectid = '' or subjectid is null)
		  )as courseIDs
		insert into  #t3 (userid,cNum) 
		select  stuid as userid,isnull(count(courseid),0) as cNum   from (
			select distinct stuid,courseid 
			from GJTK_StuSelectCourse 
			where score='合格' and stuid='@userid' and courseid IN (@CourseStr) 
			union 
			select distinct v.stuid,g.courseid from V_GJTK_Apply v 
			inner join GJTK_CourseSys g on v.TongkaoCourse=g.course 
			where v.courseStatus='已通过' and v.stuid='@userid' and g.courseid IN (@CourseStr) 
		) a  GROUP BY a.stuid
	end
	
	
	--未选的必修课
	SELECT @coursename =  STUFF(( SELECT  '、' +convert(VARCHAR,course ) FROM 	 
	(select 
	 s.course 
	FROM
		subjectcoursesys s
	INNER JOIN coursekindnumtwolist c ON s.CourseKindIDNumTwo = c.CourseKindIDNumTwo 
	WHERE
		s.subjectid = @subjectID
		and c.typeid = 1
	except 
	select s.course  from studentcoursesys stu
	INNER JOIN subjectcoursesys s on  s.courseID =stu.courseID
	where  userID = @userid
	)as aaa
	FOR XML PATH('')), 1, 1, '')

	insert into  #t4 (userid,course) values(@userid,@coursename)

fetch next from rs0 into  @subjectID,@Studykindid,@userid
end
close rs0
deallocate rs0