declare @boy table
(
boyid int,name varchar(10)
)
declare @girl table
(
girlid int,name varchar(10)
)declare @boy_girl table
(
bgid int,name varchar(10),sex varchar(10)
)
insert @boy
select 1,'Tom' union all
select 2,'Ab' union all
select 3,'John'
insert @girl
select 1,'Alice' union all
select 2,'Tand'
insert @boy_girl
select 1,null,'男' union all
select 2,null,'女' union all
select 1,null,'女' union all
select 3,null,'男'--查询
select A.bgid
,B.name
,A.sex
from @boy_girl A
join (
select *,'男' as 'sex' from @boy
union all
select *,'女' from @girl
)B on A.bgid=B.boyid and A.sex=B.sex
--结果
/*
bgid name sex
----------- ---------- ----------
1 Tom 男
2 Tand 女
1 Alice 女
3 John 男(所影响的行数为 4 行)
*/
(
boyid int,name varchar(10)
)
declare @girl table
(
girlid int,name varchar(10)
)declare @boy_girl table
(
bgid int,name varchar(10),sex varchar(10)
)
insert @boy
select 1,'Tom' union all
select 2,'Ab' union all
select 3,'John'
insert @girl
select 1,'Alice' union all
select 2,'Tand'
insert @boy_girl
select 1,null,'男' union all
select 2,null,'女' union all
select 1,null,'女' union all
select 3,null,'男'--查询
select A.bgid
,B.name
,A.sex
from @boy_girl A
join (
select *,'男' as 'sex' from @boy
union all
select *,'女' from @girl
)B on A.bgid=B.boyid and A.sex=B.sex
--结果
/*
bgid name sex
----------- ---------- ----------
1 Tom 男
2 Tand 女
1 Alice 女
3 John 男(所影响的行数为 4 行)
*/
解决方案 »
- A B C 三台mssqlserver 2000数据库服务器,数据汇总至第四台D服务器上,请前辈指志下方案!小弟先感谢了!
- 弱弱的问题。。。。
- SQL不能实现想要的结果。
- 帮我看看我的存储进程执行结果杂不对呢,比如参数我传递的是@sdate 2005-05-23 14:59:59 和@sdate 2005-05-22 14:59:59
- SQL2000的备份中"事务日志","文件和文件组"两个选项别灰色.
- 能否将sql server 中一张表中的某些字端 导入 sql server中另一数据库中的另一张表的某些字段中去?
- 为什么win98安装不上SQL Server 2000 安装服务器版 请各位大虾帮忙!!!
- 增量更新用Sql语句怎么写啊,达人救助!
- 请教一个公交车SQL查询问题?
- 求一SQL语句
- 通过Select 语句查询xml文件中的数据
- 一个比较烦的插入数据过程
AS
select bgid,name=case sex='男' then b.name else c.name end ,sex from bor_girl a inner join boy b on a.bgid=b.boyid and a.sex='男' inner join girl c on a.bgid=c.girlid and a.sex='女'
c
GO
select * from (
select boyid as 'bgid',name,'男' as sex from boy
union all
select girlid,name,'女' as sex from girl)aa
select * from (
select boyid as 'bgid',name,'男' as sex from boy
union all
select girlid,name,'女' as sex from girl)aa
as
select bgid,name=
case sex='男'
then b.name
else c.name
end ,
sex
from girl a inner join boy b
on a.bgid=b.boyid and
a.sex='男' inner join girl c
on a.bgid=c.girlid and a.sex='女'