根据主查询的结果运行SQL子查询

Question

有没有更好的方法来做这件事？

下面的SQL Server2008 R2表值函数运行一个查询，以确定有多少记录符合特定条件，然后根据第一个查询的结果执行另一个查询。

这是正确的方法吗？或者有没有更好的方法来达到同样的结果？

USE [My_MSSQL_Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetActiveProfileIDForSystemID](@SystemID NVARCHAR(10))
RETURNS @retActiveProfileID TABLE 
(
    ActiveUserProfile NVARCHAR(10)
)
AS 
BEGIN
    DECLARE @ProfileCount NVARCHAR(50);
    DECLARE @ProfilesLookup NVARCHAR(50);

    SELECT @ProfileCount = COUNT(mdl_user.id)
    FROM mdl_user
    INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
    WHERE (mdl_user_info_data.fieldid = 2)
    AND (mdl_user_info_data.data LIKE @SystemID)
    AND mdl_user.id NOT IN (
        SELECT mdl_user.id
        FROM mdl_user
        INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
        WHERE (mdl_user_info_data.fieldid = 4)
        AND (mdl_user_info_data.data LIKE 'Yes')
        AND (mdl_user.deleted = 0)
    )
    AND (mdl_user.deleted = 0);

    IF (@ProfileCount > 0)
    BEGIN
        SELECT @ProfilesLookup = mdl_user.id
        FROM mdl_user
        INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
        WHERE (mdl_user_info_data.fieldid = 2)
        AND (mdl_user_info_data.data LIKE @SystemID)
        AND mdl_user.id NOT IN (
            SELECT mdl_user.id
            FROM mdl_user
            INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
            WHERE (mdl_user_info_data.fieldid = 4)
            AND (mdl_user_info_data.data LIKE 'Yes')
            AND (mdl_user.deleted = 0)
        )
        AND (mdl_user.deleted = 0)
    END
    ELSE
    BEGIN
        SELECT @ProfilesLookup = mdl_user.id
        FROM mdl_user
        INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
        WHERE (mdl_user_info_data.fieldid = 2)
        AND (mdl_user_info_data.data LIKE @SystemID)
        AND (mdl_user.deleted = 0)
    END

    IF @ProfilesLookup IS NOT NULL 
    BEGIN
        INSERT @retActiveProfileID
        SELECT @ProfilesLookup;
    END
    RETURN
END

mdl_user_info_data.userid是mdl_user.id上的隐含FK (mdl_user.id = mdl_user_info_data.userid)

从本质上讲，我想做的是：

运行一个查询，计算mdl_user_info_data表中有多少行/记录

WHERE (mdl_user_info_data.fieldid = 4)
AND (mdl_user_info_data.data LIKE 'Yes')

and WHERE (mdl_user_info_data.fieldid = 2) AND (mdl_user_info_data.data LIKE @SystemID)

如果没有行(COUNT = 0)，那么我想：

SELECT id 
FROM mdl_user_info_data 
WHERE mdl_user_info_data.userid = @SystemID

其中@SystemID是调用时传递给GetActiveProfileIDForSystemID( @SystemID )函数的@SystemID值。

mdl_user_info_data.fieldid =2和mdl_user_info_data.fieldid =4行是应用程序(Moodle)中的"user profile“字段。

mdl_user_info_data表的定义是：

CREATE TABLE [dbo].[mdl_user_info_data](
 [id] [bigint] IDENTITY(1,1) NOT NULL,
 [userid] [bigint] NOT NULL,
 [fieldid] [bigint] NOT NULL,
 [data] [ntext] NOT NULL,
 [dataformat] [smallint] NOT NULL,
 CONSTRAINT [mdl_userinfodata_id_pk] PRIMARY KEY CLUSTERED
)

Answer 1

这取决于你说的“更好”是什么意思。

从易于理解和维护的角度来看，当前的变体可能是可以的。

从性能的角度来看，它很可能会得到改进，因为您将同一查询执行了两次。

这是一种可能的方法。这就是我对这个函数背后逻辑的理解。

有两组可能相交的ID。

A:
SELECT ID
FROM T
WHERE <ExpressionA>

SELECT mdl_user.id
FROM mdl_user
INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
WHERE (mdl_user_info_data.fieldid = 2)
AND (mdl_user_info_data.data LIKE @SystemID)
AND (mdl_user.deleted = 0)


B:
SELECT ID
FROM T
WHERE <ExpressionB>

SELECT mdl_user.id
FROM mdl_user
INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
WHERE (mdl_user_info_data.fieldid = 4)
AND (mdl_user_info_data.data LIKE 'Yes')
AND (mdl_user.deleted = 0)

如果set A减去set B有任何行，那么我们从差值中返回一个随机行，否则从set A中返回一个随机行。

这是两个集合的外观：

A: |........................|
B:                  |,,,,,,,,,,,,,,,,,,,,,,,,|

   |................|;;;;;;;|,,,,,,,,,,,,,,,,|
         A - B        A x B       B - A

如果A-B不为空，则返回

，否则返回A或AxB (如果A-B为空，则返回A = AxB)

我们只想返回一行，而不是整个集合。因此，我们可以返回A-B和AxB的有序并集，并取结果的第一行。

WITH
CTE_A
AS
(
    SELECT ID
    FROM T
    WHERE <ExpressionA>
)
,CTE_B
AS
(
    SELECT ID
    FROM T
    WHERE <ExpressionB>
)
,CTE_OrderedA
AS
(
    -- A - B
    SELECT 1 AS SortOrder, ID
    FROM CTE_A
    WHERE ID NOT IN
    (
        SELECT ID
        FROM CTE_B
    )

    UNION ALL

    -- A x B
    SELECT 2 AS SortOrder, ID
    FROM CTE_A
    WHERE ID IN
    (
        SELECT ID
        FROM CTE_B
    )
)
SELECT TOP(1) ID
FROM CTE_OrderedA
ORDER BY SortOrder
;

此查询消除了显式IF的需要。现在，您可以简化函数本身并使其成为inline TVF。它应该比当前的变体更有效，但您应该使用真实数据来衡量实际性能。

实际上，从维护的角度来看，可以说单个查询变体更好，因为查询的代码不会重复。但这背后的逻辑可能并不明显。在任何情况下，都应该有适当的注释来解释逻辑。