sql语法组由
挣扎与此,因为我不擅长与SQL和设计器不会与OVER使用工作。基本上,如果用户正在关注相关标签,则会获取主题列表。sql语法组由
我需要按T.TopicId
分组以阻止重复。如果用户选择与主题相关联的多个标签,它将列出该主题两次(每个标签一次)
当我在sql中添加一个组时,我得到了多个错误,并且我尝试了重新排列事物并且不能得到它的工作,如所说我用SQL语句无用
@id int = null
AS
SELECT
*
FROM
(SELECT
ROW_NUMBER()
OVER
(ORDER BY TopicOrder desc
,
(CASE
WHEN M.MessageCreationDate > T.TopicCreationDate THEN M.MessageCreationDate
ELSE T.TopicCreationDate
END) desc)
AS RowNumber
,T.TopicId, T.TopicTitle, T.TopicShortName, T.TopicDescription, T.TopicCreationDate, T.TopicViews, T.TopicReplies, T.UserId, T.TopicTags, T.TopicIsClose,
T.TopicOrder, T.LastMessageId, T.UserName, M.MessageCreationDate, M.UserId AS MessageUserId, MU.UserName AS MessageUserName, U.UserGroupId,
U.UserPhoto, T.UserFullName
FROM Tags INNER JOIN
TopicsComplete AS T ON T.TopicId = Tags.TopicId LEFT OUTER JOIN
Messages AS M ON M.TopicId = T.TopicId AND M.MessageId = T.LastMessageId AND M.Active = 1 LEFT OUTER JOIN
Users AS MU ON MU.UserId = M.UserId LEFT OUTER JOIN
Users AS U ON U.UserId = T.UserId LEFT OUTER JOIN
tagfollows AS TF ON @id = TF.userid
WHERE (Tags.Tag = TF.tag)
)T
如果有人能帮助它将不胜感激,谢谢! :)
我想你只需要转换的加盟tagfollows
到EXISTS
子查询(和删除冗余的嵌套):
SELECT
ROW_NUMBER()
OVER (ORDER BY TopicOrder desc
, CASE WHEN M.MessageCreationDate > T.TopicCreationDate
THEN M.MessageCreationDate
ELSE T.TopicCreationDate
END desc)
AS RowNumber,
T.TopicId, T.TopicTitle, T.TopicShortName, T.TopicDescription,
T.TopicCreationDate, T.TopicViews, T.TopicReplies, T.UserId,
T.TopicTags, T.TopicIsClose, T.TopicOrder, T.LastMessageId,
T.UserName, M.MessageCreationDate,
M.UserId AS MessageUserId,
MU.UserName AS MessageUserName,
U.UserGroupId, U.UserPhoto, T.UserFullName
FROM
TopicsComplete AS T
LEFT OUTER JOIN
Messages AS M ON M.TopicId = T.TopicId
AND M.MessageId = T.LastMessageId
AND M.Active = 1
LEFT OUTER JOIN
Users AS MU ON MU.UserId = M.UserId
LEFT OUTER JOIN
Users AS U ON U.UserId = T.UserId
WHERE EXISTS
(SELECT *
FROM Tags
INNER JOIN tagfollows AS TF
ON Tags.Tag = TF.tag
WHERE T.TopicId = Tags.TopicId
AND @id = TF.userid
) ;
嘿ypercube,谢谢你尝试,但这返回相同的数据。问题是如果一个用户使用2个标签,那么tagfollows将会是例如1个样本,1个样本2(1个是用户标识)。如果一个主题同时具有标签sample和sample2,它将返回两个标签的结果。如果它是由TopicId组完成的,那么它只会分组任何重复。不知道是否存在适用,因为这两个标签将作为一个单独的标签存在Tags.Tag – mxadam 2013-03-23 16:30:13
我已经做了一个更正。 – 2013-03-23 16:30:17
这个作品完美,谢谢ypercube你的明星! – mxadam 2013-03-23 16:51:26
你说你想在用户所关注的集合中显示带有标签的帖子,但是当你的帖子有多个匹配的标签时,你不希望帖子多次出现。这是EXISTS
subquery的完美使用。这是来自MSDN页面的一个例子。
SELECT a.FirstName, a.LastName
FROM Person.Person AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.BusinessEntityID = b.BusinessEntityID
AND a.LastName = 'Johnson');
你在人表(如您的职位表)很感兴趣,但你想显示在员工至少一个匹配记录(如您的标签表)的记录。
看起来对你说你想要的方式太复杂了。顺便说一句,这是SQL服务器? – 2013-03-23 15:45:27
是的,这段代码工作正常。我只是想把这个小组放在哪里,但是然后它抱怨说所有的T.'s在一个聚合函数或小组中出现了 – mxadam 2013-03-23 15:51:45
您可以将连接转换为'tagfollows'到一个'EXISTS'子查询中。 – 2013-03-23 16:02:37