select 字段a,字段b+tmp_table.tt from 表1,(select 表1.字段a,sum(表2.b) as tt from 表2,表1 where 表2.字段1=表1 .字段a group by 表1.字段a)tmp_table where tmp_table..字段a=表1.字段a
table1(a1,b1) table2(a2,b2)sql statement: --------------------------------------------- Select a1, b1 + sum_b2 from table1 x, (select a2, sum(b2) sum_b2 from table2 group by a2) y where x.a1= y.a2
这是oracle中的语法吧,sql server下怎么实现呢?
同意KingSunSha(弱水三千) ,sql server也是这样的
弱水的解答很对,但我条件实际是这样的 where 表2.字段1 like '字段a的当前值%' 如果是这样我应该怎么办呢?谢了!!!!
select table1.fldA,table1.fldB+tmp.Total From (select fld1,sum(b) as Total from table2 group by fld1,b) as tmp,table1 where table1.fldA=tmp.fld1
如果是要 where 表2.字段1 like '字段a.的當前值%'話,非要動用temp table或 cursor不可。不過,或許高手不用,呵呵~~~~~
可以做的: ----------------------------------------- Select a1, b1 + sum_b2 from table1 x, (select a1, sum(b2) sum_b2 from table2 t2, (select distinct a1 from table1) t1 where t2.a2 like t1.a1 || '%' group by a1) y where x.a1= y.a1
--我同事代碼…嘿嘿。 --數據 declare @t table(fld1 varchar(30),b1 int) declare @tb table (fldA varchar(30),cs int) insert into @t values( 'abc',100) insert into @tb values('abc',200) insert into @tb values('abcd',220) insert into @tb values('abcde',320) ------Select distinct r.fld1, Total=r.b1+sum(tb.cs) From @t as r ,@tb as tb Where tb.fldA like r.fld1+'%' group by r.fld1,r.b1
table2(a2,b2)sql statement:
---------------------------------------------
Select a1, b1 + sum_b2
from table1 x, (select a2, sum(b2) sum_b2
from table2
group by a2) y
where x.a1= y.a2
where 表2.字段1 like '字段a的当前值%'
如果是这样我应该怎么办呢?谢了!!!!
-----------------------------------------
Select a1, b1 + sum_b2
from table1 x, (select a1, sum(b2) sum_b2
from table2 t2, (select distinct a1 from table1) t1
where t2.a2 like t1.a1 || '%'
group by a1) y
where x.a1= y.a1
--數據
declare @t table(fld1 varchar(30),b1 int)
declare @tb table (fldA varchar(30),cs int)
insert into @t values( 'abc',100)
insert into @tb values('abc',200)
insert into @tb values('abcd',220)
insert into @tb values('abcde',320)
------Select distinct r.fld1, Total=r.b1+sum(tb.cs) From @t as r ,@tb as tb Where tb.fldA like r.fld1+'%' group by r.fld1,r.b1