去除数据库重复的保留一条记录，其他不改变_tp6.0 jion 指定字段去重保留最新一条-优快云博客

本文探讨了一种方法，通过使用ROW_NUMBER()函数优化查询过程，以减少重复数据并提高效率。详细解释了如何在特定情况下仅显示唯一记录，并提供了SQL示例来实现这一目标。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这是查询语句：

select e.id,e.UserName,e.MobilePhone,e.QQ,e.AlipayAccount,e.[Address],e.Remarks,
(select Name from QaQuestion where id=e.QuestionId) Question,
(select Name from QaOptions where id=e.OptionId) Answer
from (select b.id,a.UserName,a.MobilePhone,a.QQ,a.AlipayAccount,
a.[Address],a.Remarks,b.QuestionId,b.OptionId
from QaUserInfo a left join QaAnswer b on a.id=b.userid
where a.SubjectId=5 AND (QQ IS NOT NULL))
e

这是查询结果：

修改后的查询语句：

select id,
CASE when row=1 THEN UserName ELSE '' END UserName,
CASE when row=1 THEN MobilePhone ELSE '' END MobilePhone,
CASE when row=1 THEN QQ ELSE '' END QQ,
CASE when row=1 THEN AlipayAccount ELSE '' END AlipayAccount,
CASE when row=1 THEN [Address] ELSE '' END [Address],
CASE when row=1 THEN Remarks ELSE '' END Remarks,
(select Name from QaQuestion where id=QuestionId) Question,
(select Name from QaOptions where id=OptionId) Answer
from (
SELECT *,row_number()OVER(PARTITION BY UserName,MobilePhone,QQ,AlipayAccount,[Address],Remarks ORDER BY GETDATE())row
FROM (select b.id,a.UserName,a.MobilePhone,a.QQ,a.AlipayAccount,
a.[Address],a.Remarks,b.QuestionId,b.OptionId
from QaUserInfo a left join QaAnswer b on a.id=b.userid
where a.SubjectId=5 AND (QQ IS NOT NULL))r
)m order by id

查询结果：

这是网上参考的例子：

如上图，HeadersNo ,MachineNo,Description ,如果有行的这三列相同，那么这三列只需要显示一行数据。

解决办法：：：

CREATE TABLE #tp

(

    headerNo VARCHAR(10),

    machineNO VARCHAR(10),

    descrption nVARCHAR(20),   

    artNo  VARCHAR(20),       

    qty INT ,

    repartno varchar(20)   
 ,

    repqty INT

)

insert INTO #tp SELECT 'HD01','0101520',N'电池出问题','102020',2,'102020',2

insert INTO #tp SELECT 'HD01','0101520',N'电池出问题','101010',2,'202020',2

insert INTO #tp SELECT 'HD01','0101520',N'电池出问题','126888',2,'102020',2

insert INTO #tp SELECT 'HD02','01012221',N'D电机故障','102020',2,'102020',2

insert INTO #tp SELECT 'HD03','12312312',N'突然停机','102020',2,'102020',2

insert INTO #tp SELECT 'HD03','12312312',N'突然停机','102020',2,'102020',2

insert INTO #tp SELECT 'HD04','12312344',N'皮带松了','102020',2,'102020',2

SELECT CASE when row=1 THEN headerNo ELSE '' END headerNo,

       CASE when row=1 THEN machineNO ELSE '' END machineNO,

       CASE when row=1 THEN descrption ELSE '' END descrption

       ,artNo,qty,repartno,repqty

FROM 

(

SELECT  *,row_number()OVER(PARTITION BY headerNo,machineNO,descrption ORDER BY GETDATE())row 

FROM #tp

)M

headerNo   machineNO  descrption           artNo                qty         repartno             repqty

---------- ---------- -------------------- -------------------- ----------- -------------------- -----------

HD01       0101520    电池出问题                102020               2           102020               2

                                           101010               2           202020               2

                                           126888               2           102020               2

HD02       01012221   D电机故障                102020               2           102020               2

HD03       12312312   突然停机                 102020               2           102020               2

                                           102020               2           102020               2

HD04       12312344   皮带松了                 102020               2           102020               2

(7 row(s) affected)