sql多表联合查询

下面来看看实例

三个表

收费表

id flowid totall
1 k1 3000

详细表
id flowid pid num
1 k1 1 2000
2 k1 2 1000

付款方式表
id pname
1 现金
2 支票
3 刷卡

create table pay(id int,flowid varchar(10),totall int)
insert into pay select 1,'k1',3000

create table detail(id int,flowid varchar(10),pid int,num int)
insert into detail select 1,'k1',1,2000
insert into detail select 2,'k1',2,1000

create table mode(id int,pname varchar(10))
insert into mode select 1,'现金'
insert into mode select 2,'支票'
insert into mode select 3,'刷卡'

declare @sql varchar(8000)
set @sql=''

select @sql=@sql+',['+pname+']=sum(case b.pid when '+rtrim(id)+' then num else 0 end)'
from mode

set @sql='select a.id,a.flowid,a.totall'+@sql+' from pay a,detail b where a.flowid=b.flowid group by a.id,a.flowid,a.totall'
exec(@sql)

结果：

id flowid totall 现金 支票 刷卡
1 k1 3000 2000 1000 0