我正在从两个表中获取用户的聊天消息,因此inner join
对此使用了。这是我的SQL查询:
SELECT
m.Message_Id
, m.Chat_Relation_Id AS relation_id
, m.SenderId AS datetime
, m.Text_Message
, m.Subject
, m.DateTime
FROM
[AppResource].[dbo].[ChatMessage] AS m
INNER JOIN [AppResource].[dbo].[chatRelation] AS r
ON r.Chat_Relation_Id = m.Chat_Relation_Id
WHERE
r.User1Id=1
GROUP BY
m.Chat_Relation_Id
ORDER BY
m.DateTime DESC
但是,当我运行此程序时,出现以下错误消息:
“ AppResource.dbo.ChatMessage.Message_Id”列在选择列表中无效,因为它既不包含在聚合函数中,也不包含在GROUP BY子句中。
我在Google上进行了很多搜索,发现了很多解决方案,但没有任何效果。Text_Message
是text
数据类型,DateTime
是Datetime
数据类型。
请帮我。
Message_Id relation_id datetime Text_Message Subject DateTime
4 1 2 Hello Hi 2014-11-25 00:00:00.000
8 25 1 hiii ho 2014-03-26 09:07:27.000
7 26 1 hiiiiii 2014-03-26 07:07:27.000
6 25 1 hi 2014-03-26 07:01:58.000
使用GROUP BY
时,通常将具有聚合函数,而您提供的SQL实际上没有任何聚合函数,因此无需对其进行分组。
SELECT
m.Message_Id
, m.Chat_Relation_Id AS relation_id
, m.SenderId AS datetime
, m.Text_Message
, m.Subject
, m.DateTime
FROM
[AppResource].[dbo].[ChatMessage] AS m
INNER JOIN [AppResource].[dbo].[chatRelation] AS r
ON r.Chat_Relation_Id = m.Chat_Relation_Id
WHERE
r.User1Id=1
GROUP BY
m.Chat_Relation_Id
ORDER BY
m.DateTime desc
之所以引发该错误,是因为必须在GROUP BY
子句中定义所有非聚合列,因为这定义了其他(聚合)列的计算方式。在您的情况下,鉴于所描述的问题,我猜想只需删除即可GROUP BY
解决您的问题。
SELECT
m.Message_Id
, m.Chat_Relation_Id AS relation_id
, m.SenderId AS datetime
, m.Text_Message
, m.Subject
, m.DateTime
FROM
[AppResource].[dbo].[ChatMessage] AS m
INNER JOIN [AppResource].[dbo].[chatRelation] AS r
ON r.Chat_Relation_Id = m.Chat_Relation_Id
WHERE
r.User1Id = 1
ORDER BY
m.DateTime DESC
在有关重复条目的注释后进行编辑:
以下查询将获取每个查询的最新条目 Chat_Relation_Id
SELECT
m.Chat_Relation_Id AS relation_id
, MAX(m.DateTime) AS maxDateTime
FROM
[AppResource].[dbo].[ChatMessage] AS m
INNER JOIN [AppResource].[dbo].[chatRelation] AS r
ON r.Chat_Relation_Id = m.Chat_Relation_Id
WHERE
r.User1Id = 1
GROUP BY
m.Chat_Relation_Id
这远非理想,但可以用作子查询来选择相关文本,如下所示:
SELECT
m.Message_Id
, m.Chat_Relation_Id AS relation_id
, m.SenderId AS datetime
, m.Text_Message
, m.Subject
, m.DateTime
FROM
(SELECT
subqueryMessage.Chat_Relation_Id AS relation_id
, MAX(subqueryMessage.DateTime) AS maxDateTime
FROM
[AppResource].[dbo].[ChatMessage] AS subqueryMessage
INNER JOIN [AppResource].[dbo].[chatRelation] AS subQueryRelation
ON subQueryRelation.Chat_Relation_Id = subqueryMessage.Chat_Relation_Id
WHERE
subQueryRelation.User1Id = 1
GROUP BY
subqueryMessage.Chat_Relation_Id
) AS mySubQuery
INNER JOIN [AppResource].[dbo].[ChatMessage] AS m
ON m.Chat_Relation_Id = mySubQuery.relation_id
AND m.DateTime = mySubQuery.maxDateTime
ORDER BY
m.DateTime DESC
我不喜欢使用子查询这样的,但我是一个开发商不是DBA,和上面的是有限的,例如,如果有项目具有相同Chat_Relation_Id
和DateTime
再重复依然会发生。使用HAVING
条款可能是一种聪明的方法,但希望它不能给您带来一些帮助,从而可以帮助其他人解决您的问题。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句