导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

〓急〓SQL

tds__609 2007-11-28 03:56:10
表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中的所有数据
怎样得到上述结果啊?? 谢谢大家拉
...全文
45 点赞 收藏 5
写回复
5 条回复
切换为时间正序
请发表友善的回复…
发表回复


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

回复
tds__609 2007-11-28
表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
回复
dawugui 2007-11-28
--静态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)
回复
dawugui 2007-11-28
--静态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
回复
pengchao_214 2007-11-28
說的清楚些,有點看不明白,把A , B 的數據寫出來,讓後把想要的結果寫出來
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告