主表和辅表组成视图（一对多），能不能不显示重复项

[liujiayu10]

如主表：
OrderID Company
0001 AA
0002 BB

如辅表：
ID OrderID Product
1 0001 aaaa
2 0001 bbbb
3 0002 aaaa
4 0003 cccc

希望最终显示：

0001 AA
0002 BB

就是说辅表作为查询条件，但列表不显示！

[leo_lesley]

create table master(OrderID varchar(100),  Company varchar(10))
insert master
select '0001','AA' 
union select '0002','BB'

create table detail(ID int,OrderID varchar(10), Product varchar(10))
insert detail
select 1       ,'0001',           'aaaa' 
union select 2       ,'0001',           'bbbb' 
union select 3       ,'0002',           'aaaa' 
union select 4       ,'0003',           'cccc' 

go

select a.* from master a inner join (select distinct OrderID from detail) b on a.OrderID=b.OrderID
go
drop table master,detail

[JiangHongTao]

declare @a table(orderid char(4),company varchar(10))
declare @b table(id int,orderid char(4),product varchar(10))
insert @a select '0001','AA' 
union all select '0002','BB'
insert @b select 1,'0001','aaaa'
union all select 2,'0001','bbbb'
union all select 3,'0002','aaaa'
union all select 4,'0003','ccc'

select a.* from @a a,@b b where a.orderid=b.orderid and b.product = 'aaaa'
/*
orrderid      company
---------  ----------------
0001        AA
0002        BB
*/

[-狙击手-]

哦，


select a.*
from 主表 a,
(select distinct OrderID from 辅表) b
where a.orderid = b.orderid

[dobear_0922]

是啊，直接从主表查不就完了？

[dobear_0922]

create table master(OrderID varchar(100),  Company varchar(10))
insert master
select '0001','AA' 
union select '0002','BB'

create table detail(ID int,OrderID varchar(10), Product varchar(10))
insert detail
select 1       ,'0001',           'aaaa' 
union select 2       ,'0001',           'bbbb' 
union select 3       ,'0002',           'aaaa' 
union select 4       ,'0003',           'cccc' 

select * from master m
where exists(select 1 from detail where OrderID=m.OrderID)

/*
OrderID                                                                                              Company
---------------------------------------------------------------------------------------------------- ----------
0001                                                                                                 AA
0002                                                                                                 BB

(2 row(s) affected)
*/

drop table master,detail

[liujiayu10]

可是，我要查询的条件在detail 表里啊！如 产品aaaa

[dobear_0922]

可是，我要查询的条件在detail 表里啊！如 产品aaaa
-----------

create table master(OrderID varchar(100),  Company varchar(10))
insert master
select '0001','AA' 
union select '0002','BB'

create table detail(ID int,OrderID varchar(10), Product varchar(10))
insert detail
select 1       ,'0001',           'aaaa' 
union select 2       ,'0001',           'bbbb' 
union select 3       ,'0002',           'aaaa' 
union select 4       ,'0003',           'cccc' 

select * from master m
where exists(select 1 from detail where OrderID=m.OrderID and Product='aaaa')

/*
OrderID                                                                                              Company
---------------------------------------------------------------------------------------------------- ----------
0001                                                                                                 AA
0002                                                                                                 BB

(2 row(s) affected)
*/

drop table master,detail

[-狙击手-]

4楼符合你的意思吗？

[wzy_love_sly]

顶，up,接分

[wanglv]

select distinct a.OrderID , a.Company ,b.Product
from 主表 a ,如辅表 b where a.OrderID =b.OrderID

[liujiayu10]

以上没有正确答案呢！

我用如下查询：
SELECT a.OrderID, b.CJR,
b.CardID
FROM OrderMain a INNER JOIN OrderDetail b ON a.OrderID = b.OrderID where b.cjr like '%赵%'

结果出现了：
200801160001 赵倩男 32523525
200801160001 赵倩男 32523525
200801160002 赵倩男 32523525
200801160002 赵倩男 32523525

而我需要的是：
200801160001 赵倩男 32523525
200801160002 赵倩男 32523525

即OrderID重复的项去掉了

[ancyzhou]

SELECT distinct a.OrderID, b.CJR,
b.CardID
FROM OrderMain a INNER JOIN OrderDetail b ON a.OrderID = b.OrderID where b.cjr like '%赵%'

加个distinct 就可以了

[JL99000]

一句话搞顶的东西，愁人啊，接分了
--就这一句话
select distinct a.* from master a inner join detail b on a.OrderID=b.OrderID

[shmlove521]

用一个去处重复的 语句 可是我不会 想楼上学习

[dawugui]

select a.* from 主表 a, 辅表 b where a.orderid = b.orderid and b.product = 'aaaa'

[dawugui]

--方法一(不排除重复)
select a.* from 主表 a, 辅表 b where a.orderid = b.orderid and b.product = 'aaaa'

--方法二(排除重复)
select a.* from 主表 a,
(
  select t.* from 辅表 t where product = 'aaaa' and id = (select min(id) from 辅表 where orderid = t.orderid and product = 'aaaa') from 辅表 t
) t
where a.orderid = t.orderid

[liujiayu10]

解决！

distinct 影响的不仅是后边紧跟着的一个字段，而是所有字段！就是这样！

[wzy_love_sly]

up接分

[yms_wangxm]

顶了，，，

[-狙击手-]

这不就是主表吗？