sqlserver数据库现有sql如下
select dbno_bi XX, cwnames ,zipcode bz6 ,area_name incity,names_bi
from db_bi
where dbno_bi in (select dbno_bi from db_bi group by dbno_bi having count(dbno_bi) <= 1)
db_bi表中 dbno_bi字段有重复的值。想要达到的效果是：去掉dbno_bi重复的整条记录，只保留重复记录中cwnames不为空的那一条 请问该怎么修改这个sql?
------解决方案--------------------
delete from db_bi
where cwnames='' and dbno_bi not in (select dbno_bi from db_bi group by dbno_bi having count(dbno_bi) <= 1)
------解决方案--------------------
select dbno_bi XX,cwnames,zipcode bz6,area_name incity,names_bi
from (select *,row_number() over(partition by dbno_bi order by cwnames desc) 'rn'
from db_bi
where cwnames<>'') t
where t.rn=1
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-20 08:55:07
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not NULL drop table [huang]
go
create table [huang]([dbno_bi] int,[cwnames] nvarchar(4),[zipcode] nvarchar(2),[names_bi] nvarchar(4))
insert [huang]
select 1,'xx','A','DD' union all
select 1,'','D','FF' union all
select 1,'','F','F' union all
select 2,'','D','D' union all
select 3,'F','D','DD' union all
select 3,'','F','FF' union all
select 4,'FF','S','S'
--------------生成数据--------------------------
SELECT dbno_bi , cwnames, zipcode ,names_bi
FROM (
select * ,ROW_NUMBER()OVER (PARTITION BY DBNO_BI ORDER BY CWNAMES DESC )ID
from [huang])A
WHERE ID=1
----------------结果----------------------------
/*
dbno_bi cwnames zipcode names_bi
----------- ------- ------- --------
1 xx A DD
2 D D
3 F D DD
4 FF S S
*/