Compare difference between rows and show in next row in mysql

Question

User2027025901 posted

I trying to calculate difference between 2 rows, e/g:

Row1: 1, "Brazil", 3, 4, 5
Row2: 2, "Brazil", 5, 6, 6
Row3: 1, "Brazil", 2, 2, 1

This is the Query example how i compare:

SELECT
    '3' as RowNumber,t1.Country ,t2.col1 -t1.col1 AS Col1Diff ,t2.col2 -t1.col2 AS Col2Diff,t2.col3 -t1.col3 AS Col3Diff 
FROM        (SELECT 1 as RowNumber,'Brazil' as Country,2 As col1,3 As col2,5 As col3)AS T1
INNER JOIN ( SELECT 2 as RowNumber,'Brazil' as Country,5 As col1,6 As col2,6 As col3)AS T2 on T1.Country = T2.Country

The first and sec row was group by result from different table, and i need the third row the show the different. i tried the inner join on same table method, but it does not behave like i wish.

I can only have the different row, and i lost the 1st and sec row. Of course i can select again and UNION back, but considering the performance issue. It just not a good idea.

Does anyone have idea on this ?

I'm using SQL Server 2008 R2

Answer 1

User-271186128 posted

H ittr006,<!--?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" /-->

As for this issue, I suppose you need to create a temporary table and use the ABS function, and perhaps you need to use stored procedure. <o:p></o:p>

From my point of view, you could query the initial table and get the first two lines and insert them into the temporary table. Then query the initial table and use the ABS function to get the third line, and insert it to the temporary table. At last, query the temporary table, and get the desired values.<o:p></o:p>

Here are some relevant articles, you could refer to them.<o:p></o:p>

ABS (Transact-SQL): https://msdn.microsoft.com/en-us/library/ms189800.aspx

Temporary Tables in SQL Server: https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

Best Regards,
Dillion