使用IF ... THEN Sql查询并返回int和String

I have this Table

我有这张桌子

CREATE TABLE [dbo].[Room] (
 [Id]            INT           IDENTITY (1, 1) NOT NULL,
 [Name]          NVARCHAR (32) NOT NULL,
 [Capacity]      TINYINT       NOT NULL,
 [CapacityOccupied] TINYINT       NOT NULL,
 CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED ([Id] ASC)
);

And Data in Table is:

表中的数据是:

.

I have use query for find status capacity, like this:

我使用查询查找状态容量,如下所示:

• if room is full show full

  如果房间已满,请显示已满

• if room is empty show empty

  如果房间空是空的

• else show free capacity room

否则显示免费容量空间

i use this query but, i don't know how to show free capacity.

我使用此查询,但我不知道如何显示免费容量。

SELECT CASE 
        WHEN (Capacity-CapacityOccupied)= Capacity
           THEN 'Empty'
        when (Capacity-CapacityOccupied)=0
            then 'Full'
   END as Salable, * 
FROM Room

And result is:

结果是:

Row 2,3,4 -> how to show free capacity? for example: - Empty - 3 - 2 - 1 - Full

排2,3,4 - >如何显示空闲容量?例如: - 空 - 3 - 2 - 1 - 完全

4 个解决方案

#1

Try this way:

试试这种方式:

SELECT CASE 
        WHEN (Capacity-CapacityOccupied)= Capacity
           THEN 'Empty'
        when (Capacity-CapacityOccupied)=0
            then 'Full'
        else cast((Capacity-CapacityOccupie) as varchar(10))
   END as Salable, * 
FROM Room

#2

Try :

SELECT 
   CASE 
       WHEN (Capacity-CapacityOccupied)= Capacity
           THEN 'Empty'
       WHEN (Capacity-CapacityOccupied)=0
           THEN 'Full'
       ELSE 
           CAST((Capacity-CapacityOccupied) AS nvarchar(100))
   END AS Salable, * 
FROM Room

#3

Just add an Else. Also, the Case can be simplified:

只需添加一个Else。此外,案例可以简化:

SELECT CASE 
        WHEN CapacityOccupied = 0
           THEN 'Empty'
        when Capacity = CapacityOccupied
            then 'Full'
        Else Cast(Capacity - CapacityOccupied As Varchar (5))
   END as Salable, * 
FROM Room

#4

You need to add a default to your CASE.

您需要为CASE添加默认值。

You are hitting your conditions but you need to cater for the condition when it is not completely full or completely empty.

你正在达到你的条件,但你需要在没有完全满或完全空的情况下迎合这个条件。

Edit

Add this before the END

在END之前添加此项

ELSE THEN 'Partially Occupied'

然后'部分占用'