如主表:
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就是说辅表作为查询条件,但列表不显示!
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就是说辅表作为查询条件,但列表不显示!
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' goselect a.* from master a inner join (select distinct OrderID from detail) b on a.OrderID=b.OrderID
go
drop table master,detail
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
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
-----------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
from 主表 a ,如辅表 b where a.OrderID =b.OrderID
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重复的项去掉了
b.CardID
FROM OrderMain a INNER JOIN OrderDetail b ON a.OrderID = b.OrderID where b.cjr like '%赵%' 加个distinct 就可以了
--就这一句话
select distinct a.* from master a inner join detail b on a.OrderID=b.OrderID
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