Select不重复数据 SQL 语句
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 113/1 113 1 113 1 100017 100017 T
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 122/1 290 3 290 1 100049 100049 C
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
我需要的是 select的时候, 不需要重复的landing, 结果为(任选一个重复去掉)
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 113 1 113 1 100017 100017 T
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 290 1 100049 100049 C
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
谢谢大家
------解决方案--------------------参考 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'