我是这样写的:
SELECT a.node, SUM(b.n1) AS n1, SUM(b.n2) AS n2, SUM(b.n3) AS n3
FROM dbo.table1 a INNER JOIN
dbo.table2 b ON a.grp1 = b.grp1
GROUP BY a.node
但统计结果不对,我是要得到这样的结果;
aa sum(n1),sum(n2),sum(n3)
bb sum(n1),sum(n2),sum(n3)
cc sum(n1),sum(n2),sum(n3)
大家快帮帮忙!
SELECT a.node, SUM(b.n1) AS n1, SUM(b.n2) AS n2, SUM(b.n3) AS n3
FROM dbo.table1 a INNER JOIN
dbo.table2 b ON a.grp1 = b.grp1
GROUP BY a.node
但统计结果不对,我是要得到这样的结果;
aa sum(n1),sum(n2),sum(n3)
bb sum(n1),sum(n2),sum(n3)
cc sum(n1),sum(n2),sum(n3)
大家快帮帮忙!
FROM dbo.table1 a INNER JOIN
dbo.table2 b ON a.grp1 = b.group1
GROUP BY a.node
我是不要交叉结果的啊
insert @tb1 select '1','aa','n1','1'
union all select '2','bb','n2','2'
union all select '3','cc','n3','3'
select * from @tb1
declare @tb2 table(time1 datetime,group1 char(1),n1 real,n2 real,n3 real)
insert @tb2 select '2005-10-26 9:04:53','1','5','7','6'
union all select '2005-10-26 9:14:53','2','4','8','9'
union all select '2005-10-26 9:24:53','3','5','3','8'
select b.time1,c.node,a.* from
(select group1,sum(n1)as n1,sum(n2) as n2,sum(n3) as n3 from @tb2 group by group1) a
left join @tb2 as b on a.group1=b.group1
left join @tb1 as c on a.group1=c.grp
結果:
time1 node group1 n1 n2 n3
------------------------------------------------------ ------------------------------
2005-10-26 09:04:53.000 aa 1 5.0 7.0 6.0
2005-10-26 09:14:53.000 bb 2 4.0 8.0 9.0
2005-10-26 09:24:53.000 cc 3 5.0 3.0 8.0
有两个表table1与table2表结构以及数据如下:
table1
id int,node nvarchar(100),field char(4),grp char(1)
1 aa n1 1
2 bb n2 1
3 cc n3 1
4 dd n4 1
table2
time1 datetime,group1 char(1),n1 real,n2 real,n3 real,n4 real
2005-10-26 9:04:53 1 5 6 7 8
2005-10-26 9:14:53 1 11 22 33 44
2005-10-26 9:24:53 1 12 13 14 15
2005-10-26 9:34:53 1 22 23 24 25
现在要在某断时间得到这样的统计结果:
aa --------
bb---------
cc---------
dd---------
而我们得到的结果是:
aa 50.0 64.0 78.0 92.0
bb 50.0 64.0 78.0 92.0
cc 50.0 64.0 78.0 92.0
dd 50.0 64.0 78.0 92.0谢谢大家帮忙看看怎么写?
SELECT a.node, SUM(b.n1) AS n1, SUM(b.n2) AS n2, SUM(b.n3) AS n3, SUM(b.n4) AS n4
FROM dbo.table1 a INNER JOIN
dbo.table2 b ON a.grp = b.grp1
GROUP BY a.node
得到上面的结果