如果删除表中组合字段的重复记录

Question

大概这样，你试试：----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-05-06 15:20:05
-- 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)
--
----------------------------------------------------------------
--> 测试数据[B_Valve]
if object_id('[B_Valve]') is not null drop table [B_Valve]
go 
create table [B_Valve]([Valve_ID] int,[Valve_Dim] int,[Valve_kind] nvarchar(4),[Valve_type] nvarchar(4),[Valve_press] numeric(2,1),[Valve_price] numeric(3,1),[Year] int)
insert [B_Valve]
select 1,500,N'重锤',N'蝶阀',null,3.5,2014 union all
select 2,600,N'重锤',N'蝶阀',null,4,2014 union all
select 3,700,N'重锤',N'蝶阀',null,4.4,2014 union all
select 4,800,N'重锤',N'蝶阀',null,5.5,2014 union all
select 5,1000,N'重锤',N'蝶阀',null,7.2,2014 union all
select 6,1200,N'重锤',N'蝶阀',null,13,2014 union all
select 7,1250,N'重锤',N'蝶阀',null,15,2014 union all
select 8,1500,N'重锤',N'蝶阀',null,19,2014 union all
select 9,500,N'电动',N'蝶阀',0.6,3,2004 union all
select 10,600,N'电动',N'蝶阀',0.6,4,2004 union all
select 11,700,N'电动',N'蝶阀',0.6,5,2004 union all
select 12,800,N'电动',N'蝶阀',0.6,6,2004 union all
select 13,1000,N'电动',N'蝶阀',0.6,8,2004 union all
select 14,1200,N'电动',N'蝶阀',0.6,11,2004 union all
select 15,1250,N'电动',N'蝶阀',0.6,14,2004 union all
select 16,1500,N'电动',N'蝶阀',0.6,19,2004 union all
select 17,1750,N'电动',N'蝶阀',0.6,24,2004 union all
select 18,500,N'液压',N'蝶阀',0.6,3,2004 union all
select 19,600,N'液压',N'蝶阀',0.6,4,2004 union all
select 20,700,N'液压',N'蝶阀',0.6,6,2004 union all
select 21,800,N'液压',N'蝶阀',0.6,7,2004 union all
select 22,1000,N'液压',N'蝶阀',0.6,9,2004 union all
select 23,1200,N'液压',N'蝶阀',0.6,12,2004 union all
select 24,1250,N'液压',N'蝶阀',0.6,15,2004 union all
select 25,1500,N'液压',N'蝶阀',0.6,21,2004 union all
select 26,1750,N'液压',N'蝶阀',0.6,27,2004
--------------生成数据--------------------------
SELECT *
FROM [B_Valve] a
WHERE EXISTS (SELECT 1 FROM (
select [Valve_Dim],[Year],MAX([Valve_price])[Valve_price]
from [B_Valve]
WHERE [Valve_type] IN (N'蝶阀',N'球阀')
GROUP BY [Valve_Dim],[Year]) b WHERE a.valve_dim=b.valve_dim AND a.[year]=b.[year] AND a.[Valve_price]=b.[Valve_price])
AND  [Valve_type] IN (N'蝶阀',N'球阀')
----------------结果----------------------------
/* 
Valve_ID    Valve_Dim   Valve_kind Valve_type Valve_press                             Valve_price                             Year
----------- ----------- ---------- ---------- --------------------------------------- --------------------------------------- -----------
1           500         重锤         蝶阀         NULL                                    3.5                                     2014
2           600         重锤         蝶阀         NULL                                    4.0                                     2014
3           700         重锤         蝶阀         NULL                                    4.4                                     2014
4           800         重锤         蝶阀         NULL                                    5.5                                     2014
5           1000        重锤         蝶阀         NULL                                    7.2                                     2014
6           1200        重锤         蝶阀         NULL                                    13.0                                    2014
7           1250        重锤         蝶阀         NULL                                    15.0                                    2014
8           1500        重锤         蝶阀         NULL                                    19.0                                    2014
9           500         电动         蝶阀         0.6                                     3.0                                     2004
10          600         电动         蝶阀         0.6                                     4.0                                     2004
18          500         液压         蝶阀         0.6                                     3.0                                     2004
19          600         液压         蝶阀         0.6                                     4.0                                     2004
20          700         液压         蝶阀         0.6                                     6.0                                     2004
21          800         液压         蝶阀         0.6                                     7.0                                     2004
22          1000        液压         蝶阀         0.6                                     9.0                                     2004
23          1200        液压         蝶阀         0.6                                     12.0                                    2004
24          1250        液压         蝶阀         0.6                                     15.0                                    2004
25          1500        液压         蝶阀         0.6                                     21.0                                    2004
26          1750        液压         蝶阀         0.6                                     27.0                                    2004*/

