sql面试题

 

id	sname	smoney	sprovince
1	zhangsan	2098	A
2	lisi	3000	B
3	wangwu	6789	C
4	liumazi	4587	C
5	dongjiu	3298	B
6	shiga	4567	A

 

 

id:合同id  sname:姓名     smoney :业绩     sprovince:地区

 

第一道：显示出  业绩 大于同一地区平均值的 合同id  姓名 地区 业绩

 

 

第二道：把同一地区的  平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）

 

 

请写出sql语句

 

评论

27 楼 zhanghaocool 2010-02-10  

thinkaw 写道

第一道
SELECT A.ID, A.SNAME, A.SPROVINCE, A.SMONEY
      FROM TT A
     WHERE SMONEY > (SELECT SUM(B.SMONEY) / COUNT(1)
                       FROM TT B
                      WHERE B.SPROVINCE = A.SPROVINCE);
第二道
CREATE TABLE TT_SUM
    AS
    SELECT SPROVINCE, SUM(SMONEY)/COUNT(1) AVG_PRO
      FROM TT
    GROUP BY SPROVINCE;

相关子查询
子查询建表

26 楼 gowish 2010-01-29  

MySQL 5.1通过：
1.SELECT b.smoney,b.sprovince FROM test.biz_tbl b
inner join
(select avg(smoney) as avgMoney,sprovince as sprovinceId from test.biz_tbl a group by sprovince) c
on
b.sprovince = c.sprovinceId
and
b.smoney > c.avgMoney

2.insert into test.avgmoney_tbl (avgmoney,sprovince) SELECT b.smoney,b.sprovince FROM test.biz_tbl b
inner join
(select avg(smoney) as avgMoney,sprovince as sprovinceId from test.biz_tbl a group by sprovince) c
on
b.sprovince = c.sprovinceId
and
b.smoney > c.avgMoney

25 楼 cartonwang 2010-01-28  

cartonwang 写道

第一道：显示出  业绩 大于同一地区平均值的 合同id  姓名 地区 业绩
select t2.id,t2.name,t2.province,t2.money, t1.avg_money
       from t_employee t2, (select t.province, avg(t.money)   avg_money
                           from t_employee t
                           group by t.province) t1
       where t2.province = t1.province
       and t2.money > t1.avg_money
      
第二道：把同一地区的  平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）

      
      
create table t_employee_avg as (select avg(e.money) avg_money, e.province from t_employee e group by e.province);

-----------------------
看见去年我的回复了，一年过去了，我也开始找工作了。祝福自己能找到自己满意的工作吧！

24 楼 thinkaw 2010-01-06  

第一道：显示出 业绩 大于同一地区平均值的 合同id  姓名 地区 业绩

SELECT A.ID, A.SNAME, A.SPROVINCE, A.SMONEY
      FROM TT A
     WHERE SMONEY > (SELECT SUM(B.SMONEY) / COUNT(1)
                       FROM TT B
                      WHERE B.SPROVINCE = A.SPROVINCE);

            ID SNAME SP SMONEY
    ---------- -------------------- -- ----------

             3 WANGWU  C 6789
             5 DONGJIU B 3298
             6 SHIGA   A 4567

第二道：把同一地区的 平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）
CREATE TABLE TT_SUM
    AS
    SELECT SPROVINCE, SUM(SMONEY)/COUNT(1) AVG_PRO
      FROM TT
    GROUP BY SPROVINCE;

    SQL> select * from tt_sum;

    SP AVG_PRO
    -- ----------

    A 3332.5
    B 3149
    C 5688

注：自己扩展了几道问题（五道），欢迎浏览。
http://blog.chinaunix.net/u1/53979/showart.php?id=2142515

23 楼 wqmgxj 2010-01-03  

多老的帖子了

22 楼 duqiangcise 2010-01-02  

select a.id,a.sname,a.sprovince,b.avg_money
from table_a a,(
select avg(smoney) avg_money,sprovince
from table
group by sprovince
)b
where a.sprovince=b.sprovince
and a.smoney >b.avg_money

/*
新表table_已存在时的情况,如果新表不存在则按照楼上那些朋友的方法
create
tabe_b(avg_money,sprovince)
*/
insert into tabe_b values(
select avg(smoney),sprovince
from table
group by sprovince
);

21 楼 liran_java 2009-12-29  

明显送分题嘛

20 楼 danny.chiu 2009-11-30  

select t.* from table_name t1 join
( select sprovince, avg(smoney) avg_smoney from table_name group by sprovince ) t2
on (t1.sprovince = t2.sprovince and t1.smoney > t2.avg_smoney);

create table new_table_name as
select avg_smoney, sprovince from
(select avg(smoney) avg_smoney, sprovince from table_name group by sprovince);

19 楼 wxj76 2009-11-10  

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;

18 楼 zghaofeng 2009-03-21  

数据库为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);

17 楼 qinglin876 2009-03-19  

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

16 楼 rainerliu 2009-03-09  

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;

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

15 楼 pengjian0924 2009-03-08  

xialiang19851204 写道

这个是在知春路那边，我去笔试过，一共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)

14 楼 xialiang19851204 2009-03-03  

这个是在知春路那边，我去笔试过，一共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

13 楼 shenglongq 2009-03-02  

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

12 楼 shenglongq 2009-03-02  

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)

11 楼 a56165993 2009-03-02  

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

10 楼 benpaolang 2009-03-02  

9 楼 nwpufw 2009-03-02  

1:
select A.*
from test A
where A.smoney > (select avg(B.smoney)
                  from test B
                  where B.sprovince = B.sprovince)

"第二道：把同一地区的  平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）"
2.题目中要求是向新表中插入，所以这个表是存在的。不防取名新表名为newsell，字段“平均业绩 地区”为“smoney,sprovince”.
insert into  newsell (smoney,sprovince)
select avg(smoney)avgmoney,sprovince
from sell group by sprovince;

8 楼 cartonwang 2009-03-01  

第一道：显示出  业绩 大于同一地区平均值的 合同id  姓名 地区 业绩
select t2.id,t2.name,t2.province,t2.money, t1.avg_money
       from t_employee t2, (select t.province, avg(t.money)   avg_money
                           from t_employee t
                           group by t.province) t1
       where t2.province = t1.province
       and t2.money > t1.avg_money
      
第二道：把同一地区的  平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）

      
      
create table t_employee_avg as (select avg(e.money) avg_money, e.province from t_employee e group by e.province);