sql - 如果第一个查询运行不适合参数，您如何获得 3 个联合查询以忽略第一个查询

Question

我有一个表格，其中包含 3 组日期，以及我希望过滤的日期旁边的 3 个类别，以查找上个月内的日期和类别中的特定单词。如果我运行联合查询并且第一个查询返回第一个日期超出参数，我希望它在联合查询序列中运行第二个查询以检查第二个日期列。如何在我的 sql 视图中对此进行编码。目前我有这个编码如下。

SELECT table1. I'd
      ,table1.[Date Of Offence]
      ,table. [Offence Category]
      ,table1.[Offence Description]
      ,table1.[Action Taken]
FROM table1
WHERE table1.[Date Of Offence] Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()), Month(Date()), 0)
AND table1.[Offence Category] = "Speeding"
OR table1.[Date Of Offence] Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()), Month(Date()), 0)
AND (table1.[Offence Category] = "Speeding" IS NULL

UNION ALL

SELECT table1.ID
      ,table1[Date Of Offence 2]
      ,table1.[Offence Category 2]
      ,table1.[Offence Description 2]
      ,table1.[Action Taken 2]
FROM table1
WHERE table1.[Date Of Offence]) Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()), Month(Date()), 0)
AND table1.[Offence Category] = "Speeding"
OR table1.[Date Of Offence] Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()),Month(Date()),0))
AND table1.[Offence Category]) = "Speeding" IS NULL

UNION

Into the 3rd query

标签： sqlms-access

Answer 1

我猜你需要这个——

SELECT TOP 1 table1. Id
            ,table1.[Date Of Offence]
            ,table1. [Offence Category]
            ,table1.[Offence Description]
            ,table1.[Action Taken]
FROM table1
WHERE table1.[Date Of Offence] Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()), Month(Date()), 0)
AND table1.[Offence Category] = "Speeding"
OR table1.[Date Of Offence] Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()), Month(Date()), 0)
AND (table1.[Offence Category] = "Speeding" IS NULL

UNION ALL

SELECT table1.ID
      ,table1[Date Of Offence 2]
      ,table1.[Offence Category 2]
      ,table1.[Offence Description 2]
      ,table1.[Action Taken 2]
FROM table1
WHERE table1.[Date Of Offence]) Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()), Month(Date()), 0)
AND table1.[Offence Category] = "Speeding"
OR table1.[Date Of Offence] Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()),Month(Date()),0))
AND table1.[Offence Category]) = "Speeding" IS NULL
ORDER BY table1.[Date Of Offence]