Answer 1

但是就我这个代码跟测试数据来看，valve_dim没有相同的，所以不算重复，你的结果是怎样的贴来看看？

Answer 2

需要的结果如下：
500 重锤 蝶阀  NULL 3.5 2014
600 液压 蝶阀 1 6 2004
700 液压 蝶阀 1.6 8 2014
800 液压 蝶阀 1.6 10 2014
300 NULL 球阀 10 12 2004
400 NULL 球阀 10 15 2004
500 NULL 球阀 10 19 2004
600 NULL 球阀 10 31 2004
...
我是用Excel表的筛选功能挨个直径选择、比较，手工录入的，现在只有40种组合还可以手工，如果日后组合到几百的时候简直就是折磨人

Answer 3

  500         重锤         蝶阀         NULL                                    3.5                                     2014
           600         重锤         蝶阀         NULL                                    4.0                                     2014
           700         重锤         蝶阀         NULL                                    4.4  我数据里面前三行应该满足你的要求的啊

Answer 4

并且你的第一行数据：500 重锤 蝶阀  NULL 3.5 2014 这个3.5也并不是价格最高

Answer 5

现在我们假设去掉 表中的两个字段Valve_kind   Valve_press ，只看Valve_Dim   和 Valve_type 那么就可以看到大量的重复记录。
现在只要 这两个字段组合后，需要离现在最近，并且Valve_price 最大的，且无重复的一个，仅仅是一个记录。
本人 SQL不好，实在不知道用SQL怎么表述，请见谅。

Answer 6

由于Valve_Dim   的印象，所以会有“重复值，你先看我这个写法有没有问题，没有的话再改”----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-05-06 15:20:05
-- 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)
--
----------------------------------------------------------------
--> 测试数据[B_Valve]
if object_id('[B_Valve]') is not null drop table [B_Valve]
go 
create table [B_Valve]([Valve_ID] int,[Valve_Dim] int,[Valve_kind] nvarchar(4),[Valve_type] nvarchar(4),[Valve_press] numeric(2,1),[Valve_price] numeric(3,1),[Year] int)
insert [B_Valve]
select 1,500,N'重锤',N'蝶阀',null,3.5,2014 union all
select 2,600,N'重锤',N'蝶阀',null,4,2014 union all
select 3,700,N'重锤',N'蝶阀',null,4.4,2014 union all
select 4,800,N'重锤',N'蝶阀',null,5.5,2014 union all
select 5,1000,N'重锤',N'蝶阀',null,7.2,2014 union all
select 6,1200,N'重锤',N'蝶阀',null,13,2014 union all
select 7,1250,N'重锤',N'蝶阀',null,15,2014 union all
select 8,1500,N'重锤',N'蝶阀',null,19,2014 union all
select 9,500,N'电动',N'蝶阀',0.6,3,2004 union all
select 10,600,N'电动',N'蝶阀',0.6,4,2004 union all
select 11,700,N'电动',N'蝶阀',0.6,5,2004 union all
select 12,800,N'电动',N'蝶阀',0.6,6,2004 union all
select 13,1000,N'电动',N'蝶阀',0.6,8,2004 union all
select 14,1200,N'电动',N'蝶阀',0.6,11,2004 union all
select 15,1250,N'电动',N'蝶阀',0.6,14,2004 union all
select 16,1500,N'电动',N'蝶阀',0.6,19,2004 union all
select 17,1750,N'电动',N'蝶阀',0.6,24,2004 union all
select 18,500,N'液压',N'蝶阀',0.6,3,2004 union all
select 19,600,N'液压',N'蝶阀',0.6,4,2004 union all
select 20,700,N'液压',N'蝶阀',0.6,6,2004 union all
select 21,800,N'液压',N'蝶阀',0.6,7,2004 union all
select 22,1000,N'液压',N'蝶阀',0.6,9,2004 union all
select 23,1200,N'液压',N'蝶阀',0.6,12,2004 union all
select 24,1250,N'液压',N'蝶阀',0.6,15,2004 union all
select 25,1500,N'液压',N'蝶阀',0.6,21,2004 union all
select 26,1750,N'液压',N'蝶阀',0.6,27,2004
--------------生成数据--------------------------
--SELECT *
--FROM [B_Valve] a
--WHERE EXISTS (SELECT 1 FROM (
--select [Valve_Dim],[Year],MAX([Valve_price])[Valve_price]
--from [B_Valve]
--WHERE [Valve_type] IN (N'蝶阀',N'球阀')
--GROUP BY [Valve_Dim],[Year]) b WHERE a.valve_dim=b.valve_dim AND a.[year]=b.[year] AND a.[Valve_price]=b.[Valve_price])
--AND  [Valve_type] IN (N'蝶阀',N'球阀')select [Valve_Dim],[Valve_type],MAX([year])[year],MAX([Valve_price])[Valve_price]
from [B_Valve]
WHERE [Valve_type] IN (N'蝶阀',N'球阀')
GROUP BY [Valve_Dim],[Valve_type]
----------------结果----------------------------
/* 
Valve_Dim   Valve_type year        Valve_price
----------- ---------- ----------- ---------------------------------------
500         蝶阀         2014        3.5
600         蝶阀         2014        4.0
700         蝶阀         2014        6.0
800         蝶阀         2014        7.0
1000        蝶阀         2014        9.0
1200        蝶阀         2014        13.0
1250        蝶阀         2014        15.0
1500        蝶阀         2014        21.0
1750        蝶阀         2004        27.0*/
  

