USE WSS_Content SELECT * FROM dbo.utTable32 WHERE DB0461A IN (SELECT DB0461A FROM dbo.utTable32 WHERE ID IN('1','2','2'))---Row_NUMBER() OVER()SELECT ROW_NUMBER()OVER(ORDER BY ID)AS RowNum,* FROM dbo.utTable32 ---按照id升序排序，并编号。SELECT ROW_NUMBER()OVER(ORDER BY DB0461A)AS RowNum,* FROM dbo.utTable32 --按照车牌号升序排序并编号。SELECT ROW_NUMBER()OVER(PARTITION by DB0461A ORDER BY DB0461A)AS RowNum,* FROM dbo.utTable32 --按照车牌号升序排序并编号。--SELECT ROW_NUMBER()OVER(ORDER BY ID RANGE BETWEEN 2 preceding AND 2 preceding)AS RowNum,* FROM dbo.utTable32--SELECT DB0461A,DB0923A,SUM(DB0923A)OVER(order by id) AS SS FROM dbo.utTable32SELECT * FROM dbo.utTable32 WHERE DB0461A LIKE '豫ca%2'SELECT COUNT(*),DB0461A  FROM dbo.utTable32 WHERE ID>90 GROUP BY DB0461ASELECT COUNT(*),DB0461A  FROM dbo.utTable32 GROUP BY DB0461A HAVING ID>90SELECT * FROM dbo.utTable23 T23,dbo .utTable24 T24 WHERE T23 .DB0835A =T24 .DB0835A ;SELECT CAST(DB0835A as nvarchar(30))+CAST(DB0836A AS NVARCHAR(30)) FROM dbo.utTable23 SELECT SUBSTRING(DB0835A,8,LEN(DB0835A)-7) FROM dbo.utTable23 --======================游标==========================================================--定义游标DECLARE UT32_CURSOR CURSOR FOR SELECT ID,DB0002A FROM DBO.UTTABLE32--打开游标OPEN UT32_CURSOR--定义变量DECLARE @ID INTDECLARE @NAME NVARCHAR(40)DECLARE @CLASSid intSELECT @CLASSid =1--循环移动游标FETCH NEXT FROM UT32_CURSOR INTO @ID,@NAME WHILE(@@FETCH_STATUS=0)BEGINIF @CLASSid =12BEGIN SELECT @CLASSid =1ENDSELECT @CLASSid=@CLASSid +1INSERT INTO dbo.StuTable(ID,StuName,ClassId)VALUES(@ID,@NAME,@CLASSid)FETCH NEXT FROM UT32_CURSOR INTO @ID,@NAME END--关闭游标CLOSE UT32_CURSORDEALLOCATE UT32_CURSOR--------------------------------随机查询SELECT * FROM dbo.utTable32 ORDER BY NEWID()select cast( floor(rand(20)*100) as int) SELECT RAND(100)*100,RAND(),RAND()------------------------------DECLARE StuTable_CURSOR CURSOR FOR SELECT ID FROM StuTableDECLARE @Age INTDECLARE @UpId INTSELECT @Age =0OPEN StuTable_CURSORFETCH NEXT FROM StuTable_CURSOR INTO @UpIdWHILE(@@FETCH_STATUS =0)BEGIN    SELECT @Age=CAST(FLOOR(RAND()*50) AS INT)    WHILE(@Age <20)    BEGIN      SELECT @Age=CAST(FLOOR(RAND()*50) AS INT)    END    UPDATE StuTable SET StuAge=CAST(@Age AS NVARCHAR(20)) WHERE ID =@UpId    FETCH NEXT FROM StuTable_CURSOR INTO @UpIdENDCLOSE StuTable_CURSORDEALLOCATE StuTable_CURSOR-------------------------------CREATE TABLE #ObjList(ObjName nvarchar(20) not null)INSERT INTO #ObjList VALUES ('语文')INSERT INTO #ObjList VALUES ('数学')INSERT INTO #ObjList VALUES ('英语')INSERT INTO #ObjList VALUES ('化学')INSERT INTO #ObjList VALUES ('物理')INSERT INTO #ObjList VALUES ('生物')DECLARE STUTB_CURSOR CURSOR FOR SELECT ID FROM StuTableDECLARE Obj_CURSOR CURSOR SCROLL FORSELECT ObjName FROM #ObjListDECLARE @StuId intDECLARE @ObjName nvarchar(20)DECLARE @SID intDECLARE @Score int SELECT @SID =0SELECT @Score =1OPEN STUTB_CURSOROPEN Obj_CURSORFETCH NEXT FROM STUTB_CURSOR INTO @StuIdWHILE(@@FETCH_STATUS =0)BEGIN  FETCH NEXT FROM Obj_CURSOR INTO @ObjName  IF(@@FETCH_STATUS =-1)  BEGIN    FETCH FIRST FROM Obj_CURSOR INTO @ObjName   --FETCH NEXT FROM Obj_CURSOR INTO @ObjName  END  WHILE(@@FETCH_STATUS =0)  BEGIN    SELECT @Score =CAST(FLOOR(RAND()*100) AS INT)+1    SELECT @SID=@SID+1    INSERT INTO ScoreTable (ID,ObjectName,Score,StuId)          VALUES (@SID,@ObjName,@Score,@StuId)    FETCH NEXT FROM Obj_CURSOR INTO @ObjName  END  FETCH NEXT FROM STUTB_CURSOR INTO @StuIdENDCLOSE STUTB_CURSORCLOSE Obj_CURSORDEALLOCATE STUTB_CURSORDEALLOCATE Obj_CURSOR----------------------------------DELETE FROM ScoreTable WHERE 1=1-------------------------------------统计每个班级的人数SELECT C.ClassName,COUNT(*) AS COUT FROM ClassTable C LEFT JOIN StuTable S ON C.ID =S.ClassId GROUP BY C.ClassNameSELECT COUNT(*) AS COUT FROM ClassTable C LEFT JOIN StuTable S ON C.ID =S.ClassId WHERE C.ClassName ='2.2班'----------------------------------DECLARE UP_CURSOR CURSOR FORSELECT C.ClassName,COUNT(*) AS COUT FROM ClassTable C LEFT JOIN StuTable S ON C.ID =S.ClassId GROUP BY C.ClassNameDECLARE @Class nvarchar(20)DECLARE @Count INTOPEN UP_CURSORFETCH NEXT FROM UP_CURSOR INTO @Class,@CountWHILE(@@FETCH_STATUS =0)BEGIN UPDATE dbo.ClassTable SET StuCount =@Count WHERE ClassName =@Class  FETCH NEXT FROM UP_CURSOR INTO @Class,@CountENDCLOSE UP_CURSORDEALLOCATE UP_CURSOR-------------------------------------统计每个学生各科目成绩和总分CREATE TABLE #STUSCORE(  ID INT,  HX INT,  SW INT,  SX INT,  WL INT,  YY INT,  YW INT)ALTER TABLE #STUSCORE ADD ZF INTUSE WSS_Content DECLARE STC_CURSOR CURSOR FORSELECT ID FROM WSS_Content.dbo.StuTable --FOR --read only --WHERE ID BETWEEN 1 AND 200DECLARE @StudId intOPEN STC_CURSORFETCH NEXT FROM STC_CURSOR INTO @StudIdWHILE(@@FETCH_STATUS =0)BEGIN  DECLARE SOC_CURSOR CURSOR SCROLL FOR  SELECT ObjectName,Score,StuId FROM WSS_Content.dbo.ScoreTable WHERE StuId=@StudId ORDER BY ObjectName  DECLARE @HX INT  DECLARE @SW INT  DECLARE @SX INT  DECLARE @WL INT  DECLARE @YY INT  DECLARE @YW INT  DECLARE @KSFS INT  DECLARE @CODE INT  DECLARE @KMLX NVARCHAR(20)  DECLARE @IDD INT  DECLARE @ZF INT  OPEN SOC_CURSOR  FETCH NEXT FROM SOC_CURSOR INTO @KMLX,@KSFS,@IDD  IF @@FETCH_STATUS =-1  BEGIN     FETCH FIRST FROM SOC_CURSOR INTO @KMLX,@KSFS,@IDD  END  WHILE(@@FETCH_STATUS =0)  BEGIN     IF(@KMLX='语文')BEGIN SELECT @YW=@KSFS END     IF(@KMLX='数学')BEGIN SELECT @SX=@KSFS END     IF(@KMLX='英语')BEGIN SELECT @YY=@KSFS END     IF(@KMLX='物理')BEGIN SELECT @WL=@KSFS END     IF(@KMLX='化学')BEGIN SELECT @HX=@KSFS END     IF(@KMLX='生物')BEGIN SELECT @SW=@KSFS END     FETCH NEXT FROM SOC_CURSOR INTO @KMLX,@KSFS,@IDD  END  SELECT @ZF =0  SELECT @ZF=@YW+@SX+@YY+@WL+@HX+@SW  INSERT INTO #STUSCORE VALUES (@IDD,@HX,@SW,@SX,@WL,@YY,@YW,@ZF)  CLOSE SOC_CURSOR  DEALLOCATE SOC_CURSOR  FETCH NEXT FROM STC_CURSOR INTO @StudIdENDCLOSE STC_CURSORDEALLOCATE STC_CURSOR-----------------------------------DELETE FROM #STUSCORE WHERE 1=1SELECT * FROM #STUSCORE SELECT A.ID,B.StuName AS 姓名 ,A.YW AS 语文,A.SX AS 数学,A.YY AS 英语,A.WL AS 物理,A.HX AS 化学,A.SW AS 生物,A.ZF AS 总分 FROM #STUSCORE A JOIN StuTable B ON A.ID =B.ID ORDER BY A.ZF DESCSELECT * FROM ScoreTable -----------------------------------DECLARE STUCUR Insensitive SCROLL CURSOR FOR SELECT ID FROM StuTableDECLARE @AID INTOPEN STUCURFETCH NEXT FROM STUCUR INTO @AIDDELETE FROM StuTable WHERE ID=@AIDFETCH FIRST FROM STUCUR INTO @AIDPRINT @AIDCLOSE STUCURDEALLOCATE STUCURSELECT * FROM StuTable -----------------------------------DECLARE SSTUCUR CURSOR SCROLLFOR SELECT StuName FROM StuTableDECLARE @StuNam NVARCHAR(10)OPEN SSTUCURFETCH ABSOLUTE 1 3 FROM SSTUCUR INTO @StuNamPRINT @StuNamCLOSE SSTUCURDEALLOCATE SSTUCURSELECT * FROM StuTable ------------------------------------DECLARE SSTUCUR1 CURSOR FOR SELECT StuName FROM StuTableFOR UPDATE OF StuNameOPEN SSTUCUR1FETCH NEXT FROM SSTUCUR1UPDATE StuTable SET StuName='褚强强' WHERE CURRENT OF SSTUCUR1 CLOSE SSTUCUR1DEALLOCATE SSTUCUR1DECLARE @PRI nvarchar(10)FETCH NEXT FROM SSTUCUR1 INTO @PRI--===========================================================DECLARE @STUDENT NVARCHAR(20)SELECT TOP 200 @STUDENT=StuName FROM StuTable --WHEREPRINT @STUDENT--=========================全局变量=================================SELECT @@IDENTITYSELECT * INTO TABLE1 FROM StuTable select @@datefirst as '星期的第一天', datepart(dw, getDate()) AS '今天是星期';select @@datefirst as '星期的第一天', datepart(MM, getDate()) AS '月份';select @@dbts;--返回当前数据库唯一时间戳--===========================逻辑控制语句=============================IF 0>0  SELECT '90'ELSE IF 1=2  SELECT '120'ELSE  SELECT '110'----------------------------------PRINT '等三秒。。。。'waitfor delay '00:00:10';--定时三秒后执行SELECT * FROM StuTable ----------------------------------declare @i int;    set @i = 1;while (@i < 11)    begin                          if (@i < 5)              begin                    set @i = @i + 1;         continue;                     end        print @i;         set @i = @i + 1;                   end--------------------------------------按分数划分等级A B C DSELECT *,     '等级'=CASE               WHEN SCORE >= 90 THEN 'A'              WHEN 75
     
      30SELECT * FROM StuTable WHERE StuAge <=30SELECT * FROM StuTable WHERE 30