sql去重复查询最小的一条数据

[accp_soft3]

如图有一张表，我想在ArriveCode重复的情况下，只保留NFare最下的那条记录，请问大侠们该怎么写？

[發糞塗牆]

用row_number，然后选第一条就可以了

[accp_soft3]

我想在ArriveCode相同的情况下，取NFare最小的那条 2楼的同志方法不错，正是我想要的结果，分就给你吧，另外也很感谢每一位回复的网友！！！！

[Mr_Nice]

select ＊ from TB A where not exists(select 1 from TB B where A.ArriveCode = B.ArriveCode and A.Fromcode <B.fromcode)

[發糞塗牆]

SELECT id,nfare,fromcode,arriveCode
FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY arriveCode ORDER BY GETDATE())oid
FROM TB )a
WHERE oid=2

例子比较特殊，不知道你的重复值有多少个，我这里做了两个假设： 1、重复值最多2个。 2、你的数据没有任何排序，如果有，上面代码中的order by的列就按照你的排序字段来写

[志起计算机编程]

select id from YourTable group by ArriveCode order by NFare

不是很理解你的意思，这条语句是实现找出每一个ArriveCode对应的最小NFare值得ID，希望能帮到你

[志起计算机编程]

引用 3 楼 u012999424 的回复:

select top 1 id from YourTable group by ArriveCode order by NFare

不是很理解你的意思，这条语句是实现找出每一个ArriveCode对应的最小NFare值得ID，希望能帮到你

忘了加top 1

[--小F--]

select * from tb t where NFare=(select min(NFare) from tb where arriveCode=t.arriveCode)

[jiajiaren]

IF OBJECT_ID(N'Information',N'U') IS NOT NULL
DROP TABLE  Information

GO

CREATE TABLE Information(ID INT PRIMARY KEY,NFare FLOAT,FromCode VARCHAR(10),Arrivecode VARCHAR(10))
INSERT INTO Information 


SELECT '171056','240.00','NAY','BAV'
UNION ALL SELECT '176373','680.00','NAY','CAN'
UNION ALL SELECT '171219','380.00','PEK','CGQ'
UNION ALL SELECT '169603','180.00','NAY','CIF'
UNION ALL SELECT '170115','270.00','PEK','CIH'
UNION ALL SELECT '167895','300.00','NAY','CIH'
UNION ALL SELECT '168235','500.00','PEK','CKG'
 

----------------------------------查询-----------------------------------------------------------------------
 
SELECT  MIN(ID) ID,MIN(NFare) NFare,MIN(FromCode) FromCode ,Arrivecode FROM Information GROUP BY Arrivecode

---------------------------------结果----------------------------------------------------------------------

/*
(7 行受影响)
ID          NFare                  FromCode   Arrivecode
----------- ---------------------- ---------- ----------
171056      240                    NAY        BAV
176373      680                    NAY        CAN
171219      380                    PEK        CGQ
169603      180                    NAY        CIF
167895      270                    NAY        CIH
168235      500                    PEK        CKG

(6 行受影响)

 */
 
 
 
 

[jiajiaren]

引用 6 楼 jiajiaren 的回复:

IF OBJECT_ID(N'Information',N'U') IS NOT NULL
DROP TABLE  Information

GO

CREATE TABLE Information(ID INT PRIMARY KEY,NFare FLOAT,FromCode VARCHAR(10),Arrivecode VARCHAR(10))
INSERT INTO Information 


SELECT '171056','240.00','NAY','BAV'
UNION ALL SELECT '176373','680.00','NAY','CAN'
UNION ALL SELECT '171219','380.00','PEK','CGQ'
UNION ALL SELECT '169603','180.00','NAY','CIF'
UNION ALL SELECT '170115','270.00','PEK','CIH'
UNION ALL SELECT '167895','300.00','NAY','CIH'
UNION ALL SELECT '168235','500.00','PEK','CKG'
 

----------------------------------查询-----------------------------------------------------------------------
 
SELECT  MIN(ID) ID,MIN(NFare) NFare,MIN(FromCode) FromCode ,Arrivecode FROM Information GROUP BY Arrivecode

---------------------------------结果----------------------------------------------------------------------

/*
(7 行受影响)
ID          NFare                  FromCode   Arrivecode
----------- ---------------------- ---------- ----------
171056      240                    NAY        BAV
176373      680                    NAY        CAN
171219      380                    PEK        CGQ
169603      180                    NAY        CIF
167895      270                    NAY        CIH
168235      500                    PEK        CKG

(6 行受影响)

 */
 
 
 
 

上面有误：

 SELECT * FROM Information a INNER JOIN 
 
(SELECT  MIN(NFare) NFare,Arrivecode FROM Information GROUP BY Arrivecode) b

ON  a.NFare=b.NFare AND a.Arrivecode=b.Arrivecode

[accp_soft3]

五楼的同志，你的方法我试了，可以去重，但是在两条记录的NFare相等的情况下，就不行了，我想在NFare相等的情况下，也只保留一条 谢谢你的关注

[过_往]

都是大神啊。。。