根据列选择不同的行组合

您的问题解决方案:

SELECT table1.author1, table1.author2, table1.No_Of_Publications FROM table1
LEFT JOIN table1 as tbl
ON table1.author1 = tbl.author2
AND table1.author2 = tbl.author1
WHERE table1.author1 < tbl.author1
OR tbl.author1 IS NULL

用于演示:

http://sqlfiddle.com/#!9/2263fa/14

请尝试使用以下查询:-

select (case when author1> author2 then author2 else author1 end) as author1
      ,(case when author1> author2 then author1 else author2 end) as author2
      ,publications 
from calc
group by (case when author1> author2 then author2 else author1 end)
,(case when author1> author2 then author1 else author2 end)
,publications

试试这个 https://www.db-fiddle.com/f/buQqnt1YsfoWCuJoMdgVrz/0

CREATE TABLE table1 (
  author1 VARCHAR(100),
  author2 VARCHAR(100),
  publication INT
);

在表1中插入值('a','b',100);

在表1中插入值('b','a',100);

select   (case
  when t.author1>t.author2 then t.author1 else t.author2  END ) as a1 ,
   (case
  when t.author1>t.author2 then t.author2 else t.author1  END ) as a2 , publication

 from table1 as t group by a1, a2, publication;

CREATE TABLE #TEMI1(AUTHOR1 CHAR(3),AUTHOR2 CHAR(3),NO_OF_PUB INT)

INSERT INTO #TEMI1 VALUES
('A','B',115),('B','A',115),('A','C',66),('C','A',66),('D','A',78),('B','C',115)

WITH CTE1 AS(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY NO_OF_PUB ORDER BY NO_OF_PUB) RNUM FROM #TEMI1 )

--SELECT * FROM CTE1 C1
--JOIN CTE1 C2
--ON C1.NO_OF_PUB=C2.NO_OF_PUB AND C1.AUTHOR1=C2.AUTHOR2 AND C1.AUTHOR2=C2.AUTHOR1


DELETE C1 FROM CTE1 C1 
JOIN CTE1 C2
ON C1.NO_OF_PUB=C2.NO_OF_PUB AND C1.AUTHOR1=C2.AUTHOR2 AND C1.AUTHOR2=C2.AUTHOR1
WHERE C1.RNUM>1