一些复杂的SQL及常用SQL

一些常用的SQL
--获得表的列数
select count(1) from syscolumns where id=object_id('tableName')
--修改表名,把表a改为b
exec sp_rename 'a','b'
--关闭自动增长列
set identity_insert Jxt_DeviceManage_Config on
--关闭外键约束
ALTER TABLE tableName NOCHECK CONSTRAINT ALL
--启用外键约束
ALTER TABLE tableName CHECK CONSTRAINT ALL
SELECT Sex=CASE(sex) WHEN '1' THEN '男' ELSE '女' END FROM tableName

遍历一棵树下所有子节点（no是PK，parentNo是父节点，tblStall是表名）：
CREATE FUNCTION funGetStallChildID(@id INT) --用于遍历子节点的函数
RETURNS @re TABLE(tid INT)
AS
BEGIN
 INSERT INTO @re SELECT [no] FROM tblStall WHERE parentNo=@id
 WHILE @@ROWCOUNT>0
  INSERT INTO @re SELECT a.[no]
   FROM tblStall a INNER JOIN @re b ON a.parentNo=b.tid
   WHERE a.[no] NOT IN(SELECT tid FROM @re)
 RETURN
END

关于课程表的存储过程：
CREATE   TABLE   Curriculum(
        ID                   int                         IDENTITY(1,1),
        UnitCode           varchar(20)         NOT   NULL,   --单位编码
        GradeCode         varchar(20)     NOT   NULL,   --年级编码
        ClassCode         varchar(20)       NOT   NULL,   --班级编码
        WeekDay         smallint               NOT   NULL,   --星期几
        Serial           int                         NOT   NULL,   --节次
        Subject         varchar(10)         NOT   NULL,   --科目编码
        CONSTRAINT   PK407   PRIMARY   KEY   NONCLUSTERED   (ID)
)

insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,1,1,'语文'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,1,2,'数学'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,1,3,'物理'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,1,4,'化学'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,2,1,'政治'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,2,2,'生物'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,2,3,'历史'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,2,4,'地理'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,3,1,'体育'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,3,2,'音乐'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,3,3,'美术'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,3,4,'英语'
insert   into   Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select   1,1,1,4,1,'劳动'

declare   @s   varchar(8000)
set   @s   =   ''
select  
        @s   =   @s   +   ',['+a.item+']   =   max(case   when   WeekDay   =   '+rtrim(WeekDay)+'   and   Serial   =   '+rtrim(Serial)+'   then   Subject   end)'
from
      (select  
                WeekDay,
                Serial,
                ('星期'+rtrim(WeekDay)+'第'+rtrim(Serial)+'节')   as   item  
        from  
                Curriculum  
        group   by  
                WeekDay,Serial)   a
order   by
        a.WeekDay,a.Serial

set   @s   =   'select   ''单位''+GradeCode+''年级''+ClassCode+''班''   as   班   '   +   @s   +   '   from   Curriculum   group   by   UnitCode,GradeCode,ClassCode'

exec(@s)
select * from Curriculum

按季度显示销售量：
CREATE   TABLE   Pivot
(   Year             SMALLINT,
    Quarter       TINYINT,  
    Amount             DECIMAL(2,1)   )

INSERT   INTO   Pivot   VALUES   (1990,   1,   1.1)
INSERT   INTO   Pivot   VALUES   (1990,   2,   1.2)
INSERT   INTO   Pivot   VALUES   (1990,   3,   1.3)
INSERT   INTO   Pivot   VALUES   (1990,   4,   1.4)
INSERT   INTO   Pivot   VALUES   (1991,   1,   2.1)
INSERT   INTO   Pivot   VALUES   (1991,   2,   2.2)
INSERT   INTO   Pivot   VALUES   (1991,   3,   2.3)
INSERT   INTO   Pivot   VALUES   (1991,   4,   2.4)
GO

SELECT   Year,  
        SUM(CASE   Quarter   WHEN   1   THEN   Amount   ELSE   0   END)   AS   Q1,
        SUM(CASE   Quarter   WHEN   2   THEN   Amount   ELSE   0   END)   AS   Q2,
        SUM(CASE   Quarter   WHEN   3   THEN   Amount   ELSE   0   END)   AS   Q3,
        SUM(CASE   Quarter   WHEN   4   THEN   Amount   ELSE   0   END)   AS   Q4
