时间戳

首页 > 解决方案 > 根据特定条件在列中显示日期

sql-server - 根据特定条件在列中显示日期

问题描述

运行以下代码时，我收到错误：

子查询返回超过 1 个值。=当子查询跟在, !=, <, <=,>之后>=或当子查询用作表达式时，这是不允许的

当我添加最后一点代码时（见联合后）

(select top 1 convert(varchar,PaymentDate,103) from tblUTRDetail where CCN_No=CR.CCN_NO and CR.Claim_Status='Y' order by PaymentDate desc
union all
select top 1 convert(varchar,CreatedDate,103) from tblOpenCloseTrans where S_NO=CR.S_NO and OpenCloseType='C' and CR.Claim_Status='C' order by CreatedDate desc
union all
select top 1 convert(varchar,GENERATED_DATE,103) from tbldeficiencyletterTrans where S_NO=CR.S_NO and DEFICIENT_REPUDATION_FLAG='NC' and CR.Claim_Status='NC' order by GENERATED_DATE desc
union all
select top 1 convert(varchar,GENERATED_DATE,103) from tbldeficiencyletterTrans where S_NO=CR.S_NO and DEFICIENT_REPUDATION_FLAG='R' and CR.Claim_Status='RC' order by GENERATED_DATE desc
union 
(select GENERATED_DATE from  (Select s_no, convert (varchar,(GENERATED_DATE),103)as GENERATED_DATE ,row_number()
over(partition by s_no order by s_no asc )as rw from tblDeficiencyLetterTrans b
where DEFICIENT_REPUDATION_FLAG='NC' and s_no=CR.S_NO) T where rw=1)
) 
as SETTLEMENT_DATE,

标签： sql-servertsql

解决方案

您的查询如下所示：

SELECT *

,(select 1 union all select 2
union all select 3
union all select 3
union (select 4)) as SETTLEMENT_DATE
FROM sys.tables

您正在选择一些列值，对于其中之一，您最多可以为一列返回 4 个值SETTLEMENT_DATE。

因此，您可以有多个值，其中一个值是预期的，这就是错误对您说的。你必须选择：

  • 如果您想查看所有值，只需像这样为每一列指定一个单独的名称

    SELECT *

,select 1 as SETTLEMENT_DATE1
,
select 2 as SETTLEMENT_DATE2
,
select 3 as SETTLEMENT_DATE3
,
select 3 as SETTLEMENT_DATE4
,
(select 4) as SETTLEMENT_DATE5
FROM sys.tables

  • 添加更多逻辑以每次从此子查询仅返回一个值

    (
    SELECT TOP 1 date
    FROM
    (

        (select top 1 convert(varchar,PaymentDate,103) from tblUTRDetail where CCN_No=CR.CCN_NO and CR.Claim_Status='Y' order by PaymentDate desc
        union all
        select top 1 convert(varchar,CreatedDate,103) from tblOpenCloseTrans where S_NO=CR.S_NO and OpenCloseType='C' and CR.Claim_Status='C' order by CreatedDate desc
        union all
        select top 1 convert(varchar,GENERATED_DATE,103) from tbldeficiencyletterTrans where S_NO=CR.S_NO and DEFICIENT_REPUDATION_FLAG='NC' and CR.Claim_Status='NC' order by GENERATED_DATE desc
        union all
        select top 1 convert(varchar,GENERATED_DATE,103) from tbldeficiencyletterTrans where S_NO=CR.S_NO and DEFICIENT_REPUDATION_FLAG='R' and CR.Claim_Status='RC' order by GENERATED_DATE desc
        union 
        (select GENERATED_DATE from  (Select s_no, convert (varchar,(GENERATED_DATE),103)as GENERATED_DATE ,row_number()
        over(partition by s_no order by s_no asc )as rw from tblDeficiencyLetterTrans b
        where DEFICIENT_REPUDATION_FLAG='NC' and s_no=CR.S_NO) T where rw=1)
        ) 
    )  DS (date)
    ORDER BY date
)
as SETTLEMENT_DATE,

推荐阅读