Answer 7

对结果就是这样，
但是能否加上另外一个字段Valve_kind 和ID信息，可能存在 符合条件的记录(如 蝶阀)中有多种形式 如重锤、电动、液压的一样的价格，只需要给出符合条件的任意一种形式（不求多解，只求有一个解）就可以

Answer 8

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-05-06 15:20:05
-- 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)
--
----------------------------------------------------------------
--> 测试数据[B_Valve]
if object_id('[B_Valve]') is not null drop table [B_Valve]
go 
create table [B_Valve]([Valve_ID] int,[Valve_Dim] int,[Valve_kind] nvarchar(4),[Valve_type] nvarchar(4),[Valve_press] numeric(2,1),[Valve_price] numeric(3,1),[Year] int)
insert [B_Valve]
select 1,500,N'重锤',N'蝶阀',null,3.5,2014 union all
select 2,600,N'重锤',N'蝶阀',null,4,2014 union all
select 3,700,N'重锤',N'蝶阀',null,4.4,2014 union all
select 4,800,N'重锤',N'蝶阀',null,5.5,2014 union all
select 5,1000,N'重锤',N'蝶阀',null,7.2,2014 union all
select 6,1200,N'重锤',N'蝶阀',null,13,2014 union all
select 7,1250,N'重锤',N'蝶阀',null,15,2014 union all
select 8,1500,N'重锤',N'蝶阀',null,19,2014 union all
select 9,500,N'电动',N'蝶阀',0.6,3,2004 union all
select 10,600,N'电动',N'蝶阀',0.6,4,2004 union all
select 11,700,N'电动',N'蝶阀',0.6,5,2004 union all
select 12,800,N'电动',N'蝶阀',0.6,6,2004 union all
select 13,1000,N'电动',N'蝶阀',0.6,8,2004 union all
select 14,1200,N'电动',N'蝶阀',0.6,11,2004 union all
select 15,1250,N'电动',N'蝶阀',0.6,14,2004 union all
select 16,1500,N'电动',N'蝶阀',0.6,19,2004 union all
select 17,1750,N'电动',N'蝶阀',0.6,24,2004 union all
select 18,500,N'液压',N'蝶阀',0.6,3,2004 union all
select 19,600,N'液压',N'蝶阀',0.6,4,2004 union all
select 20,700,N'液压',N'蝶阀',0.6,6,2004 union all
select 21,800,N'液压',N'蝶阀',0.6,7,2004 union all
select 22,1000,N'液压',N'蝶阀',0.6,9,2004 union all
select 23,1200,N'液压',N'蝶阀',0.6,12,2004 union all
select 24,1250,N'液压',N'蝶阀',0.6,15,2004 union all
select 25,1500,N'液压',N'蝶阀',0.6,21,2004 union all
select 26,1750,N'液压',N'蝶阀',0.6,27,2004
--------------生成数据--------------------------
SELECT * 
FROM [B_Valve] a
WHERE EXISTS (SELECT 1 FROM (
select [Valve_Dim],[Valve_type],MAX([year])[year],MAX([Valve_price])[Valve_price]
from [B_Valve]
WHERE [Valve_type] IN (N'蝶阀',N'球阀')
GROUP BY [Valve_Dim],[Valve_type]) b WHERE a.[Valve_Dim]=b.[Valve_Dim] AND a.[Valve_type]=b.[Valve_type] AND a.[year]=b.[year] AND a.[Valve_price]=b.[Valve_price])
----------------结果----------------------------
/* 
Valve_ID    Valve_Dim   Valve_kind Valve_type Valve_press                             Valve_price                             Year
----------- ----------- ---------- ---------- --------------------------------------- --------------------------------------- -----------
1           500         重锤         蝶阀         NULL                                    3.5                                     2014
2           600         重锤         蝶阀         NULL                                    4.0                                     2014
6           1200        重锤         蝶阀         NULL                                    13.0                                    2014
7           1250        重锤         蝶阀         NULL                                    15.0                                    2014
26          1750        液压         蝶阀         0.6                                     27.0                                    2004*/

