首页 > 解决方案 > 使用 CASE 和 Union [TSQL] 选择

问题描述

我有一张付款表。我想根据收据编号选择几列。然后我想根据几个值显示一个条件状态 col。

我写了一个 SQL 语句,它可以很好地做到这一点:

代码

SELECT 
    [Number], [Name], [Amount],
    CASE 
       WHEN [Downloaded by] IS NULL 
            AND [Request Accepted?] IS NULL 
          THEN 'Sent to Payments'
       WHEN [Downloaded by] IS NOT NULL 
            AND [Request Accepted?] IS NULL
          THEN 'Received by Payments'
       WHEN [Downloaded by] IS NOT NULL
            AND [Request Accepted?] IS NOT NULL 
           THEN 'Sent to Finance'
    END AS 'Real-time Status' 
FROM
    [DB] 
WHERE
    [Request Receipt Id] = 'PR0001' 

我正在构建一个表单,用户可以在其中滚动浏览这些值,并且我想显示数据库中的另一列。但是,我不想在主搜索框中显示此列。因此,我将搜索框设置为仅包含 4x 列(数字、名称、金额和实时状态)。

这个想法是,如果我有第 5 列,它不会显示在主用户窗体中,除非他们单击它并且它将出现在另一个框中。

我的问题是设置这个 SQL 语句来组织我想要的数据:

[Number], [Name], [Amount], [Real-time Status], [Description]

我曾经这样做的代码是:

SELECT 
    [Number], [Name], [Amount],
    CASE 
       WHEN [Downloaded by] IS NULL 
            AND [Request Accepted?] IS NULL 
          THEN 'Sent to Payments'
       WHEN [Downloaded by] IS NOT NULL 
            AND [Request Accepted?] IS NULL
          THEN 'Received by Payments'
       WHEN [Downloaded by] IS NOT NULL
            AND [Request Accepted?] IS NOT NULL 
           THEN 'Sent to Finance'
    END AS 'Real-time Status' 
FROM
    TEP_Payments_Table 
WHERE 
    [Request Receipt Id] = 'PR0001' 

UNION 

SELECT 
    [Description] 
WHERE 
    [Request Receipt Id] = 'PR0001'

但是,当我尝试这样做时,SQL Server 不喜欢它并抛出错误。

你们能告诉我哪里出错了,什么是最有效的方法来获得我想要的东西。

谢谢

标签: sqlsql-servertsql

解决方案


如果所有数据都来自单个表,您只需将描述字段添加到查询中。

        SELECT [Number],[Name],[Amount],
        CASE WHEN [Downloaded by] is null and [Request Accepted?] is null then 'Sent to Payments'
        WHEN [Downloaded by] is not null and [Request Accepted?] is null then 'Received by Payments'
        When [Downloaded by] is not null and [Request Accepted?] is not null then 'Sent to Finance'
        END as 'Real-time Status',
        Description
     from TEP_Payments_Table where [Request Receipt Id] = 'PR0001' 

推荐阅读