表A结构
id num
1 4
1 3
1 2
2 1
2 2
2 3
得到最id相同的num和最大的id
这是一个问题
然后如果还有一个表B和上面A表id关联的
id test
1 abc
2 dddddd
我要查找出B表中的test字段包含'b'字母的A表中id相同的num和最大的id如果输入,按上面要求,应该结果集 id=1 sum(num=4+3+2) test=abc
id num
1 4
1 3
1 2
2 1
2 2
2 3
得到最id相同的num和最大的id
这是一个问题
然后如果还有一个表B和上面A表id关联的
id test
1 abc
2 dddddd
我要查找出B表中的test字段包含'b'字母的A表中id相同的num和最大的id如果输入,按上面要求,应该结果集 id=1 sum(num=4+3+2) test=abc
b.ID,b.test,sum(a.num)
from
a
join
b on a.Id=b.ID
group by b.ID,b.test
---测试数据---
declare @tb table(id int, num int)
insert @tb
select 1 , 4 union all
select 1 , 3 union all
select 1 , 2 union all
select 2 , 1 union all
select 2 , 2 union all
select 2 , 3 ---查询---
select id, max(num) as MaxNum from @tb group by id---结果---
id MaxNum
----------- -----------
1 4
2 3(所影响的行数为 2 行)
---测试数据---
declare @ta table(id int, num int)
insert @ta
select 1 , 4 union all
select 1 , 3 union all
select 1 , 2 union all
select 2 , 1 union all
select 2 , 2 union all
select 2 , 3
declare @tb table(id int, test varchar(10))
insert @tb
select 1, 'abc' union all
select 2, 'dddddd' ---查询---
select a.id, sum(num) as SumNum from @ta a,@tb b
where a.id=b.id and b.test like '%b%'
group by a.id---结果---
id SumNum
----------- -----------
1 9(所影响的行数为 1 行)
declare @t table (id int,num int)
insert into @t select 1,4
union all select 1,3
union all select 1,2
union all select 2,1
union all select 2,2
union all select 2,3
declare @t1 table (id int, test varchar(10))
insert into @t1 select 1,'abc'
union all select 2,'dddddd'
select a.id,b.test ,sum(a.num)num from @t a ,@t1 b where a.id=b.id
group by a.id,b.test
1 abc 9
2 dddddd 6
---测试数据---
declare @ta table(id int, num int)
insert @ta
select 1 , 4 union all
select 1 , 3 union all
select 1 , 2 union all
select 2 , 1 union all
select 2 , 2 union all
select 2 , 3
declare @tb table(id int, test varchar(10))
insert @tb
select 1, 'abc' union all
select 2, 'dddbddd' ---查询---
declare @id int;
select @id=id from(
select top 1 a.id, sum(num) as SumNum from @ta a,@tb b
where a.id=b.id and b.test like '%b%'
group by a.id
order by SumNum desc
) cselect @id as '@id'---结果---
@id
-----------
1(所影响的行数为 1 行)
这样?
on b.id= aa.id
where b.test like '%b%'