SQL语法使用：笔记(一)

本文只是将工作中学到的遇到的作为笔记记录下,有更好方法或有错误之处还请各位看官尽情点拨^_^

例一 (PVIOT以及SELECT中使用判断语句写法):

将如下表格列转行以及IsValid栏位转换为1=管制  0=不管制

思路：①使用PVIOT进行列传行

            ②使用CASE WHEN THEN  进行Valid栏位判断

            ③可事先将以上数据查询出来后塞入临时表,作为数据源,便于后续需求变更二次开发(我这里将数据塞入#PVIOT临时表)

语法：

select PdLine,
CASE WHEN [S5]='1' THEN N'管制'
	 WHEN [S5]='0' THEN N'不管制'
         ELSE N'不管制' END  AS [WS IN],
CASE WHEN [S9]='1' THEN N'管制'
	 WHEN [S9]='0' THEN N'不管制'
	 ELSE N'不管制' END  AS PF,
CASE WHEN [S8]='1' THEN N'管制'
	 WHEN [S8]='0' THEN N'不管制'
	 ELSE N'不管制' END  AS FA  from #PVIOT as P pivot (max(IsValid) for P.WC in([S5],[S9],[S8])) AS T ORDER BY PdLine

结果：

例二(将SQL语句作为字符串拼接执行)

最近公司突增大量查询页面程式,一个页面进行查询操作时,User只是单纯选择，点击查询就可以show出结果数据导出Excel即可(C#导出数据至Excel)，但我们需求进行多个判断,很可能一个页面出现上百种判断,这个对于我们来说很是头疼,如下草图(需求给出Query By有十种选择,Comp Type有六种选择,可上传Excel或手动输入,时间可输入,不同选择Show出不同栏位,但其实均通过固定几个表输出结果,程式本身不复杂,但面对过多判断,难免出现BUG)

思路：①整理所有情况所使用到的表格

            ②将有相同表格,需要Show出的相同栏位整理为一个语句

            ③将差别分别赋值给声明的SQL变量,出现判断时,调用即可

语法：

--不同情況Show出不同欄位
SET @StringNew1 = 'SELECT DISTINCT A.Sno,A.Family,A.Model AS IEC_PN,C.Pn AS Cust_PN,A.Po AS IEC_PO,C.Sono AS Cust_PO , C.CustomerSO AS Cust_SO
						FROM '
									
SET @StringNewShipping = 'SELECT DISTINCT A.Sno,A.Family,A.Model AS IEC_PN,C.Pn AS Cust_PN,A.Po AS IEC_PO,C.Sono AS Cust_PO, C.CustomerSO AS Cust_SO,E.ServerRoom,C.Dn,
								A.Ctno AS CN,A.Udt AS Ship_Date,C.Country + '' '' + C.City + '' '' + C.Adr2 + '' ''+ C.Adr1   AS Ship_Adress 
								FROM '
									
SET @StringNewCompType = 'SELECT DISTINCT A.Sno,A.Family,A.Model AS IEC_PN,C.Pn AS Cust_PN,A.Po AS IEC_PO,C.Sono AS Cust_PO, C.CustomerSO AS Cust_SO,D.ScanPn AS Comp_PN,D.ScanSn AS Comp_SN 
								FROM '
SET @StringNewAll	   = 'SELECT DISTINCT A.Sno,A.Family,A.Model AS IEC_PN,C.Pn AS Cust_PN,A.Po AS IEC_PO,C.Sono AS Cust_PO, C.CustomerSO AS Cust_SO,E.ServerRoom,C.Dn,
								A.Ctno AS CN,A.Udt AS Ship_Date,C.Country + '' '' + C.City + '' '' + C.Adr2 + '' '' + C.Adr1   AS Ship_Adress,D.ScanPn AS Comp_PN,D.ScanSn AS Comp_SN 
								FROM '
--公用表格
SET @MDS_StringNew1 = 'INNER JOIN 
	MDS..PARTS B ON  A.IsPass = 1
	INNER JOIN 
	MDS..DN C ON A.Dn=C.Dn 
	INNER JOIN 
	MDS..ASSETTAG_INFO E ON A.Dn=E.Dn AND A.Udt >=  '''+@Start+''' AND A.Udt<= '''+@End+'''
	INNER JOIN'
--拼接SQL字符串
SET @MDS_StringHis1 = @StringNew  +' MDS..SNO A ' + @MDS_StringNew1 +  ' MDS..SNO_PARTS D  ON A.Sno=D.Sno AND D.ScanPn=B.Pn  AND  D.Status=1    WHERE  A.Sno ' + @MDS_STRING
--執行SQL字符串,必须（）
EXEC(@SQL)