[SQL] IN 使用資料表值函示傳回table篩選條件

資料列表

1.建立function
CREATE FUNCTION GetWeekStrings (@roomListID int)
RETURNS TABLE
AS
RETURN (
         SELECT [Week] FROM RoomOpenSet WHERE RoomListID= 1
          )

2.找出資料Week欄位不等於 2,3,4
SELECT *, DATEPART(WEEKDAY, StartDate -1) as [Week] FROM RoomOpen WHERE RoomListID = 1 AND convert(varchar,DATEPART(WEEKDAY, StartDate -1)) not in
(
  Select * From GetWeekStrings(1)
)

結果為下表,過濾掉星期2,3,4 只撈出星期五的資料