tabel1
a1(order no),a2(order date),a3(supplier code)
10001 2008-1-1 90000
10001 2008-1-1 90000
10001 2008-1-1 90000
10002 2008-1-1 90000
10002 2008-1-1 90000
10002 2008-1-1 90000tabel2
b1(order no),b2(stock code,)b3(qty)......
10001 AAA 10
10001 AAA 10
10001 AAA 20
10002 BBB 10
10002 BBB 10
10002 BBB 20我要tabel2里的数据和tabel1里的 date,suppliercode.请问如何实现
select A1,A2,A3,B1,B2,B3 from tabel1 inner join tabel2 on A1=B1 where A3='90000' and A2>'2007-12-31'
如果使用distinct 会少查询一列数据,不用distinct,会都一些数据。。
10001 AAA 10
10001 AAA 10
用distinct只会出现一列了。
2008-1-1 90000 10001 AAA 10
2008-1-1 90000 10001 AAA 20
2008-1-1 90000 10002 BBB 10
2008-1-1 90000 10002 BBB 10
2008-1-1 90000 10002 BBB 20
就是tabel2里的全部出现group by 不行.
就是过滤掉重复的数据
select A1,A2,A3,B1,B2,B3 from tabel1 inner join tabel2 on A1=B1 where A3='90000' and A2>'2007-12-31'
这条语句跟你给的数据
得到的记录是有相当多的重复记录啊
你加上distinct 就过滤的有重复的记录了
但是用distinct会少查询数据 2008-1-1 90000 10001 AAA 10
2008-1-1 90000 10001 AAA 10 这个数据只会查询出一条来.
你想要什么啊?distinct就是去除重复行,
加了distinct 后就是过滤掉重复的记录了所以就只显示一条记录了
declare @table1 table (a1 nvarchar(10),date datetime,suppliercode int)
insert into @table1
select 10001,'2008-1-1',90000 union all
select 10001,'2008-1-1',90000 union all
select 10001,'2008-1-1',90000 union all
select 10002,'2008-1-1',90000 union all
select 10002,'2008-1-1',90000 union all
select 10002,'2008-1-1',90000select * from @table1declare @table2 table (b1 nvarchar(10),stockcode varchar(3),b3 int)
insert into @table2
select 10001,'AAA',10 union all
select 10001,'AAA',10 union all
select 10001,'AAA',20 union all
select 10002,'BBB',10 union all
select 10002,'BBB',10 union all
select 10002,'BBB',20select * from @table2select a.*,b.* from @table2 a left join
(select distinct * from @table1) b on a.b1=b.a1
/*
b1 stockcode b3 a1 date suppliercode
---------- --------- ----------- ---------- ----------------------- ------------
10001 AAA 10 10001 2008-01-01 00:00:00.000 90000
10001 AAA 10 10001 2008-01-01 00:00:00.000 90000
10001 AAA 20 10001 2008-01-01 00:00:00.000 90000
10002 BBB 10 10002 2008-01-01 00:00:00.000 90000
10002 BBB 10 10002 2008-01-01 00:00:00.000 90000
10002 BBB 20 10002 2008-01-01 00:00:00.000 90000
*/
局部distinct 就行了
go
create table [ta]([a1] int,[a2] datetime,[a3] int)
insert [ta]
select 10001,'2008-1-1',90000 union all
select 10001,'2008-1-1',90000 union all
select 10001,'2008-1-1',90000 union all
select 10002,'2008-1-1',90000 union all
select 10002,'2008-1-1',90000 union all
select 10002,'2008-1-1',90000
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([b1] int,[b2] varchar(3),[b3] int)
insert [tb]
select 10001,'AAA',10 union all
select 10001,'AAA',10 union all
select 10001,'AAA',20 union all
select 10002,'BBB',10 union all
select 10002,'BBB',10 union all
select 10002,'BBB',20
--
--select * from [ta]
--select * from [tb]select a2,a3,a1,b2,b3
from (select distinct a1,a2,a3 from ta where A3='90000' and A2>'2007-12-31') a
join tb b
on a.a1=b.b1
--测试结果:
/*
a2 a3 a1 b2 b3
----------------------- ----------- ----------- ---- -----------
2008-01-01 00:00:00.000 90000 10001 AAA 10
2008-01-01 00:00:00.000 90000 10001 AAA 10
2008-01-01 00:00:00.000 90000 10001 AAA 20
2008-01-01 00:00:00.000 90000 10002 BBB 10
2008-01-01 00:00:00.000 90000 10002 BBB 10
2008-01-01 00:00:00.000 90000 10002 BBB 20(6 行受影响)
*/