去除数据库重复的保留一条记录，其他不改变

这是查询语句：

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)