SQL Querry中的case语句 [英] case statement in SQL Querry

问题描述

大家好，
我为项目写了一个存储过程.在查询中，我有一个名为需要预订?"的字段.其可能的答案是一个复选框，即是"或否".
现在，此查询用于创建视图，但在视图页面上，该字段的"NO"为"0"，"YES"为"1".但是我希望这些字段为"YES"或"NO"类型，而不是"0"和"1"类型，所以如何我可以在查询中使用"case"语句吗? 我的SP如下:





创建过程[dbo].[usp_RW_TASEXIBIITA]

-@ int INT
AS
开始

设置NOCOUNT ON

SELECT tbl_TAS_TASRequestMaster.SubmissionDate
，tbl_PM_Project.ProjectName
，tbl_TAS_TASRequestMaster.TRNo
，tbl_PM_Employee.EmployeeName
，tbl_TAS_TASRequestMaster.DateOfTravel
，tbl_TAS_TASRequestMaster.PlaceOfTravel
，tbl_TAS_TASRequestMaster.DateOfTravel
，tbl_TAS_TASRequestMaster.ReturnDate
，tbl_PM_Customer.CustomerName
，tbl_PM_Role.RoleDescription
，tbl_TAS_TASRequestMaster.Purpose
，tbl_TAS_TASRequestMaster.BorneBy
，tbl_TAS_TASRequestMaster. 需要预订-位字段

，tbl_TAS_TASRequestMaster.BookingDetails
，tbl_TAS_TASRequestMaster.HotelBookingRequired
，tbl_TAS_TASRequestMaster.HotelDetails
，tbl_TAS_TASRequestMaster.AmountRequested
，tbl_TAS_TASRequestMaster.PayementMode
，tbl_TAS_TASRequestMaster.AdvanceAmountGiven
，tbl_TAS_TASRequestMaster.CommentsByFinanceDept

来自tbl_TAS_TASRequestMaster WITH(NOLOCK)

内联tbl_PM_Project WITH(NOLOCK)
开启tbl_PM_Project.ProjectID = tbl_TAS_TASRequestMaster.ProjectID


内联tbl_PM_Employee WITH(NOLOCK)
开启tbl_PM_Employee.EmployeeID = tbl_TAS_TASRequestMaster.EmployeeID


内联tbl_PM_Customer WITH(NOLOCK)
开启tbl_PM_Customer.Customer = tbl_TAS_TASRequestMaster.CustomerID


内联接tbl_PM_Role WITH(NOLOCK)
开启tbl_PM_Role.RoleID = tbl_PM_Employee.PostID


设置NOCOUNT OFF
END

Hi All,
I wrote one Store Procedure for my project.In my querry i have one bit field called "Booking Required ?" and its probable answer is a checkbox i.e YES or NO.
Now this querry is used to create a view but on the view page this field come as "0" for NO and "1" for YES.But i want these field as YES or NO type and not the 0 and 1 type,So how can i use "case" statement in my querry????plz guide me...
My SP is as follows :





CREATE PROCEDURE [dbo].[usp_RW_TASEXIBIITA]

--@int INT
AS
BEGIN

SET NOCOUNT ON

SELECT tbl_TAS_TASRequestMaster.SubmissionDate
,tbl_PM_Project.ProjectName
,tbl_TAS_TASRequestMaster.TRNo
,tbl_PM_Employee.EmployeeName
,tbl_TAS_TASRequestMaster.DateOfTravel
,tbl_TAS_TASRequestMaster.PlaceOfTravel
,tbl_TAS_TASRequestMaster.DateOfTravel
,tbl_TAS_TASRequestMaster.ReturnDate
,tbl_PM_Customer.CustomerName
,tbl_PM_Role.RoleDescription
,tbl_TAS_TASRequestMaster.Purpose
,tbl_TAS_TASRequestMaster.BorneBy
,tbl_TAS_TASRequestMaster.BookingRequired--a bit field

,tbl_TAS_TASRequestMaster.BookingDetails
,tbl_TAS_TASRequestMaster.HotelBookingRequired
,tbl_TAS_TASRequestMaster.HotelDetails
,tbl_TAS_TASRequestMaster.AmountRequested
,tbl_TAS_TASRequestMaster.PayementMode
,tbl_TAS_TASRequestMaster.AdvanceAmountGiven
,tbl_TAS_TASRequestMaster.CommentsByFinanceDept

FROM tbl_TAS_TASRequestMaster WITH(NOLOCK)

INNER JOIN tbl_PM_Project WITH(NOLOCK)
ON tbl_PM_Project.ProjectID = tbl_TAS_TASRequestMaster.ProjectID


INNER JOIN tbl_PM_Employee WITH(NOLOCK)
ON tbl_PM_Employee.EmployeeID=tbl_TAS_TASRequestMaster.EmployeeID


INNER JOIN tbl_PM_Customer WITH(NOLOCK)
ON tbl_PM_Customer.Customer= tbl_TAS_TASRequestMaster.CustomerID


INNER JOIN tbl_PM_Role WITH(NOLOCK)
ON tbl_PM_Role.RoleID=tbl_PM_Employee.PostID


SET NOCOUNT OFF
END

推荐答案

尝试

Try

,tbl_TAS_TASRequestMaster.BorneBy<br />
, case tbl_TAS_TASRequestMaster.BookingRequired WHEN 1 THEN ''YES'' ELSE ''NO'' END<br />
,tbl_TAS_TASRequestMaster.BookingDetails

您还可以将位字段更改为枚举

例如:ENUM(''Yes''，''No'')

you can also change the bit field as enum

eg: ENUM(''Yes'',''No'')

这篇关于SQL Querry中的case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！