34,587
社区成员
发帖
与我相关
我的任务
分享
if object_id('[tb]') is not null
drop table [tb]
go
create table [tb]([aa] int,[bb] varchar(1))
insert [tb]
select 1,'a' union all
select 1,'a' union all
select 1,'a' union all
select 1,'b' union all
select 2,'b' union all
select 2,'b'
go
if object_id('table1') is not null
drop table table1
go
create table table1([aa] int)
insert table1
select 1 union all select 2 union all select 3
go
select table1.aa,k.bb
from table1 left join (select distinct * from tb) k
on table1.aa= k.aa
/*
aa bb
----------- ----
1 a
1 b
2 b
3 NULL
*/
--下面正常的链接
select table1.aa,k.bb
from table1 join (select distinct * from tb) k
on table1.aa= k.aa
/*\
aa bb
----------- ----
1 a
1 b
2 b
*/
select table1.aa,k.bb
from table1 left join (select distinct * from 表) k
on table1.aa= k.aa
select distinct aa,bb
from tb
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-10 11:10:05
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([aa] int,[bb] varchar(1))
insert [tb]
select 1,'a' union all
select 1,'a' union all
select 1,'a' union all
select 1,'b' union all
select 2,'b' union all
select 2,'b'
--------------开始查询--------------------------
select distinct aa,bb from [tb]
----------------结果----------------------------
/*aa bb
----------- ----
1 a
1 b
2 b
(所影响的行数为 3 行)
*/
SELECT DISTINCT AA,BB FROM TB
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-10 10:58:02
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([aa] int,[bb] varchar(1))
insert [tb]
select 1,'a' union all
select 1,'a' union all
select 1,'a' union all
select 2,'b' union all
select 2,'b' union all
select 2,'b' union all
select 3,'c' union all
select 3,'c' union all
select 3,'c' union all
select 3,'d' union all
select 4,'d' union all
select 4,'d' union all
select 4,'e' union all
select 5,'f' union all
select 5,'f' union all
select 5,'f'
--------------开始查询--------------------------
select aa,bb from [tb] group by aa,bb
----------------结果----------------------------
/*
aa bb
----------- ----
1 a
2 b
3 c
3 d
4 d
4 e
5 f
(所影响的行数为 7 行)
*/