如何将requd列添加到我现有的表中... [英] How to Add requd column to my existing table as ...

问题描述

我的桌子是







如何将requddue列作为...

my table is



How to get the requddue column as...

id   FeeID    defineddue     ActualDue   reqdDue

      

1     69        1              3           2  (as 3 doesnot exist in defineddue 
                                               (for Feeid 69) it should take 2) 
                                        
2     69        2              3           2 

3     65        1              2           2

4     65        2              2           2  (as 2 exists for 65 in defined due)

4     65        3              2           2
                                        
5     70        1              3           1  ( as 3 and 2 does not exist in 
                                                defined due for 70)

 there are only 3 possible values for duedays 1,2,3 respectively ..

i guues we can achieve this with case statement..

推荐答案

每个feeid的reqddue是min（max（defineddue） ，actualdue），对吧？

首先，我们需要一个称为公用表表达式（CTE）的临时结果集来捕获每个feeid的max（defineddue），然后我们将这个cte与实际表连接起来，比如table1来得到所需的结果。参见示例：

The reqddue for each feeid is the min(max(defineddue), actualdue), right?
First, we need a temporary resultset known as Common Table Expression (CTE) to capture the max(defineddue) for each feeid, then we join this cte with the actual table, say table1 to get the desired result. See example:

WITH CTE1 (feeid, maxdefineddue, actualdue)
AS
(
    SELECT feeid, MAX(defineddue) AS maxdefineddue, actualdue
    FROM table1 GROUP BY feeid, actualdue
)
SELECT t.id, t.feeid, t.defineddue, t.actualdue,
CASE WHEN c.maxdefineddue > c.actualdue THEN c.actualdue ELSE c.maxdefineddue END AS reqddue
FROM CTE1 c JOIN table1 t ON c.feeid = t.feeid ORDER BY t.id

阅读更多关于 WITH common_table_expression（Transact-SQL） [ ^ ]

这篇关于如何将requd列添加到我现有的表中...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！