〓急〓SQL

[tds__609]

表A
station_id station_name;
表B
entry_station_id exit_station_id trade_number

求 （exit_station_id）
(entry_station_id)
station1 station2 station3
station1 XXXX XXXX XXXX
station2 XXXX XXXX XXXX
station3 XXXX XXXX XXXX

----------
XXXX表B中的trade_number
station1,2,3是表A中的所有数据
怎样得到上述结果啊?? 谢谢大家拉

[pengchao_214]

說的清楚些,有點看不明白,把A , B 的數據寫出來,讓後把想要的結果寫出來

[dawugui]

--静态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

[dawugui]

--静态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) 

[tds__609]

表A
station_id station_name
station1 one
station2 two
station3 three
表B
entry_station_id exit_station_id trade_number
one one 10
one two 11
two three 12
求
one two three
one 10 11 0
two 0 0 12
three 0 0 0

[昵称被占用了]

--静态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)