FROM   Pivot
GROUP   BY   Year

关于时间格式的一些写法
IF   EXISTE(SELECT   domain_name   FROM   information_schema.domains
        WHERE   domain_schema='dbo'AND   domain_name='member_no')
    EXEC   sp_droptype   member_no
exec   sp_addtype   mytime,'varchar(20)','null','default   right(char(8),getdate(),120)'
create   rule   rule_zip as  
@x like '[0-9][0-9][0-9][0-9][0-9][0-9]'
CREATE RULE time_rule AS @time like 'yyyy-mm-dd' or len(@time)=0
EXEC sp_addtype type_time,'Datetime'
EXEC sp_bindrule 'time_rule','type_time'
CREATE TABLE SSSSS(SID INT NOT NULL IDENTITY PRIMARY KEY,Times type_time)

insert into SSSSS values('2001-02-03')

exec   sp_addtype   type_zip ,'char(6)','not null'
exec   sp_bindrule   'rule_zip'   ,'type_zip','futureonly'
exec   sp_addtype   N'spbh',N'char(8)',N'NOT NULL'

--创建规则,保证自定义类型的长度
CREATE   RULE   RULE_spbh AS
len(@spbh)=8   --只能是8位
and   @spbh   not   like   '%[^0-9]'   --只能包含数字
go

--将规则绑定到自定义数据类型
exec   sp_bindrule   N'RULE_spbh',N'spbh'
go

--创建表
Create   table  kkkkkkkkk(
spbh   spbh,
spmc   varchar(20)   not   null,
spgg   varchar(20)   not   null,
splx   varchar(20)   not   null,
pfdj   numeric(7,2)   not   null,
kcsl   int   default(0)   not   null)
go

其中我想让spbh为自增列
--用自定义函数

--创建自定义
create function f_nid()
returns char(8)
as
begin
return(right(100000001+isnull((select   max(spbh)   from   kcgl),0),8))
end
go

--建表语句改为:
Create   table   kkkkk2(
spbh   spbh   default   dbo.f_nid(),
spmc   varchar(20)   not   null,
spgg   varchar(20)   not   null,
splx   varchar(20)   not   null,
pfdj   numeric(7,2)   not   null,
kcsl   int   default(0)   not   null)

--插入记录时使用:
insert   kcgl(spmc,...kcsl)   values('ff',...5)

select BuyTime,cast(DATEPART(yy,BuyTime) as varchar(4))+'-'+cast(DATEPART(m,BuyTime)
as varchar(2))+'-'+cast(DATEPART(d,BuyTime) as varchar(2)) as 'Time' from aa22

DECLARE   @str_today   char(10)
SET   @str_today=replace(Convert(char(10),getdate(),120),'-','.')
print   @str_today

Select   GetDate()
Select   Convert(varchar(12),GetDate(),112)

create table aaaaa(a varchar(20),b varchar(20),c varchar(20))
insert into aaaaa values ('1','1',null)
insert into aaaaa values ('2','3','无')
insert into aaaaa values ('3','5','无')

select object_id('aaaaa')

declare @ColName varchar(20)
declare @s nvarchar(200)
set @s=''
DECLARE Detail_Cursor Cursor Fast_Forward For
select Name from syscolumns where id=object_id('aaaaa')
OPEN Detail_Cursor
FETCH Next From Detail_Cursor Into @ColName
WHILE @@fetch_Status=0
BEGIN
 exec('select top 1 * from aaaaa where '+@ColName+' is not null and '+@ColName+' <>''无''')
 if @@RowCount>0
  set @s=@s+','+@ColName
 FETCH Next From Detail_Cursor Into @ColName
END
CLOSE Detail_Cursor
DEALLOCATE Detail_Cursor
print @s

set @s='select '+stuff(@s,1,1,'') +' from aaaaa'
exec (@s)

select IDENTITY(int,1,1) as tid , * into #t1 from employee
select * from #t1 where tid>=5 and tid<=25
drop table #t1

CREATE RULE time_rule AS
@time like 'yyyy-mm-dd' or len(@time)=0