Select不重复数据 SQL 语句
Forest, Block , landing, harvest, setting, no, no2, no3, no4, type
KWKAKURIPAPA1/11111100186100200C
KWKAKURIPAPA111/129921111100017100017T
KWKAKURIPAPA112/188111121100017100017T
KWKAKURIPAPA113/118021811100062100062C
KWKAKURIPAPA113/111311131100017100017T
KWKAKURIPAPA116/188111161100017100017T
KWKAKURIPAPA117/188111171100017100017T
KWKAKURIPAPA119/129031191100017100017T
KWKAKURIPAPA120/1290101201100017100017T
KWKAKURIPAPA120/2290172901100049100049C
KWKAKURIPAPA122/129031221100017100017T
KWKAKURIPAPA122/129032901100049100049C
KWKAKURIPAPA127/12127141271100070100070C
KWKAKURIPAPA127/13151201511100172100172R
KWKAKURIPAPA127/20127101271100070100070C
KWKAKURIPAPA127/2112713A1271100064100064C
KWKAKURIPAPA127/22127221271100068100068C
KWKAKURIPAPA127/23127231271100070100070C
KWKAKURIPAPA127/24127251271100068100068C
KWKAKURIPAPA127/512751271100070100070C
我需要的是 select的时候， 不需要重复的landing， 结果为（任选一个重复去掉）
Forest, Block , landing, harvest, setting, no, no2, no3, no4, type
KWKAKURIPAPA1/11111100186100200C
KWKAKURIPAPA111/129921111100017100017T
KWKAKURIPAPA112/188111121100017100017T
KWKAKURIPAPA113/111311131100017100017T
KWKAKURIPAPA116/188111161100017100017T
KWKAKURIPAPA117/188111171100017100017T
KWKAKURIPAPA119/129031191100017100017T
KWKAKURIPAPA120/1290101201100017100017T
KWKAKURIPAPA120/2290172901100049100049C
KWKAKURIPAPA122/129032901100049100049C
KWKAKURIPAPA127/12127141271100070100070C
KWKAKURIPAPA127/13151201511100172100172R
KWKAKURIPAPA127/20127101271100070100070C
KWKAKURIPAPA127/2112713A1271100064100064C
KWKAKURIPAPA127/22127221271100068100068C
KWKAKURIPAPA127/23127231271100070100070C
KWKAKURIPAPA127/24127251271100068100068C
KWKAKURIPAPA127/512751271100070100070C
谢谢大家
[解决办法]
参考 http://bbs.csdn.net/topics/240034273
[解决办法]
任选一个简单吧，max、min都行，group by landing就完了吗
[解决办法]
select * from tb as a where exists
(select 1 from tb where Forest=a.Forest and Block=a.Block and landing=a.landing
group by Forest,Block,landing
having max(no3)=a.no3)
[解决办法]
USE test
GO
-->生成表tb
if object_id('tb') is not null
drop table tb
Go
Create table tb([Forest] nvarchar(4),[Block] nvarchar(8),[landing] nvarchar(6),[harvest] smallint,[setting] nvarchar(3),[no] smallint,[no2] smallint,[no3] int,[no4] int,[type] nvarchar(1))
Insert into tb
Select N'KWKA',N'KURIPAPA',N'1/1',1,N'1',1,1,100186,100200,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'111/1',299,N'2',111,1,100017,100017,N'T'
Union all Select N'KWKA',N'KURIPAPA',N'112/1',881,N'1',112,1,100017,100017,N'T'
Union all Select N'KWKA',N'KURIPAPA',N'113/1',180,N'2',181,1,100062,100062,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'113/1',113,N'1',113,1,100017,100017,N'T'
Union all Select N'KWKA',N'KURIPAPA',N'116/1',881,N'1',116,1,100017,100017,N'T'
Union all Select N'KWKA',N'KURIPAPA',N'117/1',881,N'1',117,1,100017,100017,N'T'
Union all Select N'KWKA',N'KURIPAPA',N'119/1',290,N'3',119,1,100017,100017,N'T'
Union all Select N'KWKA',N'KURIPAPA',N'120/1',290,N'10',120,1,100017,100017,N'T'
Union all Select N'KWKA',N'KURIPAPA',N'120/2',290,N'17',290,1,100049,100049,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'122/1',290,N'3',122,1,100017,100017,N'T'
Union all Select N'KWKA',N'KURIPAPA',N'122/1',290,N'3',290,1,100049,100049,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'127/12',127,N'14',127,1,100070,100070,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'127/13',151,N'20',151,1,100172,100172,N'R'
Union all Select N'KWKA',N'KURIPAPA',N'127/20',127,N'10',127,1,100070,100070,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'127/21',127,N'13A',127,1,100064,100064,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'127/22',127,N'22',127,1,100068,100068,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'127/23',127,N'23',127,1,100070,100070,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'127/24',127,N'25',127,1,100068,100068,N'C'
Union all Select N'KWKA',N'KURIPAPA',N'127/5',127,N'5',127,1,100070,100070,N'C'
SELECT
Forest
,Block
,landing
,harvest
,setting
,no
,no2
,no3
,no4
,type
FROM
(SELECT
ROW_NUMBER()OVER(PARTITION BY [Forest],[Block],[landing] ORDER BY GETDATE()/*根據需求設置排序規則，取第一條*/)AS row
,*
FROM tb
) AS t
WHERE row=1
Go
SELECT * FROM tb
/*
Forest Block landing harvest setting no no2 no3 no4 type
------ -------- ------- ------- ------- ------ ------ ----------- ----------- ----
KWKA KURIPAPA 1/1 1 1 1 1 100186 100200 C
KWKA KURIPAPA 111/1 299 2 111 1 100017 100017 T
KWKA KURIPAPA 112/1 881 1 112 1 100017 100017 T
KWKA KURIPAPA 113/1 180 2 181 1 100062 100062 C
KWKA KURIPAPA 116/1 881 1 116 1 100017 100017 T
KWKA KURIPAPA 117/1 881 1 117 1 100017 100017 T
KWKA KURIPAPA 119/1 290 3 119 1 100017 100017 T
KWKA KURIPAPA 120/1 290 10 120 1 100017 100017 T
KWKA KURIPAPA 120/2 290 17 290 1 100049 100049 C
KWKA KURIPAPA 122/1 290 3 122 1 100017 100017 T
KWKA KURIPAPA 127/12 127 14 127 1 100070 100070 C
KWKA KURIPAPA 127/13 151 20 151 1 100172 100172 R
KWKA KURIPAPA 127/20 127 10 127 1 100070 100070 C
KWKA KURIPAPA 127/21 127 13A 127 1 100064 100064 C
KWKA KURIPAPA 127/22 127 22 127 1 100068 100068 C
KWKA KURIPAPA 127/23 127 23 127 1 100070 100070 C
KWKA KURIPAPA 127/24 127 25 127 1 100068 100068 C
KWKA KURIPAPA 127/5 127 5 127 1 100070 100070 C
*/
