select table1.a,(select count from table2 where type=type1),(select count from table2 where type=type2),(select count from table2 where type=type3) from Table1
其实,通过table2就能得到table3 语句如下: select name,sum(case when type='type1' then count end) as type1, sum(case when type='type2' then count end) as type2 , sum(case when type='type2' then count end) as type3 from table2 group by name如果还需要table1的其他项则可以 select t1.name,t2.type1,t2.type2,t3.type3 from table1 as t1, (select name,sum(case when type='type1' then count end) as type1, sum(case when type='type2' then count end) as type2 , sum(case when type='type2' then count end) as type3 from table2 group by name) as t2
更正,少个条件 select t1.name,t2.type1,t2.type2,t3.type3 from table1 as t1, (select name,sum(case when type='type1' then count end) as type1, sum(case when type='type2' then count end) as type2 , sum(case when type='type2' then count end) as type3 from table2 group by name) as t2where t1.name=t2.name
update table1 set type1=aa.type1,type2=aa.type2,type3=aa.type3 from table left join (select name,sum(case when type='type1' then count end) as type1, sum(case when type='type2' then count end) as type2 , sum(case when type='type2' then count end) as type3 from table2 group by name)aa on aa.name=table.name
declare @Field sysname declare @Name varchar(255) --自己设置长度 declare @Count varchar(20) --不能用 int declare Field cursor for select * from table2 open Field fetch next from Field into @Name, @Field, @Count while @@fetch_status = 0 begin exec ('update table1 set ' + @Field + ' = ' + @Count + ' where name = ''' + @Name + '''') -- 可以改成: -- print ('update table1 set ' + @Field + ' = ' + @Count + ' where name = ''' + @Name + '''') -- 先看看 fetch next from Field into @Name, @Field, @Count end close Field deallocate Field /* 我一直是这么做的,如果有哪位高手用一条语句搞定,我以全部分相送。 */
update table1 set type1=a.type1,type2=a.type2,type3=a.type3 from (SELECT name, SUM(CASE type WHEN 'type1' THEN count ELSE null END) AS type1, SUM(CASE type WHEN 'type2' THEN count ELSE null END) AS type2, SUM(CASE type WHEN 'type3' THEN count ELSE null END) AS type3 FROM table2 GROUP BY name) a where a.name=table1.name
楼主: declare @sql varchar(8000) set @sql = 'select name,'select @sql = @sql + 'sum(case type when '''+type+''' then count else null end) as '''+type+''',' from (select distinct type from table1) as aselect @sql = left(@sql,len(@sql)-1) + ' from table2 group by name'exec(@sql) go
语句如下:
select name,sum(case when type='type1' then count end) as type1,
sum(case when type='type2' then count end) as type2 ,
sum(case when type='type2' then count end) as type3
from table2
group by name如果还需要table1的其他项则可以
select t1.name,t2.type1,t2.type2,t3.type3 from table1 as t1,
(select name,sum(case when type='type1' then count end) as type1,
sum(case when type='type2' then count end) as type2 ,
sum(case when type='type2' then count end) as type3
from table2
group by name) as t2
select t1.name,t2.type1,t2.type2,t3.type3 from table1 as t1,
(select name,sum(case when type='type1' then count end) as type1,
sum(case when type='type2' then count end) as type2 ,
sum(case when type='type2' then count end) as type3
from table2
group by name) as t2where t1.name=t2.name
from table left join
(select name,sum(case when type='type1' then count end) as type1,
sum(case when type='type2' then count end) as type2 ,
sum(case when type='type2' then count end) as type3
from table2
group by name)aa
on aa.name=table.name
type1 type2 type3 ... type[n]
n在执行的时候才知道!
declare @Name varchar(255) --自己设置长度
declare @Count varchar(20) --不能用 int
declare Field cursor for select * from table2
open Field
fetch next from Field into @Name, @Field, @Count
while @@fetch_status = 0
begin
exec ('update table1 set ' + @Field + ' = ' + @Count + ' where name = ''' + @Name + '''')
-- 可以改成:
-- print ('update table1 set ' + @Field + ' = ' + @Count + ' where name = ''' + @Name + '''')
-- 先看看
fetch next from Field into @Name, @Field, @Count
end
close Field
deallocate Field
/*
我一直是这么做的,如果有哪位高手用一条语句搞定,我以全部分相送。
*/
SUM(CASE type WHEN 'type1' THEN count ELSE null END) AS type1,
SUM(CASE type WHEN 'type2' THEN count ELSE null END) AS type2,
SUM(CASE type WHEN 'type3' THEN count ELSE null END) AS type3
FROM table2
GROUP BY name) a where a.name=table1.name
declare @sql varchar(8000)
set @sql = 'select name,'select @sql = @sql + 'sum(case type when '''+type+'''
then count else null end) as '''+type+''','
from (select distinct type from table1) as aselect @sql = left(@sql,len(@sql)-1) + ' from table2 group by name'exec(@sql)
go
你的问题能说清楚一点吗?