Write a Hql-CodePudding

Three fields name1 name2, time
Data
B, 10
A, 20 B
A 30 B
A 40 C
Field means two people talk, talk and talk time, hereby request total duration
The output format for
B, 60
A 40 C
Asked not to use the UDF, could you tell me how to do will be AB and BA in into the same group

CodePudding user response:

SELECT
The CASE WHEN NAME1 & lt;=NAME2 THEN NAME1 ELSE NAME1 NAME2 END,
The CASE WHEN NAME1 & gt; NAME2 THEN NAME1 ELSE NAME2 END NAME2,
The SUM (TIME) TIME
The FROM (
SELECT the 'A' NAME1, 'B' NAME2, 10 TIME FROM DUAL UNION ALL
SELECT the 'A' NAME1, 'B' NAME2, 20 TIME FROM DUAL UNION ALL
SELECT 'B' NAME1, 'A' NAME2, 30 TIME FROM DUAL UNION ALL
SELECT the 'A' NAME1, 'C' NAME2, 40 TIME FROM DUAL) T
GROUP BY
The CASE WHEN NAME1 & lt;=NAME2 THEN NAME1 ELSE NAME2 END,
The CASE WHEN NAME1 & gt; NAME2 THEN NAME1 ELSE NAME2 END
The ORDER BY 1, 2;

CodePudding user response:

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The 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 
- the end of the test data 
The SELECT t.n ame1, t.n ame2, SUM (T3) time) AS the time FROM (
SELECT DISTINCT T1 name1, 
T1. Name2 
The FROM T1 # T 
WHERE NOT the EXISTS (SELECT T2 name1 
The FROM # T T2 
WHERE a T1. Name1=T2. Name2 
AND T1. Name2=T2. Name1 
AND T1. Name1 & gt; T2. Name1)) t 
JOIN the # T T3 ON (T3) name1=t.n ame1 AND T3, name2=t.n ame2) OR (T3) name2=t.n ame1 AND T3. Name1=t.n ame2) 
GROUP BY t.n ame1, t.n ame2

CodePudding user response:

reference 1st floor AHUA1001 response:

SELECT
The CASE WHEN NAME1 & lt;=NAME2 THEN NAME1 ELSE NAME1 NAME2 END,
The CASE WHEN NAME1 & gt; NAME2 THEN NAME1 ELSE NAME2 END NAME2,
The SUM (TIME) TIME
The FROM (
SELECT the 'A' NAME1, 'B' NAME2, 10 TIME FROM DUAL UNION ALL
SELECT the 'A' NAME1, 'B' NAME2, 20 TIME FROM DUAL UNION ALL
SELECT 'B' NAME1, 'A' NAME2, 30 TIME FROM DUAL UNION ALL
SELECT the 'A' NAME1, 'C' NAME2, 40 TIME FROM DUAL) T
GROUP BY
The CASE WHEN NAME1 & lt;=NAME2 THEN NAME1 ELSE NAME2 END,
The CASE WHEN NAME1 & gt; NAME2 THEN NAME1 ELSE NAME2 END
The ORDER BY 1, 2;

I have more than seventy thousand data, so that not too well is dead

CodePudding user response:

reference BackToMeNow reply: 3/f

Quote: refer to 1st floor AHUA1001 response:

SELECT
The CASE WHEN NAME1 & lt;=NAME2 THEN NAME1 ELSE NAME1 NAME2 END,
The CASE WHEN NAME1 & gt; NAME2 THEN NAME1 ELSE NAME2 END NAME2,
The SUM (TIME) TIME
The FROM (
SELECT the 'A' NAME1, 'B' NAME2, 10 TIME FROM DUAL UNION ALL
SELECT the 'A' NAME1, 'B' NAME2, 20 TIME FROM DUAL UNION ALL
SELECT 'B' NAME1, 'A' NAME2, 30 TIME FROM DUAL UNION ALL
SELECT the 'A' NAME1, 'C' NAME2, 40 TIME FROM DUAL) T
GROUP BY
The CASE WHEN NAME1 & lt;=NAME2 THEN NAME1 ELSE NAME2 END,
The CASE WHEN NAME1 & gt; NAME2 THEN NAME1 ELSE NAME2 END
The ORDER BY 1, 2;

I have more than seventy thousand data, so that not too well too dead

More than seventy thousand data, how long to do, what to ask you, or you feel where there may be a problem,

CodePudding user response:

reference 4 floor AHUA1001 response:

Quote: reference BackToMeNow reply: 3/f

Quote: refer to 1st floor AHUA1001 response:

SELECT
The CASE WHEN NAME1 & lt;=NAME2 THEN NAME1 ELSE NAME1 NAME2 END,
The CASE WHEN NAME1 & gt; NAME2 THEN NAME1 ELSE NAME2 END NAME2,
The SUM (TIME) TIME
The FROM (
SELECT the 'A' NAME1, 'B' NAME2, 10 TIME FROM DUAL UNION ALL
SELECT the 'A' NAME1, 'B' NAME2, 20 TIME FROM DUAL UNION ALL
SELECT 'B' NAME1, 'A' NAME2, 30 TIME FROM DUAL UNION ALL
SELECT the 'A' NAME1, 'C' NAME2, 40 TIME FROM DUAL) T
GROUP BY
The CASE WHEN NAME1 & lt;=NAME2 THEN NAME1 ELSE NAME2 END,
The CASE WHEN NAME1 & gt; NAME2 THEN NAME1 ELSE NAME2 END
The ORDER BY 1, 2;

I have more than seventy thousand data, so that not too well too dead

More than seventy thousand data, how long to do, what to ask you, or you feel where there may be a problem,

Union all part of the intermediate data how much how much more will together, I think so, tens of thousands of names, can't write

CodePudding user response:

I suggest to use 2 columns of ASCII value addition, the same is a group, then group by summation