根據時間段查找一列不同的重復行

我有一張客戶表，上面列出了我們何時發送了他們的到期發票以及他們是否付款。如果客戶確實支付了發票，則為“1”，否則為“0”。我想找到客戶在一年內付款的所有記錄，而不是下一年。因此，例如，選擇在 1/1/2020 和 12/30/2020 之間付款但在 1/1/2021 和 12/30/2021 之間未付款的所有客戶（訂單 = 2020 年的 1 但訂單 = 2021 年的 0 ) 基于網站。

命令

WEBSITE      PAID       SENTDATE
one.com        1        1/1/2020
two.com        1        1/5/2020
one.com        0        1/1/2021
four.com       1        2/1/2021
two.com        1        1/5/2021
five.com       1        10/15/2021

從這個串列中，只有“one.com”會被選中，因為two.com 兩年支付，四和五只在一年內有一次交易。

如果我必須進行內部連接，然后哪里不存在，我無法解決如何做到這一點？通常我可以弄清楚這一點，但我很難過。

uj5u.com熱心網友回復：

如果您只想知道符合條件的網站，這會奏效。您可能會在 senddate 上執行 YEAR() 函式，但隨后您必須執行表掃描，而不是在 SentDate 上使用可能的索引。

SELECT Website FROM myTable WHERE Paid=1 AND SentDate BETWEEN '2020-01-01' AND '2020-12-31' AND Website IN (SELECT Website FROM myTable WHERE Paid=0 AND SentDate BETWEEN '2021-01-01' AND '2021-12-31')

如果您想知道發票的發送日期，您可以進行內部聯接

SELECT t1.Website, t1.SentDate AS PaidOn, t2.SentDate AS NotPaidOn
FROM myTable t1
INNER JOIN myTable t2 ON t1.Website = t2.Website
WHERE t1.Paid = 1 AND t1.SentDate BETWEEN '2020-01-01' AND '2020-12-31'
AND t2.Paid = 0 AND t2.SentDate '2021-01-01' AND '2021-12-31'

uj5u.com熱心網友回復：

EXCEPT 運算子是實作此目的的一種方法，還有一點 if 日期（年）移位：

DECLARE @Orders TABLE (Website VARCHAR(50), Paid BIT, SentDate DATETIME)
INSERT @Orders
VALUES
    ('one.com', 1, '1/1/2020'),
    ('two.com', 1, '1/5/2020'),
    ('one.com', 0, '1/1/2021'),
    ('four.com', 1, '2/1/2021'),
    ('two.com', 1, '1/5/2021')

SELECT DISTINCT Year = YEAR(SentDate), WebSite FROM @Orders WHERE Paid = 1
    AND YEAR(SentDate) < (SELECT YEAR(MAX(SentDate)) FROM @Orders)
EXCEPT
SELECT DISTINCT Year = YEAR(SentDate) - 1, WebSite FROM @Orders WHERE Paid = 1

它幾乎是這樣寫的：向我展示在 Y 年付款的每個人，但排除（“減去”）在下一年付款的任何人。由于最近一年付款的每個人都沒有機會在未來一年付款，因此我添加了一個額外的條件來排除這些情況。

*** 編輯 ***

重讀后，我意識到我錯過了明確的未付款條件。在這種情況下，類似于 LeeG 提出的邏輯就可以解決問題。

SELECT Year = YEAR(O1.SentDate), O1.WebSite
FROM @Orders O1
JOIN @Orders O2
    ON O2.Website = O1.Website
    AND O2.Paid = 0
    AND YEAR(O2.SentDate) = YEAR(O1.SentDate)   1
WHERE O1.Paid = 1
ORDER BY YEAR(O1.SentDate) DESC, O1.WebSite

uj5u.com熱心網友回復：

您也可以使用 LEAD 函式，可能像這樣（它處理在一年內付款但在開具發票的下一年未能付款的網站，即使那不是連續一年。

WITH TwoYearView AS (
  SELECT
    WebSite,
    YEAR(SentDate) as Year,
    LEAD(YEAR(SentDate),1) OVER (PARTITION BY Website ORDER BY SentDate) as NextInvoicedYear,
    Paid,
    LEAD(Paid, 1) OVER (PARTITION BY Website ORDER BY SentDate) AS PaidNextYear
  FROM Orders
)
SELECT 
  WebSite,
  Year AS YearPaid,
  NextInvoicedYear 
FROM TwoYearView
WHERE Paid = 1 AND PaidNextYear = 0

uj5u.com熱心網友回復：

我的樣本資料：

one.com     1   2020-01-01
two.com     1   2020-01-05
one.com     0   2021-01-01
four.com    1   2021-02-01
two.com     1   2021-01-05
two.com     0   2019-01-01
five.com    1   2021-10-15
five.com    0   2021-05-26

并查詢：

select 
    cus_paid.website, 
    cus_paid.senddate as paid_date, 
    cus_nopaid.senddate as nopaid_date, 
    cus_nopaid.senddate - cus_paid.senddate as date_period 
from examples.customer as cus_paid
inner join examples.customer as cus_nopaid on 
                cus_paid.website = cus_nopaid.website and 
                cus_nopaid.paid = 0
where 
    cus_paid.paid = 1 and 
    cus_nopaid.senddate - cus_paid.senddate <= 366

并且您可以將此cus_nopaid.senddate - cus_paid.senddate <= 366條件更改為僅使用年份比較格式并從日期中提取年份。

標籤：sql sql-server 查询语句

上一篇：連續52周高點

下一篇：如何僅將唯一記錄從另一個表匯入到一個表中？