大神来帮帮新手吧，一个SQL两表查询的问题

Question

table store
store_id       city                space
wh1           new yourk            370
wh2           beijing              500
wh3           new york             200
wh4           london               400table employee
store_id           emp_id               salary
wh1                    e1                 1220
wh1                    e3                 1210
wh2                    e4                 1250
wh2                    e6                  1230
wh3                    e7                  1300
wh3                    e8                  1240
wh4                    e9                  1350
wh4                    e11                1280question1:get the employees who are paid the max salary for each city.(can create temporary tables)quetion2:get the total salary,the total space,the number of store,the number of employee and maximum salary of every city.show the results as following layout.（can create temorary tables)City    Tot.Salary    Tot.Space    #store    #employee    Max Salary
...         ...           ...       ...         ...          ...

Answer 1

1.
create table employee(store_id varchar(10),emp_id varchar(10),salary int)
insert into employee select 'wh1','e1',1220
insert into employee select 'wh1','e3',1210
insert into employee select 'wh2','e4',1250
insert into employee select 'wh2','e6',1230
insert into employee select 'wh3','e7',1300
insert into employee select 'wh3','e8',1240
insert into employee select 'wh4','e9',1350
insert into employee select 'wh4','e11',1280
go
select * from employee a where not exists(select 1 from employee where store_id=a.store_id and salary>a.salary)
/*
store_id   emp_id     salary
---------- ---------- -----------
wh1        e1         1220
wh2        e4         1250
wh3        e7         1300
wh4        e9         1350(4 行受影响)
*/

Answer 2

select 
  *
from
  tb t
where
  salary=(select max(salary) from tb where store_id=t.store_id )

Answer 3

--question1
with t as
(
select t1.city,t2.emp_id,t2.Salary
from store t1,
     employee t2
Where t1.store_id=t2.store_id
)
select t1.*
from t t1
where not exists(select 1 from t t2 where t1.city=t2.city and t1.Salary<t2.Salary)

Answer 4

--question2
with a as
(
select t1.city,t1.space,t2.store_id,t2.emp_id,t2.Salary
from store t1,
     employee t2
Where t1.store_id=t2.store_id
)
select t1.city,
TotSalary=(select sum(t2.Salary) from a t2 where t2.city=t1.city),
Totspace=(select sum(t3.space) from a t3 where t3.city=t1.city),
numstore=t1.store_id,
numemployee=t1.emp_id
MaxSalary=t1.Salary
from a t1
where not exists(select 1 from a t4 where t1.city=t4.city and t1.Salary<t4.Salary)

Answer 5

qestion2.
SELECT  A.[city], SUM(B.[salary]) 'Tot.salary', SUM(A.[space]) 'Tot.space',
COUNT(DISTINCT A.[store_id]) '#store',COUNT(DISTINCT B.[emp_id]) '#employee', MAX(B.[salary]) 'Max Salary' FROM store A INNER JOIN employee B ON A.[store_id]=B.[store_id]
GROUP BY A.[city]