SQL笔记 [长期更新] (

SQL笔记 [长期更新] (-2013.7) –tableA — CREATE TABLE TABLEA( –Column1 VARCHAR (20), –Column2 VARCHAR (20), –Column3 VARCHAR (20), –Column4 int , –Column5 VARCHAR (20), –Column6 DATETIME –) /* –ROW_NUMBER() OVER()， case when 的用

SQL笔记 [长期更新] (-2013.7)

–tableA

–CREATE TABLE TABLEA(
–Column1 VARCHAR(20),
–Column2 VARCHAR(20),
–Column3 VARCHAR(20),
–Column4 int,
–Column5 VARCHAR(20),
–Column6 DATETIME
–)
/*
–ROW_NUMBER() OVER()， case when 的用法：
select ROW_NUMBER() OVER(Order by a.Column1,a.Column2) AS rowid
,CASE WHEN a.Column3 IS NOT NULL THEN(
CASE WHEN a.Column3=’1′ AND a.Column4 IS NOT NULL AND a.Column4 > 0 THEN ‘I’
WHEN a.Column3=’2′ AND a.Column4 IS NOT NULL AND a.Column4 > 0 THEN ‘II’
WHEN a.Column3=’3′ AND a.Column4 IS NOT NULL AND a.Column4 > 0 THEN ‘III’
WHEN a.Column3=’4′ AND a.Column4 IS NOT NULL AND a.Column4 > 0 THEN ‘IV’
WHEN a.Column3=’5′ AND a.Column4 IS NOT NULL AND a.Column4 > 0 THEN ‘V’
WHEN a.Column3=’6′ AND a.Column4 IS NOT NULL AND a.Column4 > 0 THEN ‘VI’
WHEN a.Column3=’7′ AND a.Column4 IS NOT NULL AND a.Column4 > 0 THEN ‘VII’
WHEN a.Column3=’8′ AND a.Column4 IS NOT NULL AND a.Column4 > 0 THEN ‘VIII’
WHEN a.Column3=’9′ AND a.Column4 IS NOT NULL AND a.Column4 > 0 THEN ‘IX’
ELSE ” END — +'(‘+CONVERT(VARCHAR(20),a.Column4)+’)’
)ELSE ” END AS 类型编号
,
from TABLEA a
*/

/*
–本月第一天
SELECT CAST(CONVERT(VARCHAR(20), DATEADD(MONTH,-1, DATEADD(DAY,1-DATEPART(DAY,GETDATE()),DATEADD(MONTH, 1,GETDATE()))) ,112) AS DATETIME)
–本月最后一天
SELECT CAST(CONVERT(VARCHAR(20),DATEADD(DAY,-DATEPART(DAY,GETDATE()),DATEADD(MONTH, 1,GETDATE())) ,112) AS DATETIME)
–下个月第一天
SELECT CAST(CONVERT(VARCHAR(20), DATEADD(DAY,1-DATEPART(DAY,GETDATE()),DATEADD(MONTH, 1,GETDATE())) ,112) AS DATETIME)
*/

/* –复杂存储过程的使用示例：
create proc [SP_复杂存储过程的使用B] (
@ID1 VARCHAR(400)=”
,@ID2 VARCHAR (400)=’sdf7fgef-f4b0-4sfe-8ecb-cbgefb0fcfgh’ –”
,@RESULT VARCHAR(100) output
)
as

BEGIN
DECLARE @行号 INT =1
DECLARE @RESULT_temp VARCHAR(100)=”
SET @RESULT=”

WHILE (@行号
BEGIN
SET @RESULT_temp=”
EXEC SP_复杂存储过程的使用A @编号1=@ID1,@编号2=@ID2,@第几个类型=@行号,@RESULT=@RESULT_temp OUTPUT
IF @RESULT_temp IS NOT NULL AND @RESULT_temp ”
BEGIN
IF @RESULT=” –@行号=1
BEGIN
SET @RESULT=@RESULT_temp
END
ELSE
BEGIN
SET @RESULT+=’,’+@RESULT_temp
END
END
SET @行号+=1
END
END
*/

/* –以下是一个比较长的SQL语句，直到末尾结束(其中涉及到一些不常见的sql写法,值得收藏)
DECLARE @ID号 VARCHAR(50)=’JXsfge’ –放入报表文件的时候，香港虚拟主机，此行注释，服务器空间，即将@ID号当参数即可

DECLARE @i VARCHAR(400) =1
DECLARE @参数类型1 VARCHAR(20)=’某某1′
DECLARE @参数类型2 VARCHAR(20)=’某某2′
DECLARE @参数类型3 VARCHAR(20)=’某某3′
DECLARE @参数类型11 VARCHAR(20)=’某某11′
DECLARE @参数类型22 VARCHAR(20)=’某某22′
DECLARE @参数类型33 VARCHAR(20)=’某某33′
DECLARE @编号C VARCHAR(20)=”
DECLARE @CNAME VARCHAR(20)=@ID号
DECLARE @专业temp VARCHAR(400)=”
DECLARE @序号 INT =”

DECLARE @ROWS VARCHAR(400) =”
EXEC SP_复杂存储过程的使用C @某编号=@ID号,@ROWS1=@ROWS OUTPUT –可以得到输出的”行数”信息

–声明临时表@t_TableA、@t_TableX
declare @t_TableA table(j VARCHAR(10),标记 VARCHAR(50),专业 VARCHAR(50),序号 VARCHAR(10),某号A VARCHAR(50),CISLATEST VARCHAR(50))
declare @t_TableX table(j VARCHAR(10),标记 VARCHAR(50),专业 VARCHAR(50),序号 VARCHAR(10),某号A VARCHAR(50),某号名称 VARCHAR(50),
备注 VARCHAR(50),英文名称 VARCHAR(500))
–开始循环
WHILE (CONVERT(int,@i))=(CONVERT(int,@ROWS))–@i=@ROWS
BEGIN
PRINT @i

INSERT INTO @t_TableA (j,标记,专业,序号,某号A,CISLATEST)(
SELECT TOP 1 * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY TABLEB.专业 DESC,TABLEB.某号A ASC) AS j ,
CASE WHEN TABLEB.专业 =’计算机’ THEN ‘COM’
WHEN TABLEB.专业 =’英语’ THEN ‘EN’
WHEN TABLEB.专业 =’电子商务’ THEN ‘EM’
WHEN TABLEB.专业 =’国际贸易’ THEN ‘ITRADE’
WHEN TABLEB.专业 =’统计分析’ THEN ‘M’
WHEN TABLEB.专业 =’城市建设’ THEN ‘C’
WHEN TABLEB.专业 =’系统维护’ THEN ‘SYS’
ELSE ” END AS 专业,
‘1’ AS 序号,TABLEB.IDA AS 某号A,TABLEB.CISLATEST
FROM TABLEB
WHERE TABLEB.某号A = @ID号 AND TABLEB.CISLATEST=’1′
) AS m1 WHERE m1.j=(CONVERT(int,@i))
)

–调用方式,@时间 为返回值
SET @某号A=(SELECT TOP 1 某号A FROM @t_TableA WHERE j=(CONVERT(int,@i)))
PRINT @某号A

IF(@专业temp =(SELECT TOP 1 专业 FROM @t_TableA WHERE j=(CONVERT(int,@i))))
BEGIN
SET @序号+=1
END
ELSE
BEGIN
SET @序号=1
SET @专业temp=(SELECT TOP 1 专业 FROM @t_TableA WHERE j=(CONVERT(int,@i)))
END

SET @参数类型1=”
SET @参数类型2=”
SET @参数类型3=”
SET @参数类型11=”
SET @参数类型22=”
SET @参数类型33=”

EXEC SP_复杂存储过程的使用B @类型=@参数类型1,@CNAME=@CNAME,@时间=@参数类型11 OUTPUT,@某编号=”
EXEC SP_复杂存储过程的使用B @类型=@参数类型2,@CNAME=@CNAME,@时间=@参数类型22 OUTPUT,@某编号=”
EXEC SP_复杂存储过程的使用B @类型=@参数类型3,@CNAME=@CNAME,@时间=@参数类型33 OUTPUT,@某编号=@编号C OUTPUT –可以同时输出多个结果

DELETE FROM @t_TableA –清空临时表

PRINT ‘参数类型11 :’+@参数类型11+’ 参数类型22:’+@参数类型22+’ 参数类型33:’+@参数类型33

–直接全部显示
BEGIN
INSERT INTO @t_TableX (j,标记,专业,序号,某号A ,某号名称,备注)(
SELECT TOP 1 * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY TABLEB.专业 DESC,TABLEB.某号A ASC) AS j ,
CASE WHEN TABLEB.专业 =’计算机’ THEN ‘COM’
WHEN TABLEB.专业 =’英语’ THEN ‘EN’
WHEN TABLEB.专业 =’电子商务’ THEN ‘EM’
WHEN TABLEB.专业 =’国际贸易’ THEN ‘ITRADE’
WHEN TABLEB.专业 =’统计分析’ THEN ‘M’
WHEN TABLEB.专业 =’城市建设’ THEN ‘C’
WHEN TABLEB.专业 =’系统维护’ THEN ‘SYS’
ELSE ” END AS 专业,
‘1’ AS 序号,TABLEB.IDA AS 某号A,TABLEB.CISLATEST,
@参数类型11+@参数类型22+@参数类型33
FROM TABLEB
WHERE 序号= @ID号 AND TABLEB.CISLATEST=’1′
) AS m1 WHERE m1.j=(CONVERT(int,@i))
)
END
SET @i+=1

END

SELECT j,标记,专业,序号,某号A ,某号名称,备注 FROM @t_TableX

*/

下载地址：

posted on

，香港虚拟主机