表值函数查询语句IF @IsUser IS NULL
BEGIN
INSERT @Table SELECT
T.PK_ID AS FK_TaskID,
FK_SystemUserID=NULL,
BDComplete=SUM(ISNULL(UTD.BDComplete,0)),
PicNumber=CAST(COUNT(UTD.PK_ID)AS NVARCHAR(10))+'/'+CAST(TotalPIC.TotalNumber AS nvarchar(10)),
FK_LableID=NULL
FROM dbo.TaskFile TF
LEFT JOIN dbo.Task AS T ON T.PK_ID = TF.FK_TaskID
LEFT JOIN dbo.UserTaskData AS UTD ON UTD.FK_TaskFileID = TF.PK_ID
LEFT JOIN (SELECT FK_TaskID, COUNT(FK_TaskID)AS TotalNumber FROM dbo.TaskFile
GROUP BY FK_TaskID) AS TotalPIC ON T.PK_ID=TotalPIC.FK_TaskID
WHERE TF.IsDelete=0 AND UTD.FileState=3 AND t.IsDelete=0
GROUP BY t.PK_ID,T.FK_LabelID,TotalPIC.TotalNumber
END
RETURN
END
直接执行SQL语句要9秒。是写法有问题吗?
SELECT * FROM dbo.GetTable(NULL)这样写竟然要40秒才有结果
BEGIN
INSERT @Table SELECT
T.PK_ID AS FK_TaskID,
FK_SystemUserID=NULL,
BDComplete=SUM(ISNULL(UTD.BDComplete,0)),
PicNumber=CAST(COUNT(UTD.PK_ID)AS NVARCHAR(10))+'/'+CAST(TotalPIC.TotalNumber AS nvarchar(10)),
FK_LableID=NULL
FROM dbo.TaskFile TF
LEFT JOIN dbo.Task AS T ON T.PK_ID = TF.FK_TaskID
LEFT JOIN dbo.UserTaskData AS UTD ON UTD.FK_TaskFileID = TF.PK_ID
LEFT JOIN (SELECT FK_TaskID, COUNT(FK_TaskID)AS TotalNumber FROM dbo.TaskFile
GROUP BY FK_TaskID) AS TotalPIC ON T.PK_ID=TotalPIC.FK_TaskID
WHERE TF.IsDelete=0 AND UTD.FileState=3 AND t.IsDelete=0
GROUP BY t.PK_ID,T.FK_LabelID,TotalPIC.TotalNumber
END
RETURN
END
直接执行SQL语句要9秒。是写法有问题吗?
SELECT * FROM dbo.GetTable(NULL)这样写竟然要40秒才有结果
-- 函数名称按你原来的
ALTER FUNCTION dbo.Fun_XXX
(
@IsUser BIT
)
RETURNS TABLE
AS
RETURN
(
WITH notNullCte AS(
SELECT 1 AS FK_TaskID
,CAST(1 AS INT) AS FK_SystemUserID
,CAST(1 AS INT) AS BDComplete
,CAST('0' AS NVARCHAR(10)) AS PicNumber
,CAST(1 AS INT) AS FK_LableID
)
SELECT T.PK_ID AS FK_TaskID,
FK_SystemUserID = NULL,
BDComplete = SUM(ISNULL(UTD.BDComplete, 0)),
PicNumber = CAST(COUNT(UTD.PK_ID)AS NVARCHAR(10)) + '/' + CAST(TotalPIC.TotalNumber AS NVARCHAR(10)),
FK_LableID = NULL
FROM dbo.TaskFile TF
LEFT JOIN dbo.Task AS T
ON T.PK_ID = TF.FK_TaskID
LEFT JOIN dbo.UserTaskData AS UTD
ON UTD.FK_TaskFileID = TF.PK_ID
LEFT JOIN (
SELECT FK_TaskID,
COUNT(FK_TaskID) AS TotalNumber
FROM dbo.TaskFile
GROUP BY
FK_TaskID
) AS TotalPIC
ON T.PK_ID = TotalPIC.FK_TaskID
WHERE
@IsUser IS NULL
AND TF.IsDelete = 0
AND UTD.FileState = 3
AND t.IsDelete = 0
GROUP BY
t.PK_ID,
T.FK_LabelID,
TotalPIC.TotalNumber
UNION ALL
SELECT * FROM (
SELECT *
FROM notNullCte
WHERE 1=0
) AS t
WHERE @IsUser IS NOT NULL
)
GO
因为你贴出来的不全, 所以有些地方可能需要你去修正。