sql - 使用 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 不喜欢它并抛出错误。
你们能告诉我哪里出错了,什么是最有效的方法来获得我想要的东西。
谢谢
解决方案
如果所有数据都来自单个表,您只需将描述字段添加到查询中。
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'
推荐阅读
- ios - 通过 carthage 复制框架的代码符号框架失败:捆绑格式无法识别、无效或不合适
- spring - AsyncUncaughtExceptionHandler 实现中的自动装配问题
- javascript - Laravel - 使用 javascript 将用户 ID 传递给模态
- vba - 将工作表导出为 CSV 会覆盖原始文件名 VBA
- vba - 分配 MailItem.HTMLBody 会使图像质量降低
- python - p4 diff2 未显示两个文件的差异
- php - 使用php将csv文件上传到sql数据库的更好方法
- angular - 将按钮添加到具有角度 2 事件的数据表的每一行
- c# - TextChanged 后 TextBox 值为空
- python - Pyspark Cassandra - 为类型为“struct”的 spark 数据框列创建正确的用户定义类型 (UDT)