Subconsulta Mysql

Asked

Viewed 3,289 times

2

I have an employee table that has only three columns, NOME, CARGO and SALARIO, I want to assemble a query that brings me all this table and contains in it the highest salary value for the post. Totaling 4 Columns (NOME, CARGO, SALARIO and MAIOR_SALARIO_P_CARGO. This is to check the variation.

select-sql

  • Give more information. Which table is the fourth field. What is the relationship between them?

  • Tip: To edit this question, use the [Edit] link above.

2 answers

3

SELECT
    T1.NOME,
    T1.CARGO,
    T1.SALARIO_ATUAL,
    (
        SELECT MAX(SALARIO_ATUAL)
        FROM TABELA T2
        WHERE T2.CARGO = T1.CARGO
    ) AS MAIOR_SALARIO_PARA_CARGO
FROM TABELA T1

2

Answers:

Subquery

SELECT a.nome, a.cargo, a.salario, (select max(salario) from tbsalarios b where b.cargo = a.cargo) maiorsalario FROM tbsalarios a

JOIN

SELECT a.nome, 
       a.cargo, 
       a.salario, 
       b.maior 
FROM tbsalarios a 
LEFT JOIN 
      (select max(salario) maior, cargo from tbsalarios group by cargo) as b on b.cargo = a.cargo

Browser other questions tagged select-sql

You are not signed in. Login or sign up in order to post.