高人啊，来分析一下这个sqL语句怎么错呢？

[aliketen]

SELECT Top 20 a.ID as ID,b.Name as Name FROM [Uk_BBSMain] a,[Uk_BBSClass] b WHERE (a.Reply_DateTime<(SELECT MIN(a.Reply_DateTime) FROM (SELECT TOP 20 a.Reply_DateTime FROM [BBSMain] a,[BBSClass] b Where CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID ORDER BY a.Reply_DateTime desc) AS T)) And CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID ORDER BY a.Reply_DateTime desc


出现这样错误,这个是用来做分页时提取需要的数据，但是两表关联就是出错，一张时还可以

聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

[aliketen]

哦是这样
SELECT Top 20 a.ID as ID,b.Name as Name FROM [UBBSMain] a,[BBSClass] b WHERE (a.Reply_DateTime<(SELECT MIN(a.Reply_DateTime) FROM (SELECT TOP 20 a.Reply_DateTime FROM [BBSMain] a,[BBSClass] b Where CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID ORDER BY a.Reply_DateTime desc) AS T)) And CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID ORDER BY a.Reply_DateTime desc


聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

[aliketen]

诶，晕这个
SELECT Top 20 a.ID as ID,b.Name as Name FROM [BBSMain] a,[BBSClass] b WHERE (a.Reply_DateTime<(SELECT MIN(a.Reply_DateTime) FROM (SELECT TOP 20 a.Reply_DateTime FROM [BBSMain] a,[BBSClass] b Where CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID ORDER BY a.Reply_DateTime desc) AS T)) And CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID ORDER BY a.Reply_DateTime desc

[九斤半]

写成这样好像好看点，还没有改~~~

SELECT Top 20
a.ID as ID,
b.Name as Name
FROM [BBSMain] a,[BBSClass] b WHERE
(

a.Reply_DateTime<
(
SELECT MIN(a.Reply_DateTime) FROM
(
SELECT TOP 20 a.Reply_DateTime
FROM [BBSMain] a,[BBSClass] b
Where CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID
ORDER BY a.Reply_DateTime desc
) AS T
)

) And CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID
ORDER BY a.Reply_DateTime desc

[九斤半]

没看出什么问题，不过中间那个子查询跟表[BBSClass]没有关系，可以去掉~~~

SELECT Top 20
a.ID as ID,
b.Name as Name
FROM [BBSMain] a,[BBSClass] b WHERE
(

a.Reply_DateTime<
(
SELECT MIN(a.Reply_DateTime) FROM
(
SELECT TOP 20 a.Reply_DateTime
FROM [BBSMain] a
Where CharIndex('1002','|'+a.AttendID+'|')<>0
ORDER BY a.Reply_DateTime desc
) AS T
)

)
And CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID
ORDER BY a.Reply_DateTime desc

[aliketen]

服务器: 消息 147，级别 16，状态 2，行 1
聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

[aliketen]

SELECT Top 20
a.ID as ID,
b.Name as Name
FROM [BBSMain] a,[BBSClass] b WHERE
(

a.Reply_DateTime<
(
SELECT MIN(Reply_DateTime) FROM
(
SELECT TOP 20 Reply_DateTime
FROM [BBSMain]
Where CharIndex('1002','|'+AttendID+'|')<>0
ORDER BY Reply_DateTime desc
) AS T
)

)
And CharIndex('1002','|'+a.AttendID+'|')<>0 and a.Class_ID=b.ID
ORDER BY a.Reply_DateTime desc

就可以，子查询为什么不能做关联呢？