[t-sql]两个表的合并
0
悬赏园豆：40
[已解决问题]
解决于 2010-03-23 22:39
<table style="width: 304px; height: 122px;">
<thead>这是第一个表格</thead>
<tbody>
<tr>
<td style="width: 31px;">id</td>
<td style="width: 104px;">name</td>
<td style="width: 156px;">Sex</td>
</tr>
<tr>
<td style="width: 31px;">1</td>
<td style="width: 104px;">aa</td>
<td style="width: 156px;">m</td>
</tr>
<tr>
<td style="width: 31px;">2</td>
<td style="width: 104px;">bb</td>
<td style="width: 156px;">f</td>
</tr>
<tr>
<td style="width: 31px;">3</td>
<td style="width: 104px;">cc</td>
<td style="width: 156px;">f</td>
</tr>
</tbody>
</table>
<table style="width: 300px; height: 163px;">
<thead>这个是第二个表格</thead>
<tbody>
<tr>
<td style="width: 28px;">id</td>
<td style="width: 150px;">name</td>
<td style="width: 246px;">value</td>
</tr>
<tr>
<td style="width: 28px;">1</td>
<td style="width: 150px;">ad</td>
<td style="width: 246px;">ad1</td>
</tr>
<tr>
<td style="width: 28px;">1</td>
<td style="width: 150px;">age</td>
<td style="width: 246px;">ag1</td>
</tr>
<tr>
<td style="width: 28px;">2</td>
<td style="width: 150px;">ad</td>
<td style="width: 246px;">ad2</td>
</tr>
<tr>
<td style="width: 28px;">2</td>
<td style="width: 150px;">age</td>
<td style="width: 246px;">ag2</td>
</tr>
<tr>
<td style="width: 28px;">3</td>
<td style="width: 150px;">ad</td>
<td style="width: 246px;">ad3</td>
</tr>
<tr>
<td style="width: 28px;">3</td>
<td style="width: 150px;">age</td>
<td style="width: 246px;">ag3</td>
</tr>
</tbody>
</table>
<table style="width: 301px; height: 123px;">
<thead>这个是第三个表格</thead>
<tbody>
<tr>
<td style="width: 23px;">id</td>
<td style="width: 42px;">name</td>
<td style="width: 58px;">sex</td>
<td style="width: 72px;">ad</td>
<td style="width: 81px;">age</td>
</tr>
<tr>
<td style="width: 23px; height: 23px;">1</td>
<td style="width: 42px; height: 23px;">a</td>
<td style="width: 58px; height: 23px;">m</td>
<td style="width: 72px; height: 23px;">ad1</td>
<td style="width: 81px; height: 23px;">ag1</td>
</tr>
<tr>
<td style="width: 23px;">2</td>
<td style="width: 42px;">b</td>
<td style="width: 58px;">f</td>
<td style="width: 72px;">ad2</td>
<td style="width: 81px;">ag2</td>
</tr>
<tr>
<td style="width: 23px;">3</td>
<td style="width: 42px;">c</td>
<td style="width: 58px;">f</td>
<td style="width: 72px;">ad3</td>
<td style="width: 81px;">ag3</td>
</tr>
</tbody>
</table>
<p> </p>
<p>先说下我的思路：我知道可能会用到这两个关键词：union , as </p>
<p>请各位大大给帮忙给点提示。</p>
最佳答案
0
代码
declare @t table(id int,name varchar(10),sex char(1))
insert @t
select 1, 'aa', 'm'
union all
select 2, 'bb', 'f'
union all
select 3, 'cc', 'f'
declare @t1 table(id int,name varchar(10),value varchar(10))
insert @t1
select 1, 'ad','ad1'
union all
select 1, 'age', 'ag1'
union all
select 2, 'ad', 'ad2'
union all
select 2, 'age', 'ag2'
union all
select 3, 'ad', 'ad3'
union all
select 3, 'age', 'ag3'
select distinct a.*,(select value from @t1 where id=a.id and name='ad') as ad,(select value from @t1 where id=a.id and name='age') as age
from @t a left join @t1 b on a.id=b.id
收获园豆：40
谢谢！
其他回答(1)
0
你说的合并是列合并，还是行合并？
如果是行合并,也就是数据集合合并用union就可以。
列合并即关联。用left|right outer join
例子：a/b/c 为结果集合
select * from (a union b union c) as t where t.*逻辑条件。
select * from a left outer b on a.关联字段名＝b.关联字段名
