数据库较不常用关键词用法参考库

SQLsever

1.递归 查询所有子项以及子项的子项(WITH AS)

功能：实现递归查询子项以及子项的子项
用途：常用于文件夹等树形结构
核心关键词：WITH … AS 、 UNION ALL
具体参考代码：

CREATE PROCEDURE BOMTreesGetGold ( @BomFnumber VARCHAR ( 100 ) ) AS DECLARE
@BomFnumberT VARCHAR ( 100 );

SET @BomFnumberT = @BomFnumber;
WITH sll AS (
	SELECT
		A.FID AS ID,
		A.FNUMBER AS mainFnumberArgument,
		B.FENTRYID AS childID,
		C.FNUMBER+ '*' + G.FNAME AS theTitle,
		ISNULL( H.FNUMBER , zz.FNUMBER ) AS childBomNumber,
/**/
		zz.FNUMBER AS latestBomFumber,
		G.FNAME AS name 
	FROM
		T_ENG_BOM AS A
		LEFT JOIN [T_ENG_BOMCHILD] AS B ON a.FID = b.FID
		LEFT JOIN [T_BD_MATERIAL] AS C ON C.FMATERIALID = B.FMATERIALID
		LEFT JOIN T_ENG_BOM_L AS D ON A.FID = D.FID -- 		LEFT JOIN [T_BD_MATERIAL] AS E ON A.FMATERIALID = E.FMATERIALID
		LEFT JOIN [T_BD_MATERIAL_L] AS G ON B.FMATERIALID = G.FMATERIALID
		LEFT JOIN [dbo].[T_ENG_BOM] AS H ON b.FBOMID = H.FID
		LEFT JOIN ( SELECT FID, FNUMBER, FMATERIALID FROM T_ENG_BOM WHERE FID IN ( SELECT MAX ( FID ) id FROM T_ENG_BOM GROUP BY FMATERIALID ) ) AS ZZ ON ZZ.FMATERIALID = B.FMATERIALID 
	),
	SSSS AS ( SELECT * FROM sll AS A WHERE A.MainFnumberArgument IN ( @BomFnumber ) UNION ALL SELECT LL.* FROM sll AS LL JOIN SSSS AS LL1 ON LL.MainFnumberArgument = LL1.childBomNumber -- 	WHERE  MainFnumberArgument in (@BomFnumber)
	) SELECT DISTINCT 
	childID AS MM,
	1 AS FFloorNum,* 
FROM
	SSSS 
ORDER BY
	childID

核心代码：

DECLARE  @KeyNameArg VARCHAR(50);
SET @KeyNameArg = 'Key1';

WITH CodeBlockName 
AS 
(
SELECT * FROM TableName WHERE KeyName  = @KeyNameArg
UNION ALL 
SELECT ShortNameOfTableName.* FROM TableName AS ShortNameOfTableName JOIN CodeBlockName  AS ShortNameOfCodeBlock ON ShortNameOfTableName.ParentID = ShortNameOfCodeBlock.ChildID
)

SELECT * FROM CodeBlockName  

表TableName:

执行结果：

关键词用法：

2.列值旋转为列名(PIVOT)

功能：将列值旋转为列名
用途：常用于统计某字段出现的次数

具体参考代码：

SELECT
	反审核,保存,登入系统 
FROM
	( 
	SELECT COUNT ( [FOPERATENAME] ) AS [FOPERATENUMS],
	[FOPERATENAME] FROM  [dbo].[T_BAS_WEBAPILOG] WHERE [FRUNRESULT] = 'Success' 
	GROUP BY [FOPERATENAME] ) AS A PIVOT ( SUM ( A.[FOPERATENUMS] ) FOR [FOPERATENAME] IN ( 反审核, 保存, 登入系统 ) ) AS B

T_BAS_WEBAPILOG表（部分）：

执行结果：

具体代码相当简单，核心代码可以直接参考具体代码。