更改查询以返回不匹配的行

发布于 2024-10-21 21:09:10 字数 928 浏览 1 评论 0原文

SELECT
  T.clinic_code, C.dt, T.schedule_time, T.section_name,
  T.section_content, CS.schedule_event_source_id
FROM Master_Templates T
  INNER JOIN Calendar C
    ON  T.dw = C.dw
    AND T.mo = (C.D - 1) / 7 + 1
    AND C.Y = '2014'
    AND C.M = '3'
    AND T.clinic_code = 'ABC'
  LEFT OUTER JOIN Clinic_Schedule CS
    ON  CS.schedule_date=convert(varchar, C.dt, 121)
    AND CS.clinic_code=T.clinic_code
    AND CS.schedule_time=T.schedule_time
    AND CS.section_name = T.section_name
  WHERE CS.schedule_event_source_id = 2

Clinic_Schedule 包含 1 条记录：

诊所代码 |时间表_日期|日程安排时间 |部分名称 | ABC | Schedule_event_source_id 2014-03-03 |上午 |师资 | 2

Master_Template包含168条记录：

诊所代码 | dw |莫|日程安排时间 |部分名称 |部分内容

日历包含将 Master_Template 记录与星期几连接起来的列。

如何更改上面的查询以返回 167 行？即master_template 中的总行数减去clinic_schedule 中的行数。

原文

SELECT
  T.clinic_code, C.dt, T.schedule_time, T.section_name,
  T.section_content, CS.schedule_event_source_id
FROM Master_Templates T
  INNER JOIN Calendar C
    ON  T.dw = C.dw
    AND T.mo = (C.D - 1) / 7 + 1
    AND C.Y = '2014'
    AND C.M = '3'
    AND T.clinic_code = 'ABC'
  LEFT OUTER JOIN Clinic_Schedule CS
    ON  CS.schedule_date=convert(varchar, C.dt, 121)
    AND CS.clinic_code=T.clinic_code
    AND CS.schedule_time=T.schedule_time
    AND CS.section_name = T.section_name
  WHERE CS.schedule_event_source_id = 2

Clinic_Schedule contains 1 record:

clinic_code | schedule_date | schedule_time | section_name | schedule_event_source_id ABC | 2014-03-03 | AM | Faculty | 2

Master_Template contains 168 records:

clinic_code | dw | mo | schedule_time | section_name | section_content

Calendar contains columns to join the Master_Template records with days of the week.

How can I change the query above to return 167 rows? I.e. the total rows in master_template, minus the row in clinic_schedule.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

惜醉颜 2024-10-28 21:09:11

这个怎么样：

SELECT
  T.clinic_code, C.dt, T.schedule_time, T.section_name,
  T.section_content, CS.schedule_event_source_id
FROM Master_Templates T
  INNER JOIN Calendar C
    ON  T.dw = C.dw
    AND T.mo = (C.D - 1) / 7 + 1
    AND C.Y = '2014'
    AND C.M = '3'
    AND T.clinic_code = 'ABC'
  LEFT OUTER JOIN Clinic_Schedule CS
    ON  CS.schedule_date=convert(varchar, C.dt, 121)
    AND CS.clinic_code=T.clinic_code
    AND CS.schedule_time=T.schedule_time
    AND CS.section_name = T.section_name
    AND CS.schedule_event_source_id = 2
  WHERE CS.clinic_code IS NULL

？

What about this:

SELECT
  T.clinic_code, C.dt, T.schedule_time, T.section_name,
  T.section_content, CS.schedule_event_source_id
FROM Master_Templates T
  INNER JOIN Calendar C
    ON  T.dw = C.dw
    AND T.mo = (C.D - 1) / 7 + 1
    AND C.Y = '2014'
    AND C.M = '3'
    AND T.clinic_code = 'ABC'
  LEFT OUTER JOIN Clinic_Schedule CS
    ON  CS.schedule_date=convert(varchar, C.dt, 121)
    AND CS.clinic_code=T.clinic_code
    AND CS.schedule_time=T.schedule_time
    AND CS.section_name = T.section_name
    AND CS.schedule_event_source_id = 2
  WHERE CS.clinic_code IS NULL

回复收藏 0 原文

╰ゝ天使的微笑 2024-10-28 21:09:11

要仅返回 T 中在 CS 中不匹配的记录，可以使用 HAVING，它类似于 WHERE，但将条件应用于 JOIN结果：

SELECT
  T.clinic_code, C.dt, T.schedule_time, T.section_name,
  T.section_content, CS.schedule_event_source_id
FROM Master_Templates T
INNER JOIN Calendar C
    ON  T.dw = C.dw
    AND T.mo = (C.D - 1) / 7 + 1
    AND C.Y = '2014'
    AND C.M = '3'
    AND T.clinic_code = 'ABC'
LEFT OUTER JOIN Clinic_Schedule CS
    ON  CS.schedule_date=convert(varchar, C.dt, 121)
    AND CS.clinic_code=T.clinic_code
    AND CS.schedule_time=T.schedule_time
    AND CS.section_name = T.section_name
    AND CS.schedule_event_source_id = 2
HAVING CS.clinic_code IS NULL

To return only the records from T that did NOT have a match in CS, you can use HAVING, which is similar to WHERE but applies the condition on the JOIN's results:

SELECT
  T.clinic_code, C.dt, T.schedule_time, T.section_name,
  T.section_content, CS.schedule_event_source_id
FROM Master_Templates T
INNER JOIN Calendar C
    ON  T.dw = C.dw
    AND T.mo = (C.D - 1) / 7 + 1
    AND C.Y = '2014'
    AND C.M = '3'
    AND T.clinic_code = 'ABC'
LEFT OUTER JOIN Clinic_Schedule CS
    ON  CS.schedule_date=convert(varchar, C.dt, 121)
    AND CS.clinic_code=T.clinic_code
    AND CS.schedule_time=T.schedule_time
    AND CS.section_name = T.section_name
    AND CS.schedule_event_source_id = 2
HAVING CS.clinic_code IS NULL

回复收藏 0 原文

~没有更多了~