sql - 我的 CASE 语句是错误的。知道我做错了什么吗？

Question

我陷入了困境。

当我运行以下查询时，它有效:

 select DISTINCT l.Seating_Capacity - (select count(*)
                        from tblTrainings t1, tbllocations l
                        where l.locationId = t1.LocationId) as
                        availableSeats
                        from tblTrainings t1, tbllocations l
                        where l.locationId = t1.LocationId

但是，我们想添加一个 CASE 语句，说明当 Seating_Capacity - 上面显示的总计数 = 0 时，然后显示“FULL”消息。

否则，显示剩余数量。

这是查询:

                 select DISTINCT case when l.Seating_Capacity - (select count(*)
                from tblTrainings t1, tbllocations l
                where l.locationId = t1.LocationId) = 0 then 'full' else STR(Seating_Capacity) end)
                availableSeats
                from tblTrainings t1, tbllocations l
                where l.locationId = t1.LocationId

我在 'End' 附近收到 ') 附近的语法错误

我还收到一个错误，指出内部 Seating_Capacity 是无效的列名。

非常感谢您的协助。

我一定是在梦境中，因为我认为它在测试期间有效。

现在，该应用已上线但无法运行。

提前致谢

select DISTINCT l.LocationId,c.courseId, c.coursename, l.Seating_Capacity - (select count(*)
                        from tblTrainings t1
                        where l.locationId = t1.LocationId and c.courseId = t1.courseId) as
                        availableSeats,d.dateid,d.trainingDates,d.trainingtime,c.CourseDescription,
                        i.instructorName,l.location,l.seating_capacity 
                        from tblLocations l
                        Inner Join tblCourses c on l.locationId = c.locationId
                        left join tblTrainings t on l.locationId = t.LocationId and c.courseId = t.courseId
                        Inner Join tblTrainingDates d on c.dateid=d.dateid 
                        Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId  
                        Inner Join tblInstructors i on ic.instructorId = i.instructorId
                        WHERE CONVERT(VARCHAR(10), d.trainingDates, 101) >= CONVERT(VARCHAR(10), GETDATE(), 101)

Answer 1

为避免重复表达式，您可以使用 WITH clause简化您的查询:

WITH (
   -- Start with your query that already works
   SELECT DISTINCT l.Seating_Capacity - (select count(*)
                    from tblTrainings t1, tbllocations l
                    where l.locationId = t1.LocationId) AS availableSeats
   FROM tblTrainings t1, tbllocations l
   WHERE l.locationId = t1.LocationId
) AS source
SELECT
    -- Add a CASE statement on top of it
    CASE WHEN availableSeats = 0 THEN 'Full'
    ELSE STR(availableSeats)
    END AS availableSeats
FROM source