写一个Hql

[BackToMeNow]

三个字段name1,name2,time 数据 A B 10 A B 20 B A 30 A C 40 字段含义为两人通话，及通话时间，现要求通话总时长 即输出格式为 A B 60 A C 40 要求不使用UDF，请问如何做到将AB,BA划分到同一组中

[AHUA1001]

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

[AHUA1001]

引用 3 楼 BackToMeNow 的回复:

[quote=引用 1 楼 AHUA1001 的回复:] 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 ;

我有七万多条数据，这样不太好吧太死了[/quote] 七万多条数据，要执行多久。还有什么要求吗。或者您觉得哪里可能会有问题。

[BackToMeNow]

引用 4 楼 AHUA1001 的回复:

[quote=引用 3 楼 BackToMeNow 的回复:] [quote=引用 1 楼 AHUA1001 的回复:] 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 ;

我有七万多条数据，这样不太好吧太死了[/quote] 七万多条数据，要执行多久。还有什么要求吗。或者您觉得哪里可能会有问题。[/quote] 中间的union all 部分，数据多多少也要连起来多少，我认为是这样的，几万个名字，没办法写的

[BackToMeNow]

引用 1 楼 AHUA1001 的回复:

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 ;

我有七万多条数据，这样不太好吧太死了

[sxq129601]

我建议用2列的ascii值相加，一样的就是一组，然后group by 求和