问题:

I have the following table:

For Presenters

and looking to get an output as follow:

Example

I tried doing an inner join, but think got a bit confused as I'm still rather new with SQL

I got to display 2 names from the same table, but unsure where to place the comparison of the salary,

Here is my code where I tried:

select 
    t1.PNRFNAME, t2.PNRFNAME, t1.SALARY_YEARLY
from 
    PRESENTERS t1
inner join 
    PRESENTERS t2 on t1.PRESENTER_ID = t2.PRESENTER_ID
order by
    t1.SALARY_YEARLY DESC

回答1:

Just add a column called Salary_Diff:

select t1.PNRFNAME,t2.PNRFNAME, t1.SALARY_YEARLY, 

t1.SALARY_YEARLY - t2.SALARY_YEARLY AS Salary_Diff

from PRESENTERS t1
cross join PRESENTERS t2 
where t1.presenterid <> t2.presenterid
ORDER BY t1.SALARY_YEARLY DESC

回答2:

Try this:

CREATE TABLE #PRESENTERS(PRESENTERID VARCHAR(20), PNRLNAME VARCHAR(25), PNRFNAME VARCHAR(25), AGE INT, YEAR INT , SALARY_YEARLY NUMERIC(11,2))

INSERT INTO #PRESENTERS

SELECT 'EMP_01','NEEDELL','TIFF',61,10,37451000.7            UNION ALL
SELECT 'EMP_02','BUTLER-HENDERSON','VICKI',41,10,2426222.55  UNION ALL
SELECT 'EMP_03','PLATO','JASON',45,8,2910000.98


;WITH A
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY PRESENTERID DESC) RN, *
FROM #PRESENTERS
)
SELECT A.PNRFNAME , A2.PNRFNAME, A.SALARY_YEARLY - A2.SALARY_YEARLY  SALARY_DIFF
FROM A
INNER JOIN A A2 ON A.RN < A2.RN