SQL，查询，去重

[lanyjh]

A表
--------------------
yqbh 仪器编号
jyrq 检查日期
btbh 住院号
name 姓名
ybh 样本编号
B表
--------------------
yqbh 仪器编号
jyrq 检查日期
ybh 样本编号
tpnr 图片image格式
最后想要的效果 select A.住院号,A.姓名，B.仪器编号,B.检查日期,B. 图片image格式
想了半天实在写不出来，写出来的也是重复数据太多，求高手指点：目前自有41分了，全抛了！谢谢了

----------------------表结果创建脚本-------------------
--------A表创建语句
CREATE TABLE [dbo].[A](
[yqbh] [nchar](50) NULL,
[jyrq] [nchar](50) NULL,
[btbh] [nchar](50) NULL,
[name] [nchar](50) NULL,
[ybh] [nchar](50) NULL
) ON [PRIMARY]

GO
------B表创建语句
CREATE TABLE [dbo].[B](
[yqbh] [nchar](50) NULL,
[jyrq] [nchar](50) NULL,
[tpnr] image NULL,
[ybh] [nchar](50) NULL
) ON [PRIMARY]

GO
--------------------------------测试数据脚本--------------------------
---张三
insert into [A] values ('ADV212','2013-07-28','A001','张三','1')
go
insert into [A] values ('ZL9000','2013-07-28','A001','张三','1')
go
insert into [B] values ('ADV212','2013-07-28',1,'1')
go
insert into [B] values ('ZL9000','2013-07-28',1,'1')
go
---李四
insert into [A] values ('ADV212','2013-07-28','A002','李四','2')
go
insert into [A] values ('ZL9000','2013-07-28','A002','李四','2')
go
insert into [B] values ('ADV212','2013-07-28',1,'2')
go
insert into [B] values ('ZL9000','2013-07-28',1,'2')
go
---王五
insert into [A] values ('ADV212','2013-07-28','A003','王五','3')
go
insert into [A] values ('ZL9000','2013-07-28','A003','王五','3')
go
insert into [B] values ('ADV212','2013-07-28',1,'3')
go
insert into [B] values ('ZL9000','2013-07-28',1,'3')
go

insert into [B] values ('ZL9000','2013-07-28',1,'3')
go

[昵称90天可改一次]

表设计有问题 关联没有条件啊~ 仪器编号不能作为两表关联条件的

[-Tracy-McGrady-]

去我的博客里面看看吧，应该会做了

[昵称90天可改一次]

B表增加btbh列存住院号

CREATE TABLE [dbo].[A](
[yqbh] [nchar](50) NULL,
[jyrq] [nchar](50) NULL,
[btbh] [nchar](50) NULL,
[name] [nchar](50) NULL,
[ybh] [nchar](50) NULL
) ON [PRIMARY]

GO
------B表创建语句
CREATE TABLE [dbo].[B](
[btbh] [nchar](50) NULL,
[yqbh] [nchar](50) NULL,
[jyrq] [nchar](50) NULL,
[tpnr] image NULL,
[ybh] [nchar](50) NULL
) ON [PRIMARY]

GO
--------------------------------测试数据脚本--------------------------
---张三
insert into [A] values ('ADV212','2013-07-28','A001','张三','1')
go
insert into [A] values ('ZL9000','2013-07-28','A001','张三','1')
go
insert into [B] values ('A001','ADV212','2013-07-28','1','1')
go
insert into [B] values ('A001','ZL9000','2013-07-28','1','1')
go
---李四
insert into [A] values ('ADV212','2013-07-28','A002','李四','2')
go
insert into [A] values ('ZL9000','2013-07-28','A002','李四','2')
go
insert into [B] values ('A002','ADV212','2013-07-28','1','2')
go
insert into [B] values ('A002','ZL9000','2013-07-28','1','2')
go
---王五
insert into [A] values ('ADV212','2013-07-28','A003','王五','3')
go
insert into [A] values ('ZL9000','2013-07-28','A003','王五','3')
go
insert into [B] values ('A003','ADV212','2013-07-28','1','3')
go
insert into [B] values ('A003','ZL9000','2013-07-28','1','3')
go

insert into [B] values ('A003','ZL9000','2013-07-28','1','3')
go

select A.btbh,A.name,b.yqbh,b.jyrq,b.tpnr
from A
	INNER JOIN B 
	ON A.btbh=b.btbh and A.yqbh=b.yqbh
/*
btbh   name      yqbh     jyrq            tpnr

A001   张三   	ADV212 	2013-07-28 	0x31
A001   张三   	ZL9000 	2013-07-28 	0x31
A002   李四   	ADV212 	2013-07-28 	0x31
A002   李四   	ZL9000 	2013-07-28 	0x31
A003   王五   	ADV212 	2013-07-28 	0x31
A003   王五   	ZL9000 	2013-07-28 	0x31
A003   王五   	ZL9000 	2013-07-28 	0x31
*/