Inner with if语句

Question

我正在尝试构建以下sp：

USE [MarMoniApp]
GO
/****** Object:  StoredProcedure [dbo].[Desc_by_date]    Script Date: 10/23/2012     09:01:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Desc_by_date]
@DateToSearch varchar(50)
AS
BEGIN
select a.ticker,a.First_T, a.Last_T, a.Size_mln, a.pct_d_vol,a.a_trad,a.coolname,a.recency,b.Analyst
from Order_Desc a

CASE
When a.coolname = 'ELTORO'
then (Inner join MarMoniApp.dbo.Namelist b on a.ticker = b.ticker )
END

where convert(datetime, cast(rtrim(First_T) AS nvarchar)) > DateToSearch 
order by  CONVERT(DATETIME, cast(rtrim(First_T) AS nvarchar) )  DESC
END

我只想在"coolname“等于eltoro的情况下进行内连接，但似乎我的语法不正确。

Answer 1

创建两个select语句，然后将它们联合起来怎么样？

select ... from Order_Desc a
Inner join MarMoniApp.dbo.Namelist b on a.ticker = b.ticker
where a.coolname = 'ELTORO' AND ...

union

select ... from Order_Desc a
where a.coolname != 'ELTORO' AND ...

Answer 2

您应该使用左外连接，如下所示：

select a.ticker,a.First_T, a.Last_T, a.Size_mln, a.pct_d_vol,a.a_trad,a.coolname,a.recency,b.Analyst
from Order_Desc a
LEFT OUTER JOIN MarMoniApp.dbo.Namelist b
on a.ticker = b.ticker
**where 
(   
    (a.coolname <> 'ELTORO')
    OR
    (a.coolname = 'ELTORO' AND a.ticker = b.ticker)
)** 
AND convert(datetime, cast(rtrim(First_T) AS nvarchar)) > DateToSearch 
order by  CONVERT(DATETIME, cast(rtrim(First_T) AS nvarchar) )  DESC

Answer 3

你可能需要这样的东西：

INNER JOIN
 MarMoniApp.dbo.Namelist b 
ON 
 a.coolname = 'ELTORO'
 AND a.ticker = b.ticker 

基本上--您不能进行条件连接。您只能选择要联接的行。

如果只在a.coolname = 'ELTORO'的情况下才需要MarMoniApp.dbo.Namelist表中的列，请使用LEFT JOIN。

另一个选项(即在性能是关键的情况下)是使用UNION ALL作为@Turcia suggested (使用NULL作为没有JOIN的part的额外列的值)。