sql面试题

select avg(money),address,money from test group by address having avg(money)>=money;

select * from T1 t1 where t1.smoney>(select   distinct avg(t2.smoney) from T1 t2 join T1 t3 on(t2.sprovince=t3.sprovince) where t2.id=t1.id)

Create proc query_t1
@param1 int
as
   select avg(t.smoney),t.sprovince from T1 t join T1 t1 on(t.sprovince=t1.sprovince) where t.id=@param1
go


insert into T(smoney,sprovince) execute query_t1

这个是在知春路那边，我去笔试过，一共5到sql题目 题目出的还可以。答了1个半小时 然后让你回去等消息 

原题大致是这样 合同表 cid主键
cid  Region（区域）   Saler（销售员）  Money(合同金额)
  1         北京           杨建               100
  2         上海           社长               200
  3         杭州           副团               500
  4         上海           社长               200
  5         上海           杨建               400
  6         北京           社长               300
  7         北京           杨建               200
  8         杭州           副团               100


1. 查询每个区域有多少个销售人员并按区域倒叙排列
2. 查询所有相同区域中合同金额最少的区域
3. 查询表中合同金额小于所在区域平均合同金额的合同id

1.
select t.region, count(t.saler)
  from (select region, saler, count(saler)
          from test2
         group by region, saler
         order by region desc) T
group by t.region
2.
select region,min(money) from test2 group by region;
3.
select a.cid
  from test2 a
where money < (select avg(money)
                  from test2 b
                 where a.region = b.region
                 group by region)

1. 查询每个区域有多少个销售人员并按区域倒叙排列
2. 查询所有相同区域中合同金额最少的区域
3. 查询表中合同金额小于所在区域平均合同金额的合同id

1.select c.region,count(c.saler) from contract c group by c.region order by c.region desc
2.select c.region,min(s.money) from contract c group by c.region
3.select c.cid from contract c,(select cc.region,avg(cc.money) money from contract cc group by cc.region) c2 where c.region=c2.region and c.money<c2.money;

大家帮忙看看有没有问题....

Sybase 中可以如下实现
1.select id,name,province from TETONG as t1 where money>(
select avg(money)
from TETONG as t2
where t1.province=t2.province
group by t2.province
)

2.select avg(money) money,province into TETONG_NEW
from TETONG group by province

数据库为mysql5
表名为sales


1:
select id,sname,s.sprovince,avg_money
  from sales s join ( select avg(smon
ey) avg_money,sprovince from sales group by sprovince ) t
  on(s.sprovince=t.sprovince and s.smoney > t.avg_money);

2:
create table avg_money (select avg(smoney) avg_money,sprovince from sales
group by sprovince);

select a.*
from test.sqltest a
where a.smoney >
      (select avg(b.smoney) from test.sqltest b group By a.sprovince);



select avg(a.smoney),a.sprovince into test.test1 from test.sqltest a group by a.sprovince;