如何根据SQL中的其他值减少值（无循环） [英] How to decrease a value on the basis of other value in SQL (No Loop)

问题描述

我有以下要求：



加入表

 姓名加入日期 
 X 15-Aug-14 
 Y 5-Aug-14 
 Z 25-Aug- 14 

假期表

 假日日期 
 IndependenceDay 15-Aug-14 
 CompanyHoliday 2014年8月14日
 FriendshipDay 5-Aug-14 

我们需要输出为

 名称NewJoiningDate  
 X 13-Aug-14 
 Y 4 -Aug-14 
 Z 25-Aug-14 

请不要使用while循环或光标。我想它可以通过递归CTE或核心子查询来完成。还没有答案:(



谢谢

Anki

解决方案

< blockquote>有一个更好的解决方案，引用Nitesh的

谢谢Nitesh

;  with  abc  as  
（
  SELECT 名称， joindate  FROM  #Joining 
  union   all  
 选择名称，DATEADD（DAY，-1，日期）来自 #holiday h 
 内部 加入 abc < span class =code-keyword> on  abc.joiningdate = h。 date  
）
 选择名称，min（加入日期）来自 abc 
  group  按名称

;  WITH  
c 
  AS  
（
  SELECT  a.Name，b.NewDate 
  FROM 加入
  LEFT   JOIN  
（ SELECT  DATEADD（DAY，-1，[Holiday  Date ]）NewDate 
  FROM  Holiday 
  WHERE  DATEADD（DAY，-1，[Holiday  Date ]）不  IN （ SELECT  [Holiday 日期]  FROM 假日）
）b  ON  a。[加入日期]> b.NewDate 
），
d 
  AS  
（
  SELECT  c.Name，MAX（c.NewDate）NewDate  FROM  c 
  GROUP   BY  c.Name 
）
  SELECT  j.Name，
  CASE   WHEN  h。[Holiday  Date ]  IS   NULL  那么 j。[加入日期]  ELSE  d.NewDate  END  
  FROM 加入j 
  LEFT  加入假日h  ON  j。[加入日期] = h。[假日日期] 
  INNER   JOIN  d  ON  j.Name = d.Name 

  UPDATE  A  SET  NewJoiningDate = DATEADD（DAY，-1，B.HolidayDate） FROM  JoiningTable A 
  INNER   JOIN  HolidayTable B 
  ON  A.JoiningDate = B.Holidaydate 

Hi,

I have a below requirement:

Joining Table

Name	Joining Date
    X	15-Aug-14
    Y	5-Aug-14
    Z	25-Aug-14

Holiday Table

Holiday         Date
IndependenceDay 15-Aug-14
CompanyHoliday 14-Aug-2014
FriendshipDay 5-Aug-14

We need the output as

Name NewJoiningDate
X 13-Aug-14
Y 4-Aug-14
Z 25-Aug-14

Please do not use a while loop or a cursor. I guess it can be done by Recursive CTE or a corelated subquery. Don't have a answer yet :(

Thanks
Anki

解决方案

Got a better solution referencing Nitesh's
Thanks Nitesh

;with abc as
(
 SELECT Name, joiningdate FROM #Joining
 union all
 select name, DATEADD(DAY, -1, date) from #holiday  h
 inner join abc on abc.joiningdate = h.date
 )
select name,min(joiningdate) from abc
group by name

;WITH
c
AS
(
    SELECT a.Name, b.NewDate
    FROM Joining a
    LEFT JOIN
            ( SELECT DATEADD(DAY, -1, [Holiday Date]) NewDate
              FROM Holiday
              WHERE DATEADD(DAY, -1, [Holiday Date]) NOT IN (SELECT [Holiday Date] FROM Holiday)
            ) b ON a.[Joining Date] > b.NewDate
),
d
AS
(
    SELECT c.Name, MAX(c.NewDate) NewDate FROM c
    GROUP BY c.Name
)
SELECT j.Name,
CASE WHEN h.[Holiday Date] IS NULL THEN j.[Joining Date] ELSE d.NewDate END
FROM Joining j
LEFT JOIN Holiday h ON j.[Joining Date] = h.[Holiday Date]
INNER JOIN d ON j.Name = d.Name

UPDATE A SET NewJoiningDate= DATEADD(DAY,-1,B.HolidayDate) FROM JoiningTable A
INNER JOIN HolidayTable B
ON A.JoiningDate=B.Holidaydate

这篇关于如何根据SQL中的其他值减少值（无循环）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！