〓急〓SQL

--静态SQL，指只有station1     station2   station3

select a1.station_name,

  max(case a2.station_name when 'station1' then trade_number else 0 end) 'station1',

  max(case a2.station_name when 'station2' then trade_number else 0 end) 'station2',

  max(case a2.station_name when 'station3' then trade_number else 0 end) 'station3'

from b,a a1,a a2

where a1.station_id = b.entry_station_id and a2.station_id = b.exit_station_id

group by a1.station_name



--动态SQL，指不止station1     station2   station3

declare @sql varchar(8000)

set @sql = 'select a.station_name'

select @sql = @sql + ' , max(case exit_station_id when ''' + rtrim(station_id) + ''' then trade_number else 0 end) [' + station_name + ']'

from a

order by station_id



set @sql = @sql + ' from a,b where a.station_id = b.entry_station_id  group by a.station_name'

exec(@sql) 

--静态SQL，指只有station1     station2   station3

select entry_station_id,

  sum(case station_name when 'station1' then trade_number else 0 end) 'station1',

  sum(case station_name when 'station2' then trade_number else 0 end) 'station2',

  sum(case station_name when 'station3' then trade_number else 0 end) 'station3'

from a,b

where a.station_id = b.station_id

group by b.entry_station_id



--动态SQL，指不止station1     station2   station3

declare @sql varchar(8000)

set @sql = 'select entry_station_id'

select @sql = @sql + ' , sum(case station_name when ''' + station_name + ''' then trade_number else 0 end) [' + station_name + ']'

from (select distinct station_name from (select b.entry_station_id,a.station_name,b.trade_number from a,b where a.station_id = b.station_id) t) as a

set @sql = @sql + ' from (select b.entry_station_id,a.station_name,b.trade_number from a,b where a.station_id = b.station_id) t group by entry_station_id'

exec(@sql) 

--静态SQL

select entry_station_id,

  sum(case station_name when 'station1' then trade_number else 0 end) 'station1',

  sum(case station_name when 'station2' then trade_number else 0 end) 'station2',

  sum(case station_name when 'station3' then trade_number else 0 end) 'station3'

from a,b

where a.station_id = b.station_id

group by b.entry_station_id