sql - 条件 SQL 查询

Question

我有一个需要条件化的 SQL 查询，这是我目前的 SQL 查询:

-- IF the video count is 1
    UPDATE tbl_Video SET
    [Featured] = 1
    WHERE [VideoId] = 1
    GO
-- ELSE IF Video Count is greater than 1
    UPDATE tbl_Video SET
    [Featured] = 0
    WHERE [Featured] = 1
    GO
    -- Set the top 5 viewed videos as featured as default
    UPDATE tbl_Video SET
    [Featured] = 1
    WHERE VideoId In (SELECT TOP 5 VideoId FROM tbl_Video
    ORDER BY Views DESC)
    GO
-- END

希望上面查询中的注释能够解释我想要实现的目标，场景是:

If the video count is 1, make the featured column true where video Id = 1.
If the video count is greater than 1, mark all the featured columns as false then make the top 5 viewed videos as featured.

数据库是 SQL Server。

谢谢，

Answer 1

如果这是在一个 sql server 过程中，你可以这样做:

declare @videoCount integer

--this is just a guess at what video count is
select @videoCount = count(*) from tbl_video

if (@videoCount = 1)
    UPDATE tbl_Video SET
    [Featured] = 1
    WHERE [VideoId] = 1
ELSE IF (@videoCount > 1)
BEGIN
    UPDATE tbl_Video SET
    [Featured] = 0
    WHERE [Featured] = 1

    UPDATE tbl_Video SET
    [Featured] = 1
    WHERE VideoId In (SELECT TOP 5 VideoId FROM tbl_Video ORDER BY Views DESC)
END