如果记录与另一个表中的条目（例如假日日期）匹配，则排除记录的 SQL 查询

SQL query to exclude records if it matches an entry in another table (such as holiday dates)

提问by Tai Squared

I have two tables:

我有两个表：

Application
applicationid (int)
applicationname (varchar)
isavailable (bit)

应用
程序 applicationid (int)
applicationname (varchar)
isavailable (bit)

and

和

Holidays
applicationid (int)
holidaydate (datetime)

假期
applicationid (int)
假期日期 (datetime)

I need to get the isavailableflag for any given applicationname but it should only return if the day if is not a holiday. The isavailableflag is independent of holidays - it is only set if there are system-wide problems, not on a set schedule.

我需要为任何给定的应用程序名称获取isavailable标志，但它应该只在当天不是假期时返回。该isavailable标志是独立的节日-如果有系统范围内的问题，而不是按照设定的计划只能置。

I initially had something like:

我最初有这样的事情：

select top 1 apps.isavailable
from dbo.Applications apps, dbo.Holidays hol
where apps.applicationid = hol.applicationid and
      apps.applicationname = @appname and
      ((datediff(dd,getdate(),hol.holidaydate)) != 0)

but that was returning records even if today was a holiday because the other holiday dates don't equal today.

但即使今天是假期，这也会返回记录，因为其他假期日期不等于今天。

I tried

我试过

and (CONVERT(VARCHAR,getdate(),101)) not in (CONVERT(VARCHAR,hol.holidaydate,101))

(it is on SQL Server 2005, so there is no Date type so I have to convert it)

（它在 SQL Server 2005 上，所以没有日期类型，所以我必须转换它）

but again, it was returning records even if today was a holiday. How can I structure this query using a "not in" or "except" clause (or something else) to only return a record if today isn't a holiday?

但同样，即使今天是假期，它也在返回记录。如果今天不是假期，我如何使用“not in”或“except”子句（或其他东西）来构造此查询以仅返回记录？

Update

更新

I don't need a list of all applicationnames that don't have a holiday - I need a record for the specified apps.applicationname. The answers below only return the application names that don't have a holiday on today. The query should return the isavailable flag if it is not a holiday, or else return no records if it is a holiday. I don't care about the other applications.

我不需要所有没有假期的应用程序名称的列表 - 我需要指定应用程序名称的记录。下面的答案仅返回今天没有假期的应用程序名称。如果不是假期，查询应该返回 isavailable 标志，如果是假期，则不返回任何记录。我不关心其他应用程序。

Also, what if I added a table like:

另外，如果我添加了一个表，比如：

HoursOfOperations
applicationid (int)
mondayopen (datetime)
mondayclose (datetime)
tuesdayopen (datetime)
tuesdayclose (datetime)
//open and close for all seven days of the week

HoursOfOperations
applicationid (int)
mondayopen (datetime)
mondayclose (datetime)
tuesdayopen (datetime)
tuesdayclose (datetime)
//打开和关闭一周的所有 7 天

Could I join on all three of these tables to only return a record if it is within the hours for the given day and is not a holiday? Do I have to do this in separate queries?

如果它在给定日期的小时内并且不是假期，我是否可以加入所有这三个表以仅返回记录？我必须在单独的查询中执行此操作吗？

回答by Mitchel Sellers

THe following query should get you a list of applications that DO NOT have a holiday defined for the CURRENT date.

以下查询应为您提供未为当前日期定义假期的应用程序列表。

SELECT apps.ApplicationName, apps.isavailable 
FROM dbo.Applications apps
WHERE apps.ApplicationName = @AppName
    AND NOT EXISTS 
( SELECT * 
  FROM Holidays 
  WHERE ApplicationId = apps.ApplicationId
     AND CONVERT(VARCHAR,getdate(),101) = CONVERT(VARCHAR,holidaydate,101)
)

Basically what we do is select everything where it does not have a match.

基本上我们所做的是选择没有匹配项的所有内容。

回答by Matt

OK, just to be different, how about something like this:

好的，只是为了与众不同，这样的事情怎么样：

select apps.isavailable
from dbo.Application apps left outer join dbo.Holidays hol
    on apps.applicationid = hol.applicationid
    and convert(varchar(10),getdate(),101) = convert(varchar(10),hol.holidaydate,101)
where apps.applicationname = @appname
    and hol.applicationid is null

Basically, you're joining the tables based on applicationid and the current date. Since it's a left join, you'll always get all the applications that match @appname, then you just filter out any results that get a match based on the holiday date being the current date. Assuming that applicationname is unique, you'll always get a single row where the right half of the join is null, unless the current date matches a holiday, in which case the query will return no results.

基本上，您是根据 applicationid 和当前日期加入表。由于它是左连接，您将始终获得与@appname 匹配的所有应用程序，然后您只需根据当前日期的假期日期过滤掉任何获得匹配的结果。假设 applicationname 是唯一的，除非当前日期与假期匹配，否则您将始终得到一行，其中联接的右半部分为空，在这种情况下，查询将不返回任何结果。

I don't know how it stacks up with the other solutions performance-wise; I believe joins are generally supposed to be faster than sub-queries, but that probably depends on a variety of factors, so YMMV.

我不知道它在性能方面如何与其他解决方案叠加；我相信连接通常应该比子查询更快，但这可能取决于多种因素，所以 YMMV。

回答by Matt Hamilton

You can use "WHERE NOT EXISTS":

您可以使用“不存在的地方”：

SELECT *
FROM Applications a
WHERE NOT EXISTS (
    SELECT * 
    FROM Holidays h
    WHERE h.ApplicationID = a.ApplicationID
        AND HolidayDate = cast(cast(getdate() as int) as datetime)
)

I'm doing the cast there to truncate the getdate() call back to just the date. Haven't tested that exact query but I think it'll do the job for you.

我正在那里进行转换以将 getdate() 回调截断为日期。还没有测试那个确切的查询，但我认为它会为你做这项工作。

回答by marc_s

Do something like this:

做这样的事情：

SELECT (fields) FROM Application
WHERE NOT EXISTS
  (SELECT * FROM Holidays
   WHERE ApplicationID = Application.ApplicationID
   AND DAY(getdate()) = DAY(holidaydate) 
   AND MONTH(getdate()) = MONTH(holidaydate)
   AND YEAR(getdate()) = YEAR(holidaydate)
  )

Of course it would be a whole lot easier and faster with SQL Server 2008's "DATE" datatype, or if you could store day, month, year in "Holidays" separately.

当然，如果使用 SQL Server 2008 的“DATE”数据类型，或者如果您可以分别在“Holidays”中存储日、月、年，它会更容易和更快。

Marc

马克

回答by Chase Seibert

SELECT a.isAvailable
  FROM Application a
 WHERE NOT EXISTS (
    SELECT TOP 1 0
      FROM Holidays b
     WHERE a.applicationid = b.applicationid
       AND holidaydate = $today
 )

回答by alk

What about this one:

这个如何：

SELECT 
  Application.isavailable
FROM 
   Holidays 
RIGHT JOIN 
   Application
ON 
   Holidays.applicationid = Application.applicationid
WHERE 
  ((Application.applicationname='app1') AND 
  (((Holidays.holidaydate=CurrentDate()) AND (Holidays.applicationid Is Null)) OR 
  (holidays.holidaydate Is Null)));

"'app1'" should be replaced by a variable specifier and "CurrentDate()" by the system specific function to return the current date.

“'app1'”应由变量说明符和“CurrentDate()”替换为系统特定函数以返回当前日期。

Cheers

干杯

/a

/一种