方法一： use master; go --判斷數(shù)據(jù)庫Test是否存在 if exists(select * from sys.databases where name='Test') drop database Test;? --存在則刪除 go --創(chuàng)建Test數(shù)據(jù)庫 create database Test; go --使用Test數(shù)據(jù)庫 use Test; go --判斷數(shù)據(jù)庫Test是否存在emp表 if exists(select * from sys.tables where name='emp') drop table emp;? --存在則刪除 go --創(chuàng)建emp表 create table emp( eid int primary key identity(10001,1), ecd varchar(20) null,? -- 要求是YC0001? YC0002且有系統(tǒng)根據(jù)eid自動生成 ); go --判斷當前實例是否存在觸發(fā)器tri_ecd if exists(select * from sys.triggers where name='tri_ecd') drop trigger tri_ecd; go --創(chuàng)建觸發(fā)器用來實現(xiàn)工牌號的根據(jù)eid自動生成 create trigger tri_ecd? -- 創(chuàng)建觸發(fā)器tri_ecd on emp?? --在emp表中 after insert? -- 插入操作后觸發(fā)此觸發(fā)器，執(zhí)行下面的操作 as declare @eid int;? --聲明一個變量eid，用來存放最新插入的數(shù)據(jù)的eid declare @ecd varchar(14); --聲明一個變量ecd，用來存放截取的eid --set @eid=(select max(eid) from emp);? --從emp表中查詢出最新插入的數(shù)據(jù)的eid --select @eid=@@identity; set @eid= (select ident_current(‘emp‘));?? ---獲取emp表中最新生成的標識列的值 set @ecd=substring(cast(@eid as varchar),2,len(@eid)); --講整型的eid轉(zhuǎn)為字符型，然后截取除掉前面的1 update emp set ecd='YC'+@ecd where eid=@eid; -- 更新emp表中的ecd列 go insert into emp values(''); insert into emp values(''); insert into emp values(''); select * from emp; 效果： eid????????????? ecd 10001???? YC0001 10002???? YC0002? 10003???? YC0003方法二： create table dept( did varchar(20) primary key, dname varchar(20) ); go drop trigger tri_did; go create trigger tri_did on dept after insert as declare @did varchar(20); set @did=(select top 1 did from dept where did!='1' order by did desc); if((select count(*) from dept)<=1) begin update dept set did='YC10001'; end else begin declare @temp varchar(20); declare @num int; set @temp=substring(@did,3,len(@did)); set @num=(cast(@temp as int)+1); update dept set did=('YC'+cast(@num as varchar)) where did='1'; end go insert into dept values('1','a');? --注意：did列初始值必須是1 insert into dept values('1','b'); insert into dept values('1','c');

效果：
??? did?????????????? dname
YC10001?????????????? a
YC10002?????????????? b
YC10003?????????????? c

create trigger tri_did on dept after insert as declare @did varchar(20); select top 1 @did=did from dept where did!=1 order by did desc; if((select count(*) from dept)<=1) begin update dept set did='S001' end else begin declare @temp varchar(10); set @temp=stuff((cast((cast(replace(@did,'S',1) as int)+1) as varchar)),1,1,'S'); update dept set did=@temp where did='1'; end

?

--觸發(fā)器 模擬日志 create table users( uid int identity primary key, uname varchar(20), pwd varchar(20) ); go create table loginfo( lid int identity primary key, logininfo varchar(100), ldate date ); go if exists(select * from sys.triggers where name='tri_loginfo') drop trigger tri_loginfo; go create trigger tri_loginfo on users after insert,delete as declare @info varchar(100); if exists(select 1 from inserted) begin select @info='用戶 '+uname+' 添加成功' from users; end; if exists(select 1 from deleted) begin select @info='用戶 '+uname+' 刪除成功' from users; end; --insert into loginfo values((select ident_current('users')),@info,getDate()); --ident_current('users')? users表最新生成的標識列的值 --insert into loginfo values((select @@identity),@info,getDate()); go

