from 语句中的case 问题

2007-03-16 04:19:05 283 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

树莓派4B之OpenCV人脸识别实战：从Haar Cascade到实时检测（保姆级教程）本教程详细介绍了如何在树莓派4B上部署OpenCV，并利用Haar Cascade算法实现实时人脸识别。内容涵盖从系统环境搭建、算法原理解析到代码逐行讲解与实战调优，为嵌入式AI和计算机视觉初学者提供了一份完整的保姆级实践指南。阅读详情

我想实现下面的效果，不知怎么写？

select * from T1
case @Para1 when 1 then inner join T2
end
where .....
谢谢大家执教

...全文

在Ubuntu 22.04上从零搭建FVCOM环境：手把手搞定Linux、Intel编译器与NetCDF库本文详细指导在Ubuntu 22.04上从零搭建FVCOM环境，涵盖Linux系统配置、Intel编译器安装、NetCDF库编译及常见问题解决。通过手把手教程，帮助科研人员高效完成水动力数值模拟环境的部署，特别适合水环境研究领域的初学者。阅读详情

luckyTOTO

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

luckyTOTO 2007-03-16

打赏
举报

错了： SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_GetSeriesCourseList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].udf_GetSeriesCourseList
GO

CREATE FUNCTION [dbo].udf_GetSeriesCourseList
(
@SeriesID int,
@IsIncludeInstructorLedCourse bit = 1,
@IsIncludeInactiveCourse bit = 0,
@IsFilterByCourseUser bit = 1
)
/*
========================================================
Function: udf_GetSeriesCourseList
Description: Get the CourseUserStatus by courseuser or by courseuserround.
Parameters: @SeriesID -- id of series
@IsIncludeInstructorLedCourse -- 0,not in clude.1,include
@IsIncludeInactiveCourse -- 0,not include.1 include
@IsFilterByCourseUser -- 0,not filter by courseuser.1 filte by courseuser
Author: Walt Hu
Create Date: 03/15/2007
========================================================
Revision History
========================================================
Rev Date Author Description
========================================================
1 03/15/2007 Walt Hu Created
2 04/16/2007 Walt Hu Add parameter @IsIncludeInstructorLedCourse
3 04/16/2007 Walt Hu Exclude inactivecourse
========================================================
*/
RETURNS TABLE

AS
RETURN
(
SELECT
ROW_NUMBER() OVER (ORDER BY st.Sequence ASC, ss.Sequence ASC ,c.Sequence ASC) AS RowNumber,
c.CourseID,
ss.SkillsetID,
st.SuiteID,
s.SeriesID
FROM
Course c
LEFT OUTER JOIN Skillset ss ON c.SkillsetID = ss.SkillsetID
LEFT OUTER JOIN Suite st ON ss.SuiteID = st.SuiteID
LEFT OUTER JOIN Series s ON st.SeriesID = s.SeriesID
LEFT OUTER JOIN CourseUser cu ON c.CourseID = cu.CourseID
WHERE st.SeriesID = @SeriesID
AND c.CourseTypeID = CASE @IsIncludeInstructorLedCourse WHEN 1 THEN c.CourseTypeID
ELSE 0
END
AND c.CourseStatusID = CASE @IsIncludeInactiveCourse WHEN 0 THEN 0
ELSE 1
END
AND ISNULL(cu.CourseUserID,0) = CASE @IsFilterByCourseUser WHEN 0 THEN 1
ELSE 0
END
)

luckyTOTO 2007-03-16

打赏
举报

可以在where 语句中变相解决：
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_GetSeriesCourseList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].udf_GetSeriesCourseList
GO

