复杂的SQL查询(需要建议)
有没有人能告诉我如何解决以下问题：
我的数据库里有图片、视频和标签。标签可以关联到图片和视频。我必须查询7天前或更早修改的标签ID和计数的数据库，并按他们对图片和视频资产的关联计数对它们进行排序。
所以我的想法是，在最后，我可以输出在过去7天内与图片或视频关联最多的标签。我用我拥有的字段写下了DB结构
VIDEO
ID
PICTURE
ID
PICTURE_ATTRMAPPING
CS_OWNERID (ID of picture)
CS_ATTRID ( will store ID of the tag attribute (picture_tag or video_tag))
ASSETVALUE ( will store ID OF THE associated tag)
VIDEO_ATTRMAPPING
CS_OWNERID (ID of video)
CS_ATTRID ( will store ID of the tag attribute (picture_tag or video_tag))
ASSETVALUE ( will store ID OF THE associated tag)
TAG
ID
UPDATEDATE
ATTRIBUTES (picture_tag attribute ID is stored here)
ID
NAME因此，我们可以看到标签、视频、图片和属性存储在单独的表中。如果VIDEO/ picture _ATTRMAPPING表中有CS_ATTRID列的video或picture标签属性id和ASSETVALUE列的标签id的记录，我们可以判断标签被video或picture引用(换句话说，video/picture有标签关联)。
我假设它是使用子查询进行查询，所以我开始将这个任务分解为子任务，并弄清楚如何获得所需的所有信息。
我肯定要获取视频和图片对象的标签属性的ID：
SELECT id FROM ATTRIBUTES WHERE NAME = 'picture_tag' OR NAME = 'video_tag'还有一个如何查询n天前的标记的示例：
SELECT id FROM TAG WHERE updateddate BETWEEN TO_DATE('2013-08-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND CURRENT_DATE这可能并不像看起来那么复杂，但是，我不确定整个查询应该是什么样子，以及从什么开始。有人能抛出一个想法或样本吗？
我使用的是oracle，但也熟悉mysql，所以来自任何DBMS的示例都会很棒。如果我说得够清楚了，请告诉我。
回答 2
Stack Overflow用户
发布于 2013-08-26 19:36:56
最近7天更新的标签可以写成：
select id from tags where updatedate >= sysdate - 7根据您所说的，ATTRIBUTES表中只有两个值；因此您可以忽略它。即使表PICTURE_ATTRMAPPING和VIDEO_ATTRMAPPING有更多的属性，也要确保在连接到它们时强制执行这些属性，所以这也无关紧要。
如果你想要一个标签与图片或视频相关联，你只需要强制它存在于一个*_ARRTMAPPING表中：
select *
from tags t
left outer join picture_attrmappings pa
on t.id = pa.assetvalue
left outer join video_attrmappings va
on t.id = va.assetvalue
where t.updatedate >= sysdate - 7
and ( pa.assetvalue is not null
or va.assetvalue is not null
)然后，您需要修改次数最多的标签ID；因此，您需要按计数排序：
select t.id
from tags t
left outer join picture_attrmappings pa
on t.id = pa.assetvalue
left outer join video_attrmappings va
on t.id = va.assetvalue
where t.updatedate >= sysdate - 7
and ( pa.assetvalue is not null
or va.assetvalue is not null
)
group by t.id
order by count(*) desc稍微不同的是，这是一个相当奇怪的模式(假设您没有遗漏任何内容)。我希望PICTURE_ATTRMAPPINGS是标签和图片之间的连接表。TAGS表应该存储唯一的标签列表，但这似乎不是它所做的。
我怀疑您缺少一些模式，但我看不到使用您提供的图片或视频的方法。
Stack Overflow用户
发布于 2013-08-26 19:44:19
分配给图片的所有标签ids：
SELECT ASSETVALUE
FROM PICTURE_ATTRMAPPING
JOIN ATTRIBUTES
ON ( ATTRIBUTES.ID = PICTURE_ATTRMAPPING.CS_ATTRID
AND ATTRIBUTES.NAME = 'picture_tag'
) 分配给视频的所有标签ids：
SELECT ASSETVALUE
FROM VIDEO_ATTRMAPPING
JOIN ATTRIBUTES
ON ( ATTRIBUTES.ID = PICTURE_ATTRMAPPING.CS_ATTRID
AND ATTRIBUTES.NAME = 'video_tag'
)使用OUTER JOIN统计标记的图片和视频
SELECT
TAG.ID
, SUM(CASE WHEN PT.ASSETVALUE is not NULL THEN 1 ELSE 0 END) as tagged_picture_number
, SUM(CASE WHEN VT.ASSETVALUE is not NULL THEN 1 ELSE 0 END) as tagged_vieos_number
FROM
TAG
LEFT OUTER JOIN
(
SELECT ASSETVALUE
FROM PICTURE_ATTRMAPPING
JOIN ATTRIBUTES
ON ( ATTRIBUTES.ID = PICTURE_ATTRMAPPING.CS_ATTRID AND ATTRIBUTES.NAME = 'picture_tag')
) PT
ON ( PT.ASSETVALUE = TAG.ID)
LEFT OUTER JOIN
(
SELECT ASSETVALUE
FROM VIDEO_ATTRMAPPING
JOIN ATTRIBUTES
ON ( ATTRIBUTES.ID = PICTURE_ATTRMAPPING.CS_ATTRID AND ATTRIBUTES.NAME = 'video_tag')
) VT
ON ( PT.ASSETVALUE = TAG.ID)
WHERE
TAG.UPDATEDATE <= TRUNC(SYSDATE, 'DD') - 7
group by TAG.ID
order by tagged_picture_number + tagged_vieos_number DESC
;https://stackoverflow.com/questions/18442691
复制相似问题
腾讯云开发者
