我有两张桌子。
录像;ID,艺术家,标题
运行日志;VideoID,‘连接到videos.ID日期时间
我想在视频表上运行一个查询,不包括三种条件下的记录。(举个例子。我有多种情况,但如果我在这些问题上得到一些帮助,我很可能会想出其他的办法。
这得到了我的第一个结果要求,但我觉得它可以写得更好。
SELECT ID,Artist, Title FROM
videos join runlog on runlog.videoID = videos.id
where
(select COUNT(*) from runlog where datetime > DATEADD(hh,-12,GETDATE())
and runlog.videoID = videos.id) = 0
order by Artist, Title第二个要求,我不知道如何在同一个查询中组合起来。
发布于 2014-04-28 05:17:17
这个查询显式地执行后两个约束,第一个是隐式的(第二个强制执行第一个)。这是未经测试的,但它应该会让你走上正确的轨道。
select ID, Artist, Title
from Video
where Artist not in (
select V.Artist
from Video V, Runlog R
where V.ID = R.VideoID
and R.datetime > dateadd(hour, -1, getdate())
union
select V.Artist
from Video V, Runlog R
where V.ID = R.VideoID
and R.datetime > dateadd(hour, -24, getdate())
group by V.Artist
having count(*) > 2
);发布于 2014-04-28 04:11:50
SELECT ID, Artist, Title
FROM videos v join runlog l on l.videoID = v.id
WHERE
-- 1. All in past hour will not show
datetime < DATEADD(hour,-1,GETDATE())
-- 2.
and not exists
( select 1
from videos v2 join runlog l2 on l2.videoID = v2.id
where v2.Artist = v1.Artist and datetime > DATEADD(hour,-1,GETDATE())
)
-- 3.
and not exists
(
select 1
from
(
select count(1) recordCount
from videos v3 join runlog l3 on l3.videoID = v3.id
where v3.Artist = v1.Artist and datetime > DATEADD(hour,-24,GETDATE())
)
where recordCount>=3
)
order by Artist, Title或
SELECT ID, Artist, Title
FROM videos v join runlog l on l.videoID = v.id
WHERE
-- 1. All in past hour will not show
datetime < DATEADD(hour,-1,GETDATE())
and v1.Artist in
(
-- 2.
select Artist
from videos v2 join runlog l2 on l2.videoID = v2.id
where datetime > DATEADD(hour,-1,GETDATE())
union all
-- 3.
select Artist
from videos v3 join runlog l3 on l3.videoID = v3.id
where datetime > DATEADD(hour,-24,GETDATE())
group by v3.Artist having count(1) >= 3
)发布于 2014-04-28 04:42:41
SELECT ID, Artist, Title
FROM Videos V
WHERE V.ARTIST NOT IN
(SELECT V2.Artist
FROM RunLog R
INNER JOIN Videos V2 ON V2.ID = R.VideosID
WHERE dateTime >= DateAdd(hh, -1, GetDate())
UNION ALL
SELECT V3.Artist
FROM RunLog R
INNER JOIN Videos V3 ON V3.ID = R.VideosID
WHERE dateTime >= DateAdd(hh, -24, GetDate())
GROUP BY V3.Artist
HAVING Count(*) >= 3
)
)同样的查询也可以使用NOT EXISTS完成。
SELECT ID, Artist, Title
FROM Videos V
WHERE NOT EXISTS
(SELECT 1
FROM RunLog R
INNER JOIN Videos V2 ON V2.ID = R.VideosID
WHERE dateTime >= DateAdd(hh, -1, GetDate())
AND V2.Artist = V.Artist
UNION ALL
SELECT 1
FROM RunLog R
INNER JOIN Videos V3 ON V3.ID = R.VideosID
WHERE dateTime >= DateAdd(hh, -24, GetDate())
AND V3.Artist = V.Artist
GROUP BY V3.Artist
HAVING Count(*) >= 3
)
)条件2将自动满足条件1。
https://stackoverflow.com/questions/23331963
复制相似问题