All geek questions in one place

SELECT call Join multiple rows

I am currently working with an audit log table similar to:

ID | EventTime | AccessID | AccessType | Status | Final ------------------------------------------------------------------------------ 1 | 2013/02/10 01:01:01 | NULL | 123 | 335 | OK 2 | 2013/02/10 01:01:01 | 985521 | NULL | 66 | OK .... 41 | 2013/02/10 07:07:07 | NULL | 456 | 335 | OK 42 | 2013/02/10 07:07:07 | 113228 | NULL | 66 | OK

I need to select AccessID and AccessType on the same line, AccessType .:

 AccessID | AccessType ------------------------ 985521 | 123 113228 | 456

AccessID always unique and is only available on the line where Status = 66 . AccessType is only available where Status = 335 .

EventTime always used for status 335 and 66.

I tried to group the data by the time of the event using sub selects, etc., but I was not able to get the final result that I need.

Additional Information

Approximately 100,000 rows are added per day. For each EventTime, there are approximately 40 rows with different information (the number of columns is greater than the following)

+4
source share
2 answers

Try as follows:

 select max(AccessID) as AccessID, max(AccessType) as AccessType from audit_log where Status in (335,66) group by EventTime
+7
source

This should work too:

 SELECT A1.AccessID, A2.AccessType FROM (SELECT EventTime, AccessID FROM audit_log WHERE Status=66) A1 INNER JOIN (SELECT EventTime, AccessType FROM audit_log WHERE Status=335) A2 ON A1.EventTime=A2.EventTime

You can improve performance if there is an index on status . It was for me with a small test case, but not with a large dataset. However, the implementation plan implies a lower cost. I think it depends on which indexes are available.

+2
source

Source: https://habr.com/ru/post/1490706/

More articles:


All Articles