有表A(aa,bb)如下:
aa bb
1 a
1 a
1 a
2 b
2 b
2 b
3 c
3 c
3 c
3 d
4 d
4 d
4 e
5 f
5 f
5 f也就是aa,bb均不是唯一.
现需以下结果:
如果aa相同,则计算bb:当bb相同就只计算一次,bb不同就分开计算
比如:
1 a
1 a
1 a
2 b
2 b
2 b
结果应该是:
1 a
2 b
但是,当aa相同,计算bb时,bb不同,则要分开计算
比如:
3 c
3 c
3 c
3 d
结果应该是:
3 c
3 d
又当aa不同时,bb的值相互不影响
比如
3 d
4 d
结果应该是:
3 d
4 d最终结果应该是:
1 a
2 b
3 c
3 d
4 d
4 e
5 f
可能没表述清楚...囧...
aa bb
1 a
1 a
1 a
2 b
2 b
2 b
3 c
3 c
3 c
3 d
4 d
4 d
4 e
5 f
5 f
5 f也就是aa,bb均不是唯一.
现需以下结果:
如果aa相同,则计算bb:当bb相同就只计算一次,bb不同就分开计算
比如:
1 a
1 a
1 a
2 b
2 b
2 b
结果应该是:
1 a
2 b
但是,当aa相同,计算bb时,bb不同,则要分开计算
比如:
3 c
3 c
3 c
3 d
结果应该是:
3 c
3 d
又当aa不同时,bb的值相互不影响
比如
3 d
4 d
结果应该是:
3 d
4 d最终结果应该是:
1 a
2 b
3 c
3 d
4 d
4 e
5 f
可能没表述清楚...囧...
-- 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 distinct aa,bb from [tb]
----------------结果----------------------------
/*
aa bb
----------- ----
1 a
2 b
3 c
3 d
4 d
4 e
5 f(所影响的行数为 7 行)
*/
-- 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 行)
*/
有表A(aa,bb)如下:
aa bb
1 a
1 a
1 a
1 b
2 b
2 b
结果应该是:
1 a
1 b
2 b
不信你自己去测试下
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 table1.aa,表.bb
from table1 join 表
on table1.aa= 表.aa
from tb
select table1.aa,k.bb
from table1 left join (select distinct * from 表) k
on table1.aa= k.aa
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
*/