一句SQL问题

来源：百度知道 编辑：UC知道 时间：2024/05/26 03:30:36
有两张表员工表和销售情况表
employee表 province省份，city城市，pop为0时按省份计算为1时按城市计算
id province city pop
----------- ---------- ---------- -----------
1 zj hz 0
2 zj hz 0
3 zj hz 1
4 zj hz 1
5 zj nb 0
6 zj nb 1
7 js nj 0
8 js nj 1
sale表 employee_id员工id，sale销售情况
employee_id sale
----------- -----------
1 50
1 30
1 10
2 20
2 20
3 10
3 30
4 10
5 10
6 30
7 30
8 30
8 30
现在知道一个employee_id如何查询他所在省份或城市的总销售额
若pop为0则查询该省份的若pop为1则查询该城市的
希望能1句sql写出
比如
（1）.若id为1，则pop=0那么查找整个province='zj&

SELECT DISTINCT (CASE
WHEN (SELECT DISTINCT e.pop
FROM employee e, sale s
WHERE e.id = s.employee_id
AND s.employee_id = 所查employee_id) = 0 THEN
(SELECT DISTINCT sum(s.sale)
FROM sale s
WHERE s.employee_id IN
(SELECT e.id
FROM employee e
WHERE e.province =
(SELECT DISTINCT e.province
FROM employee e, sale s
WHERE e.id = s.employee_id
AND s.employee_id = 所查employee_id)))
ELSE
(SELECT DISTINCT sum(s.sale)
FROM sale s
WHERE s.employe