请问这个SQL 语句该怎么写

[yqzq]

比如 主表有如下内容
id name sex
1 yq m
2 qq f
3 p m

子表有如下内容

id js bs
1 aaa 12
1 bbb 22
2 aaa 31
2 bbb 43

我想 查询出这样的结果:

name aaa bbb
yq 12 22
qq 31 43


不知道我说的明白不?

btw: 每个主表的记录对应的子表的记录的条数是不一定的

谢谢

[lqtflwg718]

不明白你的意思啊！

[zheninchangjiang]

declare @sql varchar(8000)
set @sql='select b.name,'
select @sql=@sql+js+'=sum(case when a.js='''+js+''' then a.bs else 0 end ),' from tableb
set @sql=substring(@sql,1,len(@sql)-1)+' from tableb a join tablea a on a.id=b.id'
exec(@sql)

[zheninchangjiang]

declare @sql varchar(8000)
set @sql='select b.name,'
select @sql=@sql+js+'=sum(case when a.js='''+js+''' then a.bs else 0 end ),' from tableb
set @sql=substring(@sql,1,len(@sql)-1)+' from tableb a join tablea a on a.id=b.id group by b.name'
exec(@sql)

[zheninchangjiang]

declare @sql varchar(8000)
set @sql='select b.name,'
select @sql=@sql+js+'=sum(case when a.js='''+js+''' then a.bs else 0 end ),' from (select distinct js from tableb) a --这下好了
set @sql=substring(@sql,1,len(@sql)-1)+' from tableb a join tablea b on a.id=b.id group by b.name'
exec(@sql)

[skyboy0720]

select a.name,aaa=(select bs from 子表 where js='aaa' and id=a.id),bbb=(select bs from 子表 where js='bbb' and id=a.id) from 主表 a

试试

[zjcxc]

declare @s varchar(8000)
set @s=@s+',['+js+']=sum(case b.id when '+cast(id as varchar)+' then b.bs else 0 end)'
from 子表
group by id,js
exec('seelct a.name'+@js+'
from 主表 a join 子表b on a.id=b.id
group by a.name')

[zjcxc]

--改错:
declare @s varchar(8000)
set @s=''
select @s=@s+',['+js+']=sum(case b.id when '+cast(id as varchar)+' then b.bs else 0 end)'
from 子表
group by id,js
exec('select a.name'+@s+'
from 主表 a join 子表 b on a.id=b.id
group by a.name')

[zjcxc]

--测试

--测试数据
create table 主表(id int,name varchar(10),sex char(1))
insert 主表 select 1,'yq','m'
union all select 2,'qq','f'
union all select 3,'p' ,'m'

create table 子表(id int,js varchar(10),bs int)
insert 子表 select 1,'aaa',12
union all select 1,'bbb',22
union all select 2,'aaa',31
union all select 2,'bbb',43
go

--查询
declare @s varchar(8000)
set @s=''
select @s=@s+',['+js+']=sum(case b.id when '+cast(id as varchar)+' then b.bs else 0 end)'
from 子表
group by id,js
exec('select a.name'+@s+'
from 主表 a join 子表 b on a.id=b.id
group by a.name')
go

--删除测试
drop table 主表,子表

/*--测试结果

name aaa bbb aaa bbb
---------- ----------- ----------- ----------- -----------
qq 0 0 74 74
yq 34 34 0 0
--*/

[MichaelZou]

CREATE TABLE 主表
(id INT,name VARCHAR(10),sex VARCHAR(1))
INSERT INTO 主表
SELECT 1,'yq','m' UNION ALL
SELECT 2,'qq','f' UNION ALL
SELECT 3,'p' ,'m'

CREATE TABLE 子表
(id INT,js VARCHAR(10),bs INT)
INSERT INTO 子表
SELECT 1,'aaa',12 UNION ALL
SELECT 1,'bbb',22 UNION ALL
SELECT 2,'aaa',31 UNION ALL
SELECT 2,'bbb',43

---------------------------------
DECLARE @sql1 VARCHAR(2000)
DECLARE @sql2 VARCHAR(5000)

SET @sql1 = ''
SET @sql2 = ''

SELECT @sql1 = @sql1 + ',SUM(CASE WHEN js = '''+js+''' THEN bs END) ['+js+']' FROM (SELECT DISTINCT js FROM 子表) A
SET @sql2 = 'SELECT name'+@sql1+' FROM 子表 LEFT OUTER JOIN 主表 ON 子表.id = 主表.id GROUP BY name '

EXEC(@sql2)

---------------------------------
DROP TABLE 主表
DROP TABLE 子表

[ocean9b]

你是不是想吧行转换成列?

declare @sql_exe as varchar(8000)
declare @sql_colvalue as varchar(30)
declare @sql_colcount int

select @sql_colcount=count(distinct js) from [子表] --找出子表最大的项数

select @sql_exe=''

declare cur_colname cursor
for
select distinct js from [子表] --查找的记录

/*打开游标*/
open cur_colname
fetch next from cur_colname
into @sql_colvalue
while (@@fetch_status=0)
begin
select @sql_exe=@sql_exe+',sum(case js when '+char(39)+@sql_colvalue+char(39)+' then bs else 0 end) '+char(39)+@sql_colvalue+char(39) --用语句构造列

/*游标指向下一条记录*/
fetch next from cur_colname
into @sql_colvalue
end
close cur_colname
deallocate cur_colname

select @sql_exe='select [name]'+@sql_exe+' from [主表],[子表] where [主表].id=[子表].id group by [name] order by [name]'

if @sql_colcount=0
select message='no records'
else
execute(@sql_exe)

在概是这样吧...

[hsanzhang]

不知你要的是不是這樣
SELECT a.id,name,
max(case js when 'aaa' then bs end) as 'aaa',
max(case js when 'bbb' then bs end ) as 'bbb'
FROM a INNER JOIN
b ON a.aid =b.aid
group by a.id,name
以上做的是交叉表

[yqzq]

谢谢各位

等我测试一下就散分!

谢谢!