导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

主表和辅表组成视图(一对多),能不能不显示重复项

liujiayu10 2008-01-18 12:56:42
如主表:
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

就是说辅表作为查询条件,但列表不显示!
...全文
150 点赞 收藏 21
写回复
21 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
-狙击手- 2008-01-18
这不就是主表吗?
回复
yms_wangxm 2008-01-18
顶了,,,
回复
wzy_love_sly 2008-01-18
up接分
回复
liujiayu10 2008-01-18
解决!

distinct 影响的不仅是后边紧跟着的一个字段,而是所有字段!就是这样!
回复
dawugui 2008-01-18
--方法一(不排除重复)
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
回复
dawugui 2008-01-18
select a.* from 主表 a, 辅表 b where a.orderid = b.orderid and b.product = 'aaaa'
回复
shmlove521 2008-01-18
用一个去处重复的 语句 可是我不会 想楼上学习
回复
JL99000 2008-01-18
一句话搞顶的东西,愁人啊,接分了
--就这一句话
select distinct a.* from master a inner join detail b on a.OrderID=b.OrderID
回复
ancyzhou 2008-01-18
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 就可以了
回复
liujiayu10 2008-01-18
以上没有正确答案呢!

我用如下查询:
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重复的项去掉了

回复
wanglv 2008-01-18
select distinct a.OrderID , a.Company ,b.Product
from 主表 a ,如辅表 b where a.OrderID =b.OrderID
回复
wzy_love_sly 2008-01-18
顶,up,接分
回复
-狙击手- 2008-01-18
4楼符合你的意思吗?
回复
dobear_0922 2008-01-18
可是,我要查询的条件在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
回复
liujiayu10 2008-01-18
可是,我要查询的条件在detail 表里啊!如 产品aaaa
回复
dobear_0922 2008-01-18
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
回复
dobear_0922 2008-01-18
是啊,直接从主表查不就完了?
回复
-狙击手- 2008-01-18
哦,


select a.*
from 主表 a,
(select distinct OrderID from 辅表) b
where a.orderid = b.orderid
回复
JiangHongTao 2008-01-18
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
*/
回复
leo_lesley 2008-01-18
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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告