两张表 一张是Message 一张是 Reply 第一张表里有 PublishedDatetime第二张表里有 ReplyDatetimeReplyID 是外键
(select PublishedDateTime from [Message] where ConditionID = 2)
找出了已经回复了的Message 的发表时间(select ReplyDatetime from Reply INNER JOIN [Message] ON [Message].ReplyID = Reply.ReplyID找出了 Message回复的回复时间用datediff() 求出时间差,
但提示 :
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。SELECT avg(
datediff(dd,(select PublishedDateTime from [Message] where ConditionID = 2),
(select ReplyDatetime from Reply INNER JOIN [Message] ON [Message].ReplyID = Reply.ReplyID)))请问一下要怎么改?
(select PublishedDateTime from [Message] where ConditionID = 2)
找出了已经回复了的Message 的发表时间(select ReplyDatetime from Reply INNER JOIN [Message] ON [Message].ReplyID = Reply.ReplyID找出了 Message回复的回复时间用datediff() 求出时间差,
但提示 :
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。SELECT avg(
datediff(dd,(select PublishedDateTime from [Message] where ConditionID = 2),
(select ReplyDatetime from Reply INNER JOIN [Message] ON [Message].ReplyID = Reply.ReplyID)))请问一下要怎么改?
select avg(datediff(dd,publishedatetime,lReplyDatetime)) from Reply INNER JOIN [Message] ON [Message].ReplyID = Reply.ReplyID
where conditionid=2
from Message,Reply
where ConditionID = 2
and Message.ReplyID = Reply.ReplyID
create table Message (ReplyID int,PublishedDateTime datetime)
create table Reply (ReplyID int,ReplyDatetime datetime)
insert Message select 1 ,'2010-1-1'
insert Reply select 1,'2010-2-1'union select 1,'2010-3-1'select avg(datediff(dd,PublishedDateTime,ReplyDatetime)) from Reply INNER JOIN [Message] ON [Message].ReplyID = Reply.ReplyID/*
-----------
45(所影响的行数为 1 行)
*/
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281