Answer 9

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-05-06 15:20:05
-- 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)
--
----------------------------------------------------------------
--> 测试数据[B_Valve]
if object_id('[B_Valve]') is not null drop table [B_Valve]
go 
create table [B_Valve]([Valve_ID] int,[Valve_Dim] int,[Valve_kind] nvarchar(4),[Valve_type] nvarchar(4),[Valve_press] numeric(2,1),[Valve_price] numeric(3,1),[Year] int)
insert [B_Valve]
select 1,500,N'重锤',N'蝶阀',null,3.5,2014 union all
select 2,600,N'重锤',N'蝶阀',null,4,2014 union all
select 3,700,N'重锤',N'蝶阀',null,4.4,2014 union all
select 4,800,N'重锤',N'蝶阀',null,5.5,2014 union all
select 5,1000,N'重锤',N'蝶阀',null,7.2,2014 union all
select 6,1200,N'重锤',N'蝶阀',null,13,2014 union all
select 7,1250,N'重锤',N'蝶阀',null,15,2014 union all
select 8,1500,N'重锤',N'蝶阀',null,19,2014 union all
select 9,500,N'电动',N'蝶阀',0.6,3,2004 union all
select 10,600,N'电动',N'蝶阀',0.6,4,2004 union all
select 11,700,N'电动',N'蝶阀',0.6,5,2004 union all
select 12,800,N'电动',N'蝶阀',0.6,6,2004 union all
select 13,1000,N'电动',N'蝶阀',0.6,8,2004 union all
select 14,1200,N'电动',N'蝶阀',0.6,11,2004 union all
select 15,1250,N'电动',N'蝶阀',0.6,14,2004 union all
select 16,1500,N'电动',N'蝶阀',0.6,19,2004 union all
select 17,1750,N'电动',N'蝶阀',0.6,24,2004 union all
select 18,500,N'液压',N'蝶阀',0.6,3,2004 union all
select 19,600,N'液压',N'蝶阀',0.6,4,2004 union all
select 20,700,N'液压',N'蝶阀',0.6,6,2004 union all
select 21,800,N'液压',N'蝶阀',0.6,7,2004 union all
select 22,1000,N'液压',N'蝶阀',0.6,9,2004 union all
select 23,1200,N'液压',N'蝶阀',0.6,12,2004 union all
select 24,1250,N'液压',N'蝶阀',0.6,15,2004 union all
select 25,1500,N'液压',N'蝶阀',0.6,21,2004 union all
select 26,1750,N'液压',N'蝶阀',0.6,27,2004
--------------生成数据--------------------------
SELECT MAX([Valve_ID])[Valve_ID],[Valve_Dim],[Valve_type],[year],MAX([Valve_price])[Valve_price]
FROM [B_Valve] a
WHERE EXISTS (SELECT 1 FROM (
select [Valve_Dim],[Valve_type],MAX([year])[year],MAX([Valve_price])[Valve_price]
from [B_Valve]
WHERE [Valve_type] IN (N'蝶阀',N'球阀')
GROUP BY [Valve_Dim],[Valve_type]) b WHERE a.[Valve_Dim]=b.[Valve_Dim] AND a.[Valve_type]=b.[Valve_type] AND a.[year]=b.[year])
GROUP BY [Valve_Dim],[Valve_type],[year]
----------------结果----------------------------
/* 
Valve_ID    Valve_Dim   Valve_type year        Valve_price
----------- ----------- ---------- ----------- ---------------------------------------
1           500         蝶阀         2014        3.5
2           600         蝶阀         2014        4.0
3           700         蝶阀         2014        4.4
4           800         蝶阀         2014        5.5
5           1000        蝶阀         2014        7.2
6           1200        蝶阀         2014        13.0
7           1250        蝶阀         2014        15.0
8           1500        蝶阀         2014        19.0
26          1750        蝶阀         2004        27.0
*/

