时间戳

首页 > 解决方案 > 如何通过引用先前的值来添加 IF ELSE

sql - 如何通过引用先前的值来添加 IF ELSE

问题描述

我正在尝试读取我之前的“OPENSEATING”值并将其存储到变量@OPENSEATING 中。对于第二个查询，如果匹配条件执行以下查询，我希望它首先检查 @OPENSEATING 的值。我相信我的声明是正确的，是否有任何语法错误？

DECLARE @OPENSEATING AS BIT
    SELECT

        '2019-01-31' AS DATE,


    (SELECT 
      THEATRES.OPENSEATING
        FROM
            SEAT LEFT JOIN THEATRES ON SEAT.ROOMID = THEATRES.ID
            LEFT JOIN SHOWTIMES AS B  ON THEATRES.ID = B.THEATREID 
            LEFT JOIN TICKET_ITEMS ON TICKET_ITEMS.SEATNO = SEAT.SEATNO AND B.ID = TICKET_ITEMS.SHOWTIMESID
        WHERE
            B.ID = A.ID
        GROUP BY THEATRES.OPENSEATING) AS @OPENSEATING,


    IF(@OPENSEATING =0)
    BEGIN
      (SELECT 
        (THEATRES.CAPACITY - COUNT(TICKET_ITEMS.ID)) AS SEATREMAINING

      FROM
        SEAT LEFT JOIN THEATRES ON SEAT.ROOMID = THEATRES.ID
        LEFT JOIN SHOWTIMES  ON THEATRES.ID = SHOWTIMES.THEATREID 
        LEFT JOIN TICKET_ITEMS ON TICKET_ITEMS.SEATNO = SEAT.SEATNO AND SHOWTIMES.ID = TICKET_ITEMS.SHOWTIMESID
      WHERE
        SHOWTIMES.ID = A.ID
      GROUP BY THEATRES.CAPACITY) AS REMAININGSEAT
    END
    ELSE IF(@OPENSEATING =1)
    BEGIN
      (SELECT 
        (THEATRES.CAPACITY - COUNT(TICKET_ITEMS.ID)) AS SEATREMAINING

      FROM
        SHOWTIMES LEFT JOIN THEATRES ON THEATRES.ID = SHOWTIMES.THEATREID 
        LEFT JOIN TICKET_ITEMS ON TICKET_ITEMS.SEATNO = SEAT.SEATNO AND SHOWTIMES.ID = TICKET_ITEMS.SHOWTIMESID
      WHERE
        SHOWTIMES.ID = A.ID
      GROUP BY THEATRES.CAPACITY) AS REMAININGSEAT
    END



        SHOWSCHEDULES.ID AS SHOWSCHEDULE_ID,
        A.ID AS SHOWTIME_ID,
        A.THEATREID
    FROM
        SHOWSCHEDULES JOIN SHOWTIMES AS A ON SHOWSCHEDULES.ID = A.SHOWSCHEDULEID
        JOIN SHOWLANGUAGES ON A.SHOWLANGUAGEID = SHOWLANGUAGES.ID
    WHERE
        '2019-01-31' BETWEEN CONVERT(VARCHAR(10), SHOWSCHEDULES.SHOWFROMDATE, 20) AND CONVERT(VARCHAR(10), SHOWSCHEDULES.SHOWTODATE, 20)
     AND CONVERT(VARCHAR(8), getdate(), 8) < CONVERT(VARCHAR(10), A.SHOWENDTIME,8) 
  ORDER BY SHOWBEGINTIME_24 ASC
    ;

正如@Slava Murygin 建议的那样，我尝试如下创建CTE TABLE，但即使这样也显示错误，我的简单cte 表有什么问题。内部查询工作正常。

