买一张没有周末和假期的桌子。MYSQL

Question

我已经写了两张桌子了。第一个是time_dimension的所有日期，第二个是今年的公共假日。然而，我想让这一天没有周末和假期。我写的查询就像；

SELECT ti.id, ti.db_date, ti.year, ti.month, ti.day, ti.quarter, ti.week,   ti.day_name, ti.month_name
FROM time_dimension ti
LEFT JOIN  tbl_holidays tb on ti.db_date=tb.startdate
where  day_name in ('Monday','Tuesday','Wednesday','Thursday','Friday');

有人能帮我摆脱这个查询中的假日表吗？

更新答案：

SELECT ti.id, ti.db_date, ti.year, ti.month, ti.day, ti.quarter, ti.week, ti.day_name, ti.month_name
FROM DeJuMa.time_dimension ti
LEFT JOIN  tbl_holidays tb on ti.db_date=tb.startdate
where  day_name in ('Monday','Tuesday','Wednesday','Thursday','Friday')
AND ti.db_date NOT IN (select startdate from tbl_holidays tb );

Answer 1

您可以使用NOT EXISTS来排除假日：

SELECT *
FROM time_dimension ti
WHERE
    NOT EXISTS(SELECT 1 FROM tbl_holidays WHERE startdate = ti.db_date)
    AND day_name IN ('Monday','Tuesday','Wednesday','Thursday','Friday');

Answer 2

    SELECT ti.id, ti.db_date, ti.year, ti.month, ti.day, ti.quarter, ti.week, ti.day_name, ti.month_name
        FROM time_dimension ti WHERE day_name NOT IN ('Saturday','Sunday')
        AND ti.db_date NOT IN (SELECT startdate FROM tbl_holidays);