關(guān)閉標識列
set identity_insert 表名 on;?

select case e.sex when 'M' then'男' when 'F' then '女' end 性別 from temp; --替換顯示 select sname,case sex when 'M' then'男' when 'F' then '女' end 性別 from stuinfo; 統(tǒng)計列印各科成績,各分數(shù)段人數(shù):課程名稱,[100-85],[85-70],[70-60],[ <60] , select * from score select cname,sum(case when score between 85 and 100 then 1 else 0 end) as [100 - 85], sum(case when score between 70 and 85 then 1 else 0 end) as[85 - 70], sum(case when score between 60 and 70 then 1 else 0 end) as [70 - 60], sum(case when score < 60 then 1 else 0 end) as [60 -0] from score,course group by cname; --去掉補考的 select cname,sum(case when score between 85 and 100 then 1 else 0 end) as [100 - 85], sum(case when score between 70 and 85 then 1 else 0 end) as [85 - 70], sum(case when score between 60 and 70 then 1 else 0 end) as [70 - 60], sum(case when score < 60 then 1 else 0 end) as [60 -0] from ( select studentid,courseid,cid,cname,max(score) as score from score,course where courseid=cid group by studentid,courseid,cid,cname) oo ) where courseid=cid group by cname; 查找學生姓名和出生日期，如果出生日期為空值，顯示日期不詳,并按部門排序輸出,日期格式為yyyy-mm-dd。 select sname? 姓名,isnull(CONVERT(char(10),e.BirthDate),'日期不詳') 出生日期 from stuinfo; --查詢選修了兩個以上課程的學生姓名 --1.先從score中查出選修了兩門以上的學生的學號,去掉補考的 --2.通過查出來的學號從stuinfo中查出姓名 select sname from stuinfo stu where stu.stuid in( select sc.studentid from score sc group by sc.studentid having COUNT(distinct(courseid)>=2); --查出沒有選修1號課程的學生信息 select * from stuinfo where stuid not in(select sc.studentid from score sc where sc.courseid=1); select * from stuinfo stu? where not exists( select * from score where stu.stuid=score.studentid and score.courseid=1); --查詢選修了所有課程的學生信息(不存在這樣的課程，他沒有參加選修) select * from stuinfo where not exists(--不存在這樣的學生不在 select * from course where not exists(? --不存在這樣的課程不在學生的選修課成績單中 select * from score where score.courseid=course.cid and stuinfo.stuid=score.studentid)); select * from stuinfo where stuid in( select studentid from score group by studentid having COUNT(distinct(courseid)=(select count(*) from course)); --查詢至少選修了2號學生所有課程的學生信息 select * from stuinfo where not exists( select * from course where course.cid in( --查出課程2號學生學修了的課程 select score.courseid from score where score.studentid=2) and not exists( select * from score where score.studentid=stuinfo.stuid and cid=score.courseid)); --選修了2號課程以上的學生信息 select * from stuinfo where stuid in(select studentid from score where courseid=2)? --選修了2號課程的 select * from stuinfo where stuid in( select studentid from score group by studentid having count(distinct(courseid)>1 and studentid in( select * from stuinfo where stuid in(select studentid from score where courseid=2))); --5檢索至少選修了1號和2的課程的學生信息 select * from stuinfo,score where studentid=stuid and courseid=1 and stuid in(select studentid from score where courseid=2); select distinct * from stuinfo sf1,score sc1,score sc2 where stuid=sc1.studentid and stuid=sc2.studentid and sc1.courseid=1 and sc2.courseid=2; --查詢學生成績，包括姓名，課程名，成績,如有補考，只顯示最高的一次 select sname,cname,max(score) from score,course,stuinfo where courseid=cid and studentid=stuid? group by sname,cname

?

轉(zhuǎn)載于:https://www.cnblogs.com/navyzhou/archive/2013/05/07/3065714.html

總結(jié)

以上是生活随笔為你收集整理的sql下员工工牌（YC0001）的简单实现和一些特殊查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。