带有案例陈述的 TSQL 选择子句

Question

我有一个基本的选择语句，它为我提供了存储在数据库中的类型列表：

SELECT teType
FROM   BS_TrainingEvent_Types
WHERE source = @source
FOR    XML PATH ('options'), TYPE, ELEMENTS, ROOT ('types')

我的表包含一个类型列和一个源列。

该表中有一条记录，我需要将其包含在两个单独的源中，但我无法为其创建单独的记录。

**Table  Data**
type   |  source   
test      users
test2     members
test3     admins

我需要一份案例陈述才能说IF source = admins also give me the type test2.

这是否有意义，是否可以使用基本选择？

更新我想出了这个临时解决方案，但我仍然认为有更好的方法来解决这个问题。

DECLARE @tmp AS TABLE (
QID VARCHAR (10));
INSERT INTO @tmp (QID)
SELECT DISTINCT qid
FROM   tfs_adhocpermissions;
SELECT t.QID,
       emp.FirstName,
       emp.LastName,
       emp.NTID,
       (SELECT accessKey
        FROM   TFS_AdhocPermissions AS p
        WHERE  p.QID = t.QID
        FOR    XML PATH ('key'), TYPE, ELEMENTS, ROOT ('keys'))
FROM   @tmp AS t
       LEFT OUTER JOIN
       dbo.EmployeeTable AS emp
       ON t.QID = emp.QID
FOR    XML PATH ('data'), TYPE, ELEMENTS, ROOT ('root');

Answer 1

试试这个

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--create temp table for testing
IF OBJECT_ID('Tempdb..#BS_TrainingEvent_Types') IS NOT NULL 
    DROP TABLE #BS_TrainingEvent_Types
SELECT  [type] ,
        [source]
INTO    #BS_TrainingEvent_Types
FROM    ( VALUES ( 'test', 'users'), ( 'test2', 'members'),
        ( 'test3', 'admins') ) t ( [type], [source] ) 
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--final query
DECLARE @Source VARCHAR(10) = 'users'
IF @Source = 'admins' 
    BEGIN
        SELECT  [Type]
        FROM    #BS_TrainingEvent_Types
        WHERE   source = @source
                OR [type] = 'test2'
        FOR     XML PATH('options') ,
                    TYPE ,
                    ELEMENTS ,
                    ROOT('types')
    END
ELSE 
    BEGIN
        SELECT  [Type]
        FROM    #BS_TrainingEvent_Types
        WHERE   source = @source
        FOR     XML PATH('options') ,
                    TYPE ,
                    ELEMENTS ,
                    ROOT('types')
    END

Answer 2

select sq.teType
from (
    SELECT t.teType
    FROM   BS_TrainingEvent_Types t
    WHERE t.source = @source
    union all
    SELECT t.teType
    FROM   BS_TrainingEvent_Types t
    WHERE @source = 'admins' and t.source = 'members'
) sq
FOR XML PATH ('options'), TYPE, ELEMENTS, ROOT ('types');

尽管通常最好引入一个额外的表来存储这些关系，以便整个想法更具可扩展性。