想要一个删除重复数据的SQL语句，希望大家帮帮忙
id name school addr code
1 Jack SYB JEROAD NULL
2 JACK1 SYB NULL 0097
3 JACK2 SYB JEROAD 0097
4 Jack SYB JEROAD 11
5 Jack SYB JEROAD 11
6 Jack SYB MCROAD 11
9 Jack SYB JEROAD NULL
10 Jack SYB JEROAD NULL
11 Jack NULL JEROAD NULL
表的数据是这样，id自增，name不为空，现在我想删除name重复的数据，剩下的应该是这样：
2 JACK1 SYB NULL 0097
3 JACK2 SYB JEROAD 0097
4 Jack SYB JEROAD 11
依据：
这个结果就是去掉name重复，并且根据school，addr，code来排序（有空值优先排除），根据这个条件剩下结果就是id为2,3,4,5,6，
456中取id最小的4，结果就是上面了。
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-26 19:19:06
-- Version:
-- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
-- Jun 28 2012 08:36:30
-- 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]([id] int,[name] nvarchar(10),[school] nvarchar(6),[addr] nvarchar(12),[code] nvarchar(8))
insert [huang]
select 1,'Jack','SYB','JEROAD',null union all
select 2,'JACK1','SYB',null,'0097' union all
select 3,'JACK2','SYB','JEROAD','0097' union all
select 4,'Jack','SYB','JEROAD','11' union all
select 5,'Jack','SYB','JEROAD','11' union all
select 6,'Jack','SYB','MCROAD','11' union all
select 9,'Jack','SYB','JEROAD',null union all
select 10,'Jack','SYB','JEROAD',null union all
select 11,'Jack',null,'JEROAD',null
--------------生成数据--------------------------
--查询
SELECT id,name,school,addr ,code
FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY name ORDER BY [school] DESC ,addr ,code DESC )id2
FROM huang)a
WHERE id2=1
--删除
;WITH cte as
(
SELECT id,name,school,addr ,code
FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY name ORDER BY [school] DESC ,addr ,code DESC )id2
FROM huang)a
WHERE id2=1
)
DELETE FROM huang WHERE id NOT IN (SELECT id FROM cte)
SELECT * FROM huang
----------------结果----------------------------
/*
id name school addr code
----------- ---------- ------ ------------ --------
4 Jack SYB JEROAD 11
2 JACK1 SYB NULL 0097
3 JACK2 SYB JEROAD 0097
(3 row(s) affected)
(6 row(s) affected)
id name school addr code
----------- ---------- ------ ------------ --------
2 JACK1 SYB NULL 0097
3 JACK2 SYB JEROAD 0097
4 Jack SYB JEROAD 11
*/