CREATE FUNCTION [dbo].udf_GetSeriesCourseList
(
@SeriesID int,
@IsIncludeInstructorLedCourse bit = 1,
@IsIncludeInactiveCourse bit = 0
)
/*
========================================================
Function: udf_GetSeriesCourseList
Description: Get the CourseUserStatus by courseuser or by courseuserround.
Parameters: @SeriesID -- id of series
@IsIncludeInstructorLedCourse -- 0,not in clude.1,include
@IsIncludeInactiveCourse -- 0,not include.1 include
Author: Walt Hu
Create Date: 03/15/2007
========================================================
Revision History
========================================================
Rev Date Author Description
========================================================
1 03/15/2007 Walt Hu Created
2 04/16/2007 Walt Hu Add parameter @IsIncludeInstructorLedCourse
3 04/16/2007 Walt Hu Exclude inactivecourse
========================================================
*/
RETURNS TABLE

AS
RETURN
(
SELECT
ROW_NUMBER() OVER (ORDER BY st.Sequence ASC, ss.Sequence ASC ,c.Sequence ASC) AS RowNumber,
c.CourseID,
ss.SkillsetID,
st.SuiteID,
s.SeriesID
FROM
Course c
LEFT OUTER JOIN Skillset ss ON c.SkillsetID = ss.SkillsetID
LEFT OUTER JOIN Suite st ON ss.SuiteID = st.SuiteID
LEFT OUTER JOIN Series s ON st.SeriesID = s.SeriesID
LEFT OUTER JOIN CourseUser cu ON c.CourseID = cu.CourseID
WHERE st.SeriesID = @SeriesID
AND c.CourseTypeID = CASE @IsIncludeInstructorLedCourse WHEN 1 THEN c.CourseTypeID
ELSE 0
END
AND c.CourseStatusID = CASE @IsIncludeInactiveCourse WHEN 0 THEN 0
ELSE 1
END
AND ISNULL(cu.CourseUserID,0) = CASE @IsIncludeInactiveCourse WHEN 0 THEN 1
ELSE 0
END
)

luckyTOTO 2007-03-16

打赏
举报

开来只能这样了

wangdehao 2007-03-16

打赏
举报

那就直接用if语句吧

luckyTOTO 2007-03-16

打赏
举报

zlp321002(行走江湖，万事低调可保一路平安) 谢谢我想问一下 case 语句能用在from 子句中马？

paoluo 2007-03-16

打赏
举报

似乎只有最笨的方法

Declare @Para1 Int
Select @Para1 = 1
If @Para1 = 1
select * from T1
inner join T2
On t1.id = t2.id
where t1.id > 0
Else
select * from T1
where t1.id > 0

saiwong 2007-03-16

打赏
举报

declare @sql varchar(4000)
select @sql='select * from T1'+case @Para1 when 1 then +' inner join T2 on ...' end +' where .....'
exec(@sql)

huyouni 2007-03-16

打赏
举报

luckyTOTO(NULL) ( ) 信誉：95 Blog 2007-03-16 16:23:55 得分: 0

不用动态语句

------------------------
那就用zlp321002(行走江湖，万事低调可保一路平安)的

wangdehao 2007-03-16

打赏
举报

declare @Para1 int
declare @sql varchar(8000)

set @Para1=1
set @sql= case @Para1 when 1 then 'select * from T1 inner join ...' end

exec(@sql)

zlp321002 2007-03-16

打赏
举报

select * from T1
case @Para1 when 1 then inner join T2
end
where .....
-------------------->>
if @Para1=1
begin
select * from T1 inner join t2
on ....
end

luckyTOTO 2007-03-16

打赏
举报

不用动态语句

luckyTOTO 2007-03-16

打赏
举报

select * from T1
case @Para1 when 1 then inner join T2
end
where t1.id > 0

where 子句不用考虑

huyouni 2007-03-16

打赏
举报

exec(@sql)

huyouni 2007-03-16

打赏
举报

declare @sql varchar(8000)
select @sql='select * from T1'+case @Para1 when 1 then +' inner join T2 on ...' end +' where .....'

renjun24 2007-03-16

打赏
举报

不会，帮顶

wangdehao 2007-03-16

打赏
举报

能不能把问题写完整了？

from 语句中的case 问题

相关推荐