我對SQL相當陌生，我正在研究一個問題來學習它，但我有點卡住了。
任務是這樣的。在一個CRM系統中有兩個表，"Contacts"（ContactID、Name、Create）和 "ContactActivities"（ContactID、Activity、ActivityDate）。每當一個聯系人在CRM中被修改時，一個新的活動就會被添加到ContactActivities表中，其中有該聯系人的ContactID和一個描述該活動的字串Activity。
創建一個查詢，表明每個聯系人的某項活動是否已經發生（1）或沒有（0）。該活動應該在創建聯系人后的某個時間段內發生（需要2周）。
這是我想出的辦法（通過使用 SQLFiddle 這里進行檢查，這似乎是可行的）： 現在我想知道（實際上我很確定），這可以通過對 我很高興得到任何幫助！ uj5u.com熱心網友回復： 像這樣，使用連接和聚合： uj5u.com熱心網友回復： 對于條件 SQL Fiddle here
標籤：
(SELECT SIGN(COUNT(*)
FROM ContactActivities AS c2
WHERE c2.Activity = 'opt-in'
AND c2.ContactID = c1.ContactID
AND (SELECT MIN（c2.ActivityDate） - c1.CreationDate) <14
) AS OPT_IN,
(SELECT SIGN(COUNT(*)
FROM ContactActivities AS c2
WHERE c2.Activity = 'purchase'
AND c2.ContactID = c1.ContactID
AND (SELECT MIN（c2.ActivityDate） - c1.CreationDate) <14
) as PURCHASE,
(SELECT SIGN(COUNT(*)
FROM ContactActivities AS c2
WHERE c2.Activity = 'delete'
AND c2.ContactID = c1.ContactID
AND (SELECT MIN（c2.ActivityDate） - c1.CreationDate) <14
) AS DELETED
FROM Contacts as c1
WHERE陳述句進行更好的嵌套來實作 - 但我真的不知道如何實作。
SELECT c1.ContactID
, MAX(CASE WHEN c2. 活動 = 'opt-in' THEN 1 ELSE 0 END) AS OPT_IN
, MAX(CASE WHEN c2. 活動 = 'purchase THEN 1 ELSE 0 END) as PURCHASE
, MAX(CASE WHEN c2. 活動 = 'delete' THEN 1 ELSE 0 END) AS DELETED
FROM Contacts AS c1
LEFT JOIN ContactActivities AS c2
ON c2.ContactID = c1.ContactID
AND c2.ActivityDate - c1.CreationDate </span> 14
GROUP BY c1.ContactID
;
case when then else end，將檢查一個正的sum，通過 contactid：select c.contactID
, sum(case when ACTIVITY='opt-in' and datediff(ACTIVITYDATE, CREATIONDATE）< =14 then 1 else 0 end) > 0 as OPT_IN
, sum(case when ACTIVITY='purchase' and datediff（ACTIVITYDATE, CREATIONDATE）< =14 then 1 else 0 end) > 0 as PURCHASE
, sum(case when ACTIVITY='delete' and datediff（ACTIVITYDATE, CREATIONDATE）< =14 then 1 else 0 end) > 0 as DELETED
from contacts c
left join contactActivities a
on a.contactID = c.contactID
group by c.contactID
;