select a, b, [count]=count(*), e_sum=sum(e), f_sum=sum(f) from T_test
group by a, b
group by a, b
解决方案 »
- sqlserver2005安装不上
- sql函数分哪几类,distinct属于哪一类?
- 请教几个关于存储过程的问题,谢谢
- attach Northwind数据库到SQL 2005 Express报错是啥原因?
- 执行存贮过程返回记录集,为何存贮过程中不加:set nocount on就不会返回记录集呢?
- exec sp_addrolemember N'db_owner', N'enanny'
- 如何实现顺序编号!
- 如何用sql 语句把一列名字累加起来组成一个长字符串付给一个变量??
- 模糊查询时间问题,
- 在存储过程调用不同数据库的数据该如何写??
- ASP+SQL中,UPDATE语法出错
- 如何重 批处理中 返回值?
感谢,我不是想要这个结果
(
select a, b, [count]=count(*), e_sum=sum(e), f_sum=sum(f) from T_test
group by a, b
)tmpA
left join
(
select a, b, [count]=(select top 1 count(*) from T_test2 where a=A.a and b=A.b group by a,g) from T_test2 as A
group by
a, b
)tmpB on tmpA.a=tmpB.a and tmpA.b=tmpB.b --result
a b count e_sum f_sum count
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 15 18 1
1 3 2 10 12 2(2 row(s) affected)
(id int identity(1,1) primary key,
a int,b int ,c int ,d int, e int ,f int )
insert into t_test
select 1,2,3,3,5,6 union all
select 1,2,3,3,5,6 union all
select 1,2,3,4,5,6 union all
select 1,3,3,3,5,6 union all
select 1,3,3,3,5,6 create table T_test2
(
id int identity(1,1) primary key,
a int,g int,b int
)
insert into T_test2
select 1,2,2 union all
select 1,1,2 union all
select 1,2,2 union all
select 1,2,3 union all
select 1,2,3
go
select
a.*,b.cnt2
from
(select a,b,count(*) as cnt1,sum(e) as e,sum(f) as f from T_test group by a,b) a,
(select a,b,count(*) as cnt2 from T_test2 group by a,b) b
where
a.a=b.a and a.b=b.b
go
/*
a b cnt1 e f cnt2
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 15 18 3
1 3 2 10 12 2
*/drop table t_test,t_test2
go
libin_ftsafe的结果好象有点问题
marco08(天道酬勤)应该对了
我试试
select tmpA.*, tmpB.[count] from
(
select a, b, [count]=count(*), e_sum=sum(e), f_sum=sum(f) from T_test
group by a, b
)tmpA
left join
(
select a, b, [count]=(select count(distinct g) from T_test2 where a=A.a and b=A.b)
from T_test2 as A
group by
a, b
)tmpB on tmpA.a=tmpB.a and tmpA.b=tmpB.b --result
a b count e_sum f_sum count
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 15 18 2
1 3 2 10 12 1(2 row(s) affected)
isnull(a.a,b.a) as a,
isnull(a.b,b.b) as b,
a.cnt1,
a.e,
a.f,
b.cnt2
from
(select a,b,count(*) as cnt1,sum(e) as e,sum(f) as f from T_test group by a,b) a
full outer join
(select a,b,count(*) as cnt2 from T_test2 group by a,b) b
on
a.a=b.a and a.b=b.b
marco08 的可以了
我仔细看看
谢谢你们