WITH cte_name (OPENSEATING) AS (
    SELECT

        (SELECT 
      THEATRES.OPENSEATING
        FROM
            SEAT LEFT JOIN THEATRES ON SEAT.ROOMID = THEATRES.ID
            LEFT JOIN SHOWTIMES AS B  ON THEATRES.ID = B.THEATREID 
            LEFT JOIN TICKET_ITEMS ON TICKET_ITEMS.SEATNO = SEAT.SEATNO AND B.ID = TICKET_ITEMS.SHOWTIMESID
        WHERE
            B.ID = A.ID
        GROUP BY THEATRES.OPENSEATING) AS OPENSEATING

  FROM
        SHOWSCHEDULES JOIN SHOWTIMES AS A ON SHOWSCHEDULES.ID = A.SHOWSCHEDULEID
        JOIN SHOWLANGUAGES ON A.SHOWLANGUAGEID = SHOWLANGUAGES.ID
    WHERE
        '2019-01-31' BETWEEN CONVERT(VARCHAR(10), SHOWSCHEDULES.SHOWFROMDATE, 20) AND CONVERT(VARCHAR(10), SHOWSCHEDULES.SHOWTODATE, 20)
     AND CONVERT(VARCHAR(8), getdate(), 8) < CONVERT(VARCHAR(10), A.SHOWENDTIME,8)
)

标签： sqlsql-server

解决方案

你不能在一个查询中做到这一点。您必须在第一个查询中获取变量，然后在 IF 语句中使用它。

如果您只想使用一个语句，那么您必须使用 CTE，但它会隐藏逻辑并且难以调试。

它可能是这样的，但不能保证它会起作用，因为我不知道你的业务逻辑和背后的数据：

;WITH OPENSEATING as (
    SELECT THEATRES.OPENSEATING, B.ID
    FROM SEAT 
    LEFT JOIN THEATRES ON SEAT.ROOMID = THEATRES.ID
    LEFT JOIN SHOWTIMES AS B  ON THEATRES.ID = B.THEATREID 
    LEFT JOIN TICKET_ITEMS ON TICKET_ITEMS.SEATNO = SEAT.SEATNO AND B.ID = TICKET_ITEMS.SHOWTIMESID
    GROUP BY THEATRES.OPENSEATING),
REMAININGSEAT0 as (
    SELECT st.ID, (t.CAPACITY - COUNT(ti.ID)) AS SEATREMAINING
    FROM SEAT as s 
    LEFT JOIN THEATRES as t ON s.ROOMID = t.ID
    LEFT JOIN SHOWTIMES as st ON t.ID = st.THEATREID 
    LEFT JOIN TICKET_ITEMS ON ti.SEATNO = s.SEATNO AND st.ID = ti.SHOWTIMESID
    GROUP BY t.CAPACITY),
REMAININGSEAT1 as (
    SELECT st.ID, (t.CAPACITY - COUNT(ti.ID)) AS SEATREMAINING
    FROM SHOWTIMES as st
    LEFT JOIN THEATRES as t ON t.ID = st.THEATREID 
    LEFT JOIN SEAT as s ON s.ROOMID = t.ID
    LEFT JOIN TICKET_ITEMS as ti ON ti.SEATNO = s.SEATNO AND st.ID = ti.SHOWTIMESID
    GROUP BY t.CAPACITY)

SELECT '2019-01-31' AS DATE
    , ss.ID AS SHOWSCHEDULE_ID
    , A.ID AS SHOWTIME_ID
    , A.THEATREID
    , SEATREMAINING = CASE OS.OPENSEATING WHEN 0 THEN RS0.SEATREMAINING ELSE RS1.SEATREMAINING END
FROM SHOWSCHEDULES as ss
INNER JOIN SHOWTIMES AS A ON ss.ID = A.SHOWSCHEDULEID
INNER JOIN SHOWLANGUAGES as SL ON A.SHOWLANGUAGEID = SL.ID
INNER JOIN OPENSEATING as OS ON OS.ID = A.ID
LEFT JOIN REMAININGSEAT0 as RS0 ON RS0.ID = A.ID
LEFT JOIN REMAININGSEAT1 as RS1 ON RS1.ID = A.ID
WHERE '2019-01-31' BETWEEN CONVERT(VARCHAR(10), ss.SHOWFROMDATE, 20) AND CONVERT(VARCHAR(10), ss.SHOWTODATE, 20)
    AND CONVERT(VARCHAR(8), getdate(), 8) < CONVERT(VARCHAR(10), A.SHOWENDTIME,8) 
ORDER BY SHOWBEGINTIME_24 ASC;

推荐阅读