Select memberID,sum(num) as num from
(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
) temp
group by MemberID
(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
) temp
group by MemberID
((select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)) a group by
memberid
(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
) A
group by MemberID
insert into aa values('1',1)
insert into aa values('1',2)
insert into aa values('1',3)
insert into aa values('2',2)
insert into aa values('2',3)CREATE table bb (a varchar(10) ,b int)
insert into bb values('1',4)
insert into bb values('1',5)
go
--創建視圖
CREATE View v_aabb
as
select a,sum(b) as b from aa group by a
union
select a,sum(b) as b from bb group by a
go
select a,sum(b) as b from v_aabb group by a
(SELECT num
FROM (SELECT Replies.MemberID,Replies.ReplyID,T.num
FROM Replies LEFT JOIN
(select MemberID,Sum(num) AS num from(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)) as A
group by MemberID) T
ON Forums_Replies.MemberID = T.MemberID) R
WHERE Forums_Replies.ReplyID = R.ReplyID) AS Num这一句是上面那一句的扩展,其中R表为
MemberID ReplyID num
1 134 5
1 135 5
2 136 4
可是 (Select num from ()R where Replies.ReplyID=R.ReplyID)AS num
就报错,什么地方错了呢?
select MemberID,sum(num) num
from (
select MemberID,Count(*) num
from Replies
group by Replies.MemberID
union
select MemberID,Count(*) num
from Topics
group by Topics.MemberID)) a
group by MemberID
报的是GROUP BY子句出错
你的连接条件写错了把
你是Replies和t表连接
怎么是ON Forums_Replies.MemberID = T.MemberIDSELECT num
FROM (
SELECT Replies.MemberID,Replies.ReplyID,T.num
FROM Replies LEFT JOIN
(select MemberID,Sum(num) AS num
from(
select MemberID,Count(*) num from Replies group by Replies.MemberID
union
select MemberID,Count(*) num from Topics group by Topics.MemberID) as A
group by MemberID) T
ON Replies.MemberID = T.MemberID ) R
WHERE Forums_Replies.ReplyID = R.ReplyID) AS Num
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
) as tmp group by MemberID
FROM (SELECT Replies.MemberID,Replies.ReplyID,T.num
FROM Replies LEFT JOIN
(select MemberID,Sum(num) AS num from(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)) as A
group by MemberID) T
ON Replies.MemberID = T.MemberID) R
WHERE Replies.ReplyID = R.ReplyID) AS Num
在用你给的
select memberID,sum(num) num from
(
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
) A
group by MemberID语句当
MemberID num
1 3
1 3
2 1会得到下面的表,而这时当MemberID=1时num应该为6而不是为3???
MemberID num
1 3
2 1
(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID))TEMPTABLE
GROUP BY MemberID
FROM (
SELECT Replies.MemberID,Replies.ReplyID,T.num
FROM Replies LEFT JOIN
(select MemberID,Sum(num) AS num
from(
select MemberID,Count(*) num from Replies group by Replies.MemberID
union
select MemberID,Count(*) num from Topics group by Topics.MemberID) as A
group by MemberID) T
ON Replies.MemberID = T.MemberID ) R
WHERE Forums_Replies.ReplyID = R.ReplyID) AS Num
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)) a
group by a.MemberID