Answer 10

SELECT MAX([Valve_ID])[Valve_ID],[Valve_Dim],[Valve_type],[valve_kind],[year],MAX([Valve_price])[Valve_price]
FROM [a_Valve] a
WHERE EXISTS (SELECT 1 FROM (
select [Valve_Dim],[Valve_type],MAX([year])[year],MAX([Valve_price])[Valve_price]
from [a_Valve]
WHERE [Valve_type] IN (N'蝶阀',N'球阀')
GROUP BY [Valve_Dim],[Valve_type]) b WHERE a.[Valve_Dim]=b.[Valve_Dim] AND a.[Valve_type]=b.[Valve_type] AND a.[year]=b.[year])
GROUP BY [Valve_Dim],[Valve_type],[year],[valve_kind]
我理解后的修改 ，增加了KIND
结果如下：
Valve_ID    Valve_Dim   Valve_type Valve_kind year        Valve_price
124 300 球阀 NULL 2004 12
125 400 球阀 NULL 2004 15
144 500 蝶阀 重锤 2014 3.5
126 500 球阀 NULL 2004 19
145 600 蝶阀 重锤 2014 4
127 600 球阀 NULL 2004 31
128 650 球阀 NULL 2004 38
151 700 蝶阀 重锤 2014 4.4
129 700 球阀 NULL 2004 44
146 800 蝶阀 重锤 2014 6
130 800 球阀 NULL 2004 59
131 900 球阀 NULL 2004 80
147 1000 蝶阀 重锤 2014 7.2
132 1000 球阀 NULL 2004 101
133 1100 球阀 NULL 2004 117
148 1200 蝶阀 重锤 2014 13
134 1200 球阀 NULL 2004 134
149 1250 蝶阀 重锤 2014 15
135 1300 球阀 NULL 2004 155
136 1400 球阀 NULL 2004 176
150 1500 蝶阀 重锤 2014 19
137 1500 球阀 NULL 2004 202
138 1600 球阀 NULL 2004 228
45 1750 蝶阀 电动 2004 32
54 1750 蝶阀 液压 2004 36
81 2000 蝶阀 液压 2004 47
82 2250 蝶阀 液压 2004 58
83 2500 蝶阀 液压 2004 72
84 2800 蝶阀 液压 2004 96
85 3000 蝶阀 液压 2004 112
142 3200 蝶阀 电动 2013 62
141 3200 蝶阀 液压 2013 72
87 3400 蝶阀 液压 2004 147
88 3600 蝶阀 液压 2004 158
89 3800 蝶阀 液压 2004 170
90 4000 蝶阀 液压 2004 183
91 4200 蝶阀 液压 2004 202
92 4400 蝶阀 液压 2004 224
93 4600 蝶阀 液压 2004 248通过和版主的沟通，我提高不少，谢谢