select @fieldrecordscount=count(a_field) from a group by a_field order by count(a_field) desc for i=0 to @fieldrecordcount
select @fieldrecordscount=count(a_field) from a group by a_field order by count(a_field) desc go record's top for i=0 to @fieldrecordcount ..........
你需要动态拼一个SQL,然后执行它。 可能要写一个存储过程或类似的东西。
就是说先统计出A表A_Field最大的记录个数 然后用循环得出select 从B_field c_fields...... 的select 语名 如:set MySQL="select A_field " for i=1 to @fieldrecordcount set MySQL=Mysql+...(字段名可灵活自取) next i set MySQL=MySQL+" from a order by A_field asc"
declare @fieldrecordscount int declare @b_field1 decimal(9,2) declare @a_field1 nvarchar(10) declare @mysql1 nvarchar(4000) declare @mysql2 nvarchar(4000) declare @mysql4 nvarchar(4000) declare @mysql3 nvarchar(4000) declare @mysql nvarchar(4000) declare @temp varchar(10) declare @a_field varchar(10) declare @i as int select top 1 @fieldrecordscount=count(a_field) from mytable group by a_field order by count(a_field) descset @mysql='' set @mysql1='' set @mysql2='' set @mysql3='' set @mysql4='' declare recordsort cursor for select a_field from mytable group by a_field open recordsort fetch next from recordsort into @a_field while @@fetch_status<>-1 begin set @i=1 declare records cursor for select a_field,b_field from mytable where a_field=@a_field open records fetch next from records into @a_field1,@b_field1 set @temp='temptable1' set @mysql1='select '+@temp+'.a_field,'+@temp+'.b_field as b_field'+convert(varchar(2),@i) set @mysql2=' from mytable '+@temp set @mysql3=' where '+@temp+'.a_field='+''''+@a_field1+''''++' and '+@temp+'.b_field='+str(@b_field1) set @mysql4='' fetch next from records into @a_field1,@b_field1 set @i=@i+1 while @@fetch_status<>-1 begin set @temp='temptable'+convert(varchar(2),@i) set @mysql4=@mysql4+' join ( select a_field,b_field from mytable where a_field='+''''+@a_field1+''''++' and b_field='+str(@b_field1)+') '+@temp+' on temptable1.a_field='+@temp+'.a_field' set @mysql1=@mysql1+','+@temp+'.b_field as b_field'+convert(varchar(2),@i) fetch next from records into @a_field1,@b_field1 set @i=@i+1 end set @i=@i-1 while @i<@fieldrecordscount begin set @mysql1=@mysql1+','''' as b_field'+convert(varchar(2),@i) set @i=@i+1 end close records deallocate records set @mysql1=@mysql1+@mysql2+@mysql4+@mysql3 fetch next from recordsort into @a_field if @@fetch_status<>-1
set @mysql=@mysql+@mysql1+' union ' else set @mysql=@mysql+@mysql1 end close recordsort deallocate recordsort set @mysql=@mysql+' order by temptable1.a_field asc ' exec sp_executesql @mysql 以上经SQLSERVER2000测试成功
declare @fieldrecordscount int declare @b_field1 decimal(9,2) declare @a_field1 nvarchar(10) declare @mysql1 nvarchar(4000) declare @mysql2 nvarchar(4000) declare @mysql4 nvarchar(4000) declare @mysql3 nvarchar(4000) declare @mysql nvarchar(4000) declare @temp varchar(10) declare @a_field varchar(10) declare @i as int select top 1 @fieldrecordscount=count(a_field) from mytable group by a_field order by count(a_field) descset @mysql='' set @mysql1='' set @mysql2='' set @mysql3='' set @mysql4='' declare recordsort cursor for select a_field from mytable group by a_field open recordsort fetch next from recordsort into @a_field while @@fetch_status<>-1 begin set @i=1 declare records cursor for select a_field,b_field from mytable where a_field=@a_field open records fetch next from records into @a_field1,@b_field1 set @temp='temptable1' set @mysql1='select '+@temp+'.a_field,'+@temp+'.b_field as b_field'+convert(varchar(2),@i) set @mysql2=' from mytable '+@temp set @mysql3=' where '+@temp+'.a_field='+''''+@a_field1+''''++' and '+@temp+'.b_field='+str(@b_field1) set @mysql4='' fetch next from records into @a_field1,@b_field1 set @i=@i+1 while @@fetch_status<>-1 begin set @temp='temptable'+convert(varchar(2),@i) set @mysql4=@mysql4+' join ( select a_field,b_field from mytable where a_field='+''''+@a_field1+''''++' and b_field='+str(@b_field1)+') '+@temp+' on temptable1.a_field='+@temp+'.a_field' set @mysql1=@mysql1+','+@temp+'.b_field as b_field'+convert(varchar(2),@i) fetch next from records into @a_field1,@b_field1 set @i=@i+1 end set @i=@i-1 while @i<@fieldrecordscount begin set @mysql1=@mysql1+','''' as b_field'+convert(varchar(2),@i) set @i=@i+1 end close records deallocate records set @mysql1=@mysql1+@mysql2+@mysql4+@mysql3 fetch next from recordsort into @a_field if @@fetch_status<>-1
set @mysql=@mysql+@mysql1+' union ' else set @mysql=@mysql+@mysql1
end close recordsort deallocate recordsort set @mysql=@mysql+' order by temptable1.a_field asc ' exec sp_executesql @mysql结果: a_field b_field1 b_field2 a 123 456 b 789
这样就可以了很简单了select a_field, sum( case a_field when 'a' then b_field else null end ) as b_field, sum( case a_field when 'b' then b_field else null end ) as c_field from table1 group by a_field
for i=0 to @fieldrecordcount
go record's top
for i=0 to @fieldrecordcount
..........
可能要写一个存储过程或类似的东西。
然后用循环得出select 从B_field c_fields...... 的select 语名
如:set MySQL="select A_field "
for i=1 to @fieldrecordcount
set MySQL=Mysql+...(字段名可灵活自取)
next i
set MySQL=MySQL+" from a order by A_field asc"
declare @b_field1 decimal(9,2)
declare @a_field1 nvarchar(10)
declare @mysql1 nvarchar(4000)
declare @mysql2 nvarchar(4000)
declare @mysql4 nvarchar(4000)
declare @mysql3 nvarchar(4000)
declare @mysql nvarchar(4000)
declare @temp varchar(10)
declare @a_field varchar(10)
declare @i as int
select top 1 @fieldrecordscount=count(a_field) from mytable group by a_field order by count(a_field) descset @mysql=''
set @mysql1=''
set @mysql2=''
set @mysql3=''
set @mysql4=''
declare recordsort cursor for
select a_field from mytable group by a_field
open recordsort
fetch next from recordsort into @a_field
while @@fetch_status<>-1
begin
set @i=1
declare records cursor for
select a_field,b_field from mytable where a_field=@a_field
open records
fetch next from records into @a_field1,@b_field1
set @temp='temptable1'
set @mysql1='select '+@temp+'.a_field,'+@temp+'.b_field as b_field'+convert(varchar(2),@i)
set @mysql2=' from mytable '+@temp
set @mysql3=' where '+@temp+'.a_field='+''''+@a_field1+''''++' and '+@temp+'.b_field='+str(@b_field1)
set @mysql4='' fetch next from records into @a_field1,@b_field1
set @i=@i+1
while @@fetch_status<>-1
begin
set @temp='temptable'+convert(varchar(2),@i)
set @mysql4=@mysql4+' join ( select a_field,b_field from mytable where a_field='+''''+@a_field1+''''++' and b_field='+str(@b_field1)+') '+@temp+' on temptable1.a_field='+@temp+'.a_field'
set @mysql1=@mysql1+','+@temp+'.b_field as b_field'+convert(varchar(2),@i)
fetch next from records into @a_field1,@b_field1
set @i=@i+1 end
set @i=@i-1
while @i<@fieldrecordscount
begin
set @mysql1=@mysql1+','''' as b_field'+convert(varchar(2),@i)
set @i=@i+1
end close records
deallocate records set @mysql1=@mysql1+@mysql2+@mysql4+@mysql3
fetch next from recordsort into @a_field
if @@fetch_status<>-1
set @mysql=@mysql+@mysql1+' union '
else
set @mysql=@mysql+@mysql1
end
close recordsort
deallocate recordsort
set @mysql=@mysql+' order by temptable1.a_field asc '
exec sp_executesql @mysql
以上经SQLSERVER2000测试成功
declare @b_field1 decimal(9,2)
declare @a_field1 nvarchar(10)
declare @mysql1 nvarchar(4000)
declare @mysql2 nvarchar(4000)
declare @mysql4 nvarchar(4000)
declare @mysql3 nvarchar(4000)
declare @mysql nvarchar(4000)
declare @temp varchar(10)
declare @a_field varchar(10)
declare @i as int
select top 1 @fieldrecordscount=count(a_field) from mytable group by a_field order by count(a_field) descset @mysql=''
set @mysql1=''
set @mysql2=''
set @mysql3=''
set @mysql4=''
declare recordsort cursor for
select a_field from mytable group by a_field
open recordsort
fetch next from recordsort into @a_field
while @@fetch_status<>-1
begin
set @i=1
declare records cursor for
select a_field,b_field from mytable where a_field=@a_field
open records
fetch next from records into @a_field1,@b_field1
set @temp='temptable1'
set @mysql1='select '+@temp+'.a_field,'+@temp+'.b_field as b_field'+convert(varchar(2),@i)
set @mysql2=' from mytable '+@temp
set @mysql3=' where '+@temp+'.a_field='+''''+@a_field1+''''++' and '+@temp+'.b_field='+str(@b_field1)
set @mysql4='' fetch next from records into @a_field1,@b_field1
set @i=@i+1
while @@fetch_status<>-1
begin
set @temp='temptable'+convert(varchar(2),@i)
set @mysql4=@mysql4+' join ( select a_field,b_field from mytable where a_field='+''''+@a_field1+''''++' and b_field='+str(@b_field1)+') '+@temp+' on temptable1.a_field='+@temp+'.a_field'
set @mysql1=@mysql1+','+@temp+'.b_field as b_field'+convert(varchar(2),@i)
fetch next from records into @a_field1,@b_field1
set @i=@i+1
end
set @i=@i-1
while @i<@fieldrecordscount
begin
set @mysql1=@mysql1+','''' as b_field'+convert(varchar(2),@i)
set @i=@i+1
end close records
deallocate records set @mysql1=@mysql1+@mysql2+@mysql4+@mysql3
fetch next from recordsort into @a_field
if @@fetch_status<>-1
set @mysql=@mysql+@mysql1+' union '
else
set @mysql=@mysql+@mysql1
end
close recordsort
deallocate recordsort
set @mysql=@mysql+' order by temptable1.a_field asc '
exec sp_executesql @mysql结果:
a_field b_field1 b_field2
a 123 456
b 789
sum( case a_field when 'a' then b_field else null end ) as b_field,
sum( case a_field when 'b' then b_field else null end ) as c_field
from table1 group by a_field