我有两个表Companies1
和Companies2
,其中公司名称始终存在于Companies2
表中,但可能位于Companies1
表中,也可能不在表中。
我需要应用此条件:
CONVERT(VARCHAR(11), GETDATE(), 112) BETWEEN Start AND End
仅当表中存在名称Companies1
:
SELECT DISTINCT
COALESCE(NULLIF(Company1_Name, ''), Company2_Name) Company
FROM
[Companies1] AS C1
LEFT JOIN
[Companies2] AS C2 ON (C1.Company1_Name = C2.Company2_Name)
WHERE
Company1_Name = ('XYZ')
AND CONVERT(VARCHAR(11), GETDATE(), 112) BETWEEN Start AND End
我怎样才能做到这一点?
请尝试这样的事情。另外,请不要在 where 子句中的列周围使用函数,因为它会使查询不可优化。
select DISTINCT COALESCE(NULLIF(Company1_Name,''),Company2_Name) Company
FROM [Companies1] as C1 left join [Companies2] AS C2
on(C1.Company1_Name=C2.Company2_Name) where Company1_Name=('XYZ')
AND
CONVERT(VARCHAR(11), getdate(), 112) >=
CASE WHEN C1.Company1_Name IS NOT NULL THEN [Start] ELSE CONVERT(VARCHAR(11), getdate(), 112) END
AND
CONVERT(VARCHAR(11), getdate(), 112) <=
CASE WHEN C1.Company1_Name IS NOT NULL THEN [End] ELSE CONVERT(VARCHAR(11), getdate(), 112) END
SELECT DISTINCT COALESCE(NULLIF(Company1_Name,''),Company2_Name) Company
FROM [Companies1] AS C1 LEFT JOIN [Companies2] AS C2 ON (C1.Company1_Name=C2.Company2_Name) WHERE EXISTS Company1_Name=('XYZ') AND
CONVERT(VARCHAR(11), getdate(), 112)
IN BETWEEN Start AND End