我有两个这样的表:
TABLE1 (SENDS)
------------------------------------------------
| ReceiptId | JobId | SK | LogDate |
|-----------|-------|------|---------------------|
| 0000001 | 67789 | 4151 | 6/4/2018 2:31:44 AM |
|-----------|-------|------|---------------------|
| 0000002 | 67789 | 4151 | 6/4/2018 8:25:50 AM |
|-----------|-------|------|---------------------|
| 0000003 | 67789 | 4151 | 6/4/2018 7:42:09 PM |
------------------------------------------------
TABLE2 (CLICKS)
------------------------------------
| JobId | SK | LogDate |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 4:51:23 AM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 5:32:52 AM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 7:12:03 AM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 5:14:37 PM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 6:07:12 PM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 9:46:52 PM |
------------------------------------
我需要执行一个SQL查询来从TABLE1
中提取ReceiptId
,其中(TABLE1.JobId = TABLE2.JobId
& TABLE1.SK = TABLE2.SK
)和TABLE2.LogDate
的记录数介于TABLE1.LogDate
和下一个最高的TABLE1.LogDate
之间。
因此,此示例的表结果为:
RESULT TABLE
-----------------------------------------
| ReceiptId | JobId | SK | Clicks Count |
|-----------|-------|------|--------------|
| 0000001 | 67789 | 4151 | 3 |
|-----------|-------|------|--------------|
| 0000002 | 67789 | 4151 | 2 |
|-----------|-------|------|--------------|
| 0000003 | 67789 | 4151 | 1 |
-----------------------------------------
非常提前感谢您。
发布于 2018-06-07 20:26:57
您似乎想要将clicks
连接到sends
,然后对logdate
进行过滤,并使用GROUP BY
和count()
。
但是你的描述和你想要的样本输出彼此不一致。
因此,下面就得到了你所描述的内容:
SELECT s.receiptid,
s.jobid,
s.sk,
count(*)
FROM sends s
LEFT JOIN clicks c
ON c.jobid = s.jobid
AND c.sk = s.sk
WHERE c.logdate >= s.logdate
AND c.logdate <= (SELECT min(ss.logdate)
FROM sends ss
WHERE ss.logdate > s.logdate
AND ss.jobid = s.jobid
AND ss.sk = s.sk)
GROUP BY s.receiptid,
s.jobid,
s.sk;
下面的代码将获得所需的示例输出:
SELECT s.receiptid,
s.jobid,
s.sk,
count(*)
FROM sends s
LEFT JOIN clicks c
ON c.jobid = s.jobid
AND c.sk = s.sk
WHERE c.logdate >= s.logdate
AND c.logdate <= coalesce((SELECT min(ss.logdate)
FROM sends ss
WHERE ss.logdate > s.logdate
AND ss.jobid = s.jobid
AND ss.sk = s.sk),
c.logdate)
GROUP BY s.receiptid,
s.jobid,
s.sk;
https://stackoverflow.com/questions/50740661
复制相似问题