(select MemberID,Count(*) num from Replies group by Replies.MemberID)
union
(select MemberID,Count(*) num from Topics group by Topics.MemberID)
通过上面的语句取得下面的表
MemberID num
1 3
1 2
2 1
我要得到下面的表
MemberID num
1 5
2 1
我要怎么办??
...全文
4414打赏收藏
在下面的情况下sum要如何用?
(select MemberID,Count(*) num from Replies group by Replies.MemberID) union (select MemberID,Count(*) num from Topics group by Topics.MemberID) 通过上面的语句取得下面的表 MemberID num 1 3 1 2 2 1 我要得到下面的表 MemberID num 1 5 2 1 我要怎么办??
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)) a
group by a.MemberID
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 Replies.MemberID = T.MemberID ) R
WHERE Forums_Replies.ReplyID = R.ReplyID) AS Num
SELECT MemberID, SUM(num) FROM (
(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
yujohny(踏网无痕) :
在用你给的
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 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 Replies.ReplyID = R.ReplyID) AS Num
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 tmp group by MemberID
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 Replies.MemberID = T.MemberID ) R
WHERE Forums_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
(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
就报错,什么地方错了呢?
CREATE table aa (a varchar(10) ,b int)
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 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
select MemberID,sum(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
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