求一条去重的sql（三个列视为一个整体去重）

[DevIsMyLove]

select PROVINCE_ID,CITY_ID,COUNTY_ID from tbDepartment
这条sql可以查出表tbDepartment中的省市县ID。

比如上条sql查出的结果如下
ID PROVINCE_ID CITY_ID COUNTY_ID
1 1 1000 1001001
2 2 2001 200101
3 1 1000 1001001
4
5

我的要求是将查询结果中重复的去掉，也就是得到的结果应该为->
ID PROVINCE_ID CITY_ID COUNTY_ID
1 1 1000 1001001
2 2 2001 200101
4
也就是将这三列看成一个整体去重。

同时还有一个要求，三个列都为空（指空字符串 而不是null。这三列均为varchar类型）的“不显示”在查询结果中，也就是最终效果应该如下
ID PROVINCE_ID CITY_ID COUNTY_ID
1 1 1000 1001001
2 2 2001 200101

[yinghuigu]

select min(ID)id, PROVINCE_ID,CITY_ID,COUNTY_ID  from tb where isnull(PROVINCE_ID,'')+isnull(CITY_ID,'')+isnull(COUNTY_ID,'')<>'' group by PROVINCE_ID,CITY_ID,COUNTY_ID   

[yy1987316]

select distinct PROVINCE_ID,CITY_ID,COUNTY_ID from tbDepartment where PROVINCE_ID+CITY_ID+COUNTY_ID<>''

[dong_y888]

select min(ID) as id, PROVINCE_ID,CITY_ID,COUNTY_ID from tb where PROVINCE_ID<>'' or CITY_ID<>'' or COUNTY_ID<>'' group by PROVINCE_ID,CITY_ID,COUNTY_ID

[骑着蜗牛去爬山]

select min(ID)id, PROVINCE_ID,CITY_ID,COUNTY_ID from tb group by PROVINCE_ID,CITY_ID,COUNTY_ID

[szm341]

select PROVINCE_ID,CITY_ID,COUNTY_ID from tbDepartment as a
where exists(select * from tbDepartment where a.id=id
group by PROVINCE_ID,CITY_ID,COUNTY_ID
having min(id)=a.id)
and PROVINCE_ID<>'' and CITY_ID<>'' and COUNTY_ID<>''

[百年树人]

select * from tbDepartment t
where 
  not exists(
    select 1 from tbDepartment 
    where (PROVINCE_ID=t.PROVINCE_ID and CITY_ID=t.CITY_ID and COUNTY_ID=t.COUNTY_ID and id<t.id)
    or (id=t.id and PROVINCE_ID is null and CITY_ID is null and COUNTY_ID is null)
)

[百年树人]

if object_id('[tbDepartment]') is not null drop table [tbDepartment]
go
create table [tbDepartment]([ID] int,[PROVINCE_ID] int,[CITY_ID] int,[COUNTY_ID] int)
insert [tbDepartment]
select 1,1,1000,1001001 union all
select 2,2,2001,200101 union all
select 3,1,1000,1001001 union all
select 4,null,null,null union all
select 5,null,null,null
go

-->查询
select * from tbDepartment t
where 
  not exists(
    select 1 from tbDepartment 
    where (PROVINCE_ID=t.PROVINCE_ID and CITY_ID=t.CITY_ID and COUNTY_ID=t.COUNTY_ID and id<t.id)
    or (id=t.id and PROVINCE_ID is null and CITY_ID is null and COUNTY_ID is null)
)

/**
ID          PROVINCE_ID CITY_ID     COUNTY_ID
----------- ----------- ----------- -----------
1           1           1000        1001001
2           2           2001        200101

(2 行受影响)
**/

[骑着蜗牛去爬山]

select min(ID)id, PROVINCE_ID,CITY_ID,COUNTY_ID from tb where not(PROVINCE_ID is null and CITY_ID is null and COUNTY_ID is null) group by PROVINCE_ID,CITY_ID,COUNTY_ID order by id

[DevIsMyLove]

结合上面二位 我写成了如下 select PROVINCE_ID,CITY_ID,COUNTY_ID from "000".tbDepartment where PROVINCE_ID<>'' and CITY_ID<>'' and COUNTY_ID<>'' group by PROVINCE_ID,CITY_ID,COUNTY_ID 存在的问题是，PROVINCE_ID不为空 而 CITY_ID<>为空的 这类数据同样也过滤掉了，我希望只过滤掉三列全为空的 其中一列或两列为空还是要保留的

[DevIsMyLove]

引用 3 楼 josy 的回复:

SQL code?1234567select * from tbDepartment twhere not exists( select 1 from tbDepartment where (PROVINCE_ID=t.PROVINCE_ID and CITY_ID=t.CITY_ID and COUNTY_ID=t.COUNTY_ID and id<t……

我这里的为空指的是空字符串 不知道is null是否可以

[szm341]

select PROVINCE_ID,CITY_ID,COUNTY_ID  from "000".tbDepartment 
       where  not(PROVINCE_ID='' and CITY_ID='' and COUNTY_ID='')
            group by PROVINCE_ID,CITY_ID,COUNTY_ID  

[骑着蜗牛去爬山]

引用 7 楼 DevIsMyLove 的回复:

引用 3 楼 josy 的回复:SQL code?1234567select * from tbDepartment twhere not exists( select 1 from tbDepartment where (PROVINCE_ID=t.PROVINCE_ID and CITY_ID=t.CITY_ID and COUNTY_ID=t.CO……

isnull(列名,'')!=''

[DevIsMyLove]

引用 8 楼 szm341 的回复:

SQL code?123select PROVINCE_ID,CITY_ID,COUNTY_ID from "000".tbDepartment where not(PROVINCE_ID='' and CITY_ID='' and COUNTY_ID='') group by PROVINCE_ID,CITY_ID,COUNTY_……

这个结果是我想要的应该

[百年树人]

引用 7 楼 DevIsMyLove 的回复:

引用 3 楼 josy 的回复:SQL code?1234567select * from tbDepartment twhere not exists( select 1 from tbDepartment where (PROVINCE_ID=t.PROVINCE_ID and CITY_ID=t.CITY_ID and COUNTY_ID=t.CO……

select * from tbDepartment t
where 
  not exists(
    select 1 from tbDepartment 
    where (PROVINCE_ID=t.PROVINCE_ID and CITY_ID=t.CITY_ID and COUNTY_ID=t.COUNTY_ID and id<t.id)
    or (id=t.id and isnull(ltrim(PROVINCE_ID),'')='' and isnull(ltrim(CITY_ID),'')='' and isnull(ltrim(COUNTY_ID),'')='')
)

[csdnTimePeriod]

select distinct PROVINCE_ID,CITY_ID,COUNTY_ID from tbDepartment where not exists （select PROVINCE_ID,CITY_ID,COUNTY_ID from tbDepartment where PROVINCE_ID is null and CITY_ID is null and COUNTY_ID is null)