SELECT CASE WHEN NAME1<=NAME2 THEN NAME1 ELSE NAME2 END NAME1, CASE WHEN NAME1>NAME2 THEN NAME1 ELSE NAME2 END NAME2, SUM(TIME) TIME FROM ( SELECT 'A' NAME1,'B' NAME2,10 TIME FROM DUAL UNION ALL SELECT 'A' NAME1,'B' NAME2,20 TIME FROM DUAL UNION ALL SELECT 'B' NAME1,'A' NAME2,30 TIME FROM DUAL UNION ALL SELECT 'A' NAME1,'C' NAME2,40 TIME FROM DUAL ) T GROUP BY CASE WHEN NAME1<=NAME2 THEN NAME1 ELSE NAME2 END , CASE WHEN NAME1>NAME2 THEN NAME1 ELSE NAME2 END ORDER BY 1,2 ;
--测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([name1] nvarchar(21),[name2] nvarchar(21),[time] int) Insert #T select N'A',N'B',10 union all select N'A',N'B',20 union all select N'B',N'A',30 union all select N'A',N'C',40 Go --测试数据结束 SELECT t.name1,t.name2,SUM(T3.time) AS time FROM ( SELECT DISTINCT T1.name1, T1.name2 FROM #T T1 WHERE NOT EXISTS ( SELECT T2.name1 FROM #T T2 WHERE T1.name1 = T2.name2 AND T1.name2 = T2.name1 AND T1.name1 > T2.name1))t JOIN #T T3 ON (T3.name1 = t.name1 AND T3.name2 = t.name2) OR (T3.name2 = t.name1 AND T3.name1 = t.name2) GROUP BY t.name1,t.name2
我有七万多条数据,这样不太好吧太死了七万多条数据,要执行多久。还有什么要求吗。或者您觉得哪里可能会有问题。 中间的union all 部分,数据多多少也要连起来多少,我认为是这样的,几万个名字,没办法写的
CASE WHEN NAME1<=NAME2 THEN NAME1 ELSE NAME2 END NAME1,
CASE WHEN NAME1>NAME2 THEN NAME1 ELSE NAME2 END NAME2,
SUM(TIME) TIME
FROM (
SELECT 'A' NAME1,'B' NAME2,10 TIME FROM DUAL UNION ALL
SELECT 'A' NAME1,'B' NAME2,20 TIME FROM DUAL UNION ALL
SELECT 'B' NAME1,'A' NAME2,30 TIME FROM DUAL UNION ALL
SELECT 'A' NAME1,'C' NAME2,40 TIME FROM DUAL ) T
GROUP BY
CASE WHEN NAME1<=NAME2 THEN NAME1 ELSE NAME2 END ,
CASE WHEN NAME1>NAME2 THEN NAME1 ELSE NAME2 END
ORDER BY 1,2 ;
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([name1] nvarchar(21),[name2] nvarchar(21),[time] int)
Insert #T
select N'A',N'B',10 union all
select N'A',N'B',20 union all
select N'B',N'A',30 union all
select N'A',N'C',40
Go
--测试数据结束
SELECT t.name1,t.name2,SUM(T3.time) AS time FROM (
SELECT DISTINCT T1.name1,
T1.name2
FROM #T T1
WHERE NOT EXISTS ( SELECT T2.name1
FROM #T T2
WHERE T1.name1 = T2.name2
AND T1.name2 = T2.name1
AND T1.name1 > T2.name1))t
JOIN #T T3 ON (T3.name1 = t.name1 AND T3.name2 = t.name2) OR (T3.name2 = t.name1 AND T3.name1 = t.name2)
GROUP BY t.name1,t.name2
中间的union all 部分,数据多多少也要连起来多少,我认为是这样的,几万个名字,没办法写的