mysql - 使用 2 进行一个选择查询，查询在同一个表上进行操作

Question

以 2 个表作为输入 - Employee 和 salary 为

Employee            Salary  
ID  Name            ID  Salary  
1   Anshika         1   1000  
2   Arnav           2   2000  
3   Ishita          3   3000  
4   Iti             4   4000  

将输出显示为--

A       I  
3000    7000 

我写了 2 个查询——

    mysql> select sum(salary) as sumA from salEmp where id in (select id from nameEmp where name LIKE 'A%');  

output  

sumA  
3000

集合中的 1 行(0.00 秒)

    mysql> select sum(salary) as sumI from salEmp where id in (select id from nameEmp where name LIKE 'I%');  

output  

sumI  
7000

集合中的 1 行(0.01 秒)

但我想编写一个查询，以便我可以获得问题中所需的输出。

我试过了——

mysql> select sum(a.salary) as sumA, sum(b.salary) as sumI
->  from salEmp a,salEmp b
->  where a.id in
->          (select id from nameEmp where name LIKE 'A%')
->  AND b.id in
->          (select id from nameEmp where name LIKE 'I%');

但输出如下--

sumA  sumI  
6000  14000  
    1 row in set (0.00 sec) 

值(value)翻倍。
请帮助获得原始输出...

Answer 1

你正在做一个 cross join .所以结果表得到重复值。相反，您可以按照以下查询:

SELECT 
    SUM(CASE WHEN ID IN (SELECT ID FROM nameEmp 
                        WHERE name Like 'A%') THEN Salary ELSE 0 END) AS sumA,
    SUM(CASE WHEN ID IN (SELECT ID FROM nameEmp 
                        WHERE name Like 'I%') THEN Salary ELSE 0 END) AS sumI

FROM salEmp

如果您对此不熟悉，可以从此处了解“CASE WHEN”。

http://www.java2s.com/Code/SQL/Flow-Control/UseCASEWHENstatementinSELECT.htm

http://dev.mysql.com/doc/refman/5.0/en/case.html