sql联表查询常用

有三个表：T_USER， sql联表查询常用

t_group， sql联表查询常用

T_USER_GROUP, sql联表查询常用

求分组某个平均数：

[html]view plain copy
3./*各部门平均奖金*/  
select t_group.groupname as "部门", AVG(T_USER.bonus) AS "平均奖金"   
from t_group left join T_USER_GROUP on t_group.groupid = T_USER_GROUP.groupid  
left join T_USER on T_USER_GROUP.userid = T_USER.userid group by t_group.groupname;  

sql联表查询常用

[html]view plain copy
4./*各部门里面奖金大于平均值的职员*/  
  
select a.groupid, a.groupname,a.username,a.bonus from  
 (select t_group.groupid, t_group.groupname,T_USER.username,T_USER.bonus   
from t_group left join T_USER_GROUP on t_group.groupid = T_USER_GROUP.groupid   
left join T_USER on  T_USER.userid = T_USER_GROUP.userid) a   
  
left join   
(select t_group.groupid,t_group.groupname, AVG(T_USER.bonus) AS avgbonus   
from t_group left join T_USER_GROUP on t_group.groupid = T_USER_GROUP.groupid  
left join T_USER on T_USER_GROUP.userid = T_USER.userid group by t_group.groupid,t_group.groupname) b on a.groupid=b.groupid  
  
where a.bonus>b.avgbonus  

sql联表查询常用

[html]view plain copy
5./*各部门里奖金最高（低）的面前三名*/  
SELECT  t1.*  
FROM    (select t_group.groupid,T_USER.userid,T_USER.username,t_group.groupname,T_USER.bonus from   
T_USER_GROUP LEFT JOIN T_USER ON T_USER.userid = T_USER_GROUP.userid LEFT JOIN t_group   
ON t_group.groupid = T_USER_GROUP.groupid) t1  
WHERE   t1.userid IN ( SELECT TOP 3  
                            t2.userid  
                     FROM   (select t_group.groupid,T_USER.username,T_USER.userid,t_group.groupname,T_USER.bonus from   
T_USER_GROUP LEFT JOIN T_USER ON T_USER.userid = T_USER_GROUP.userid LEFT JOIN t_group   
ON t_group.groupid = T_USER_GROUP.groupid) t2  
                     WHERE  t2.groupid = t1.groupid  
                     ORDER BY t2.bonus ASC )  
                     ORDER BY t1.groupid DESC ,t1.bonus asc  

[html]view plain copy
6./*部门平均奖金<400*/  
 select *  
 from  
 (select t_group.groupid as "部门编号",t_group.groupname as "部门名字", AVG(T_USER.bonus) AS "平均奖金"   
from t_group left join T_USER_GROUP on t_group.groupid = T_USER_GROUP.groupid  
left join T_USER on T_USER_GROUP.userid = T_USER.userid group by t_group.groupid,t_group.groupname) b  
where  "平均奖金"<400  

[html]view plain copy
7./*部门平均奖金小于所有人的平均奖金*/    
select b.*  
from (select t_group.groupname as "部门", AVG(T_USER.bonus) AS "平均奖金"   
from t_group left join T_USER_GROUP on t_group.groupid = T_USER_GROUP.groupid  
left join T_USER on T_USER_GROUP.userid = T_USER.userid group by t_group.groupname) b  
  
 join   
(select t_group.groupname as "部门",AVG(T_USER.bonus) as "所有人平均奖金" from T_USER,t_group  group by t_group.groupid,t_group.groupname) a on b.部门=a.部门  
where b.平均奖金<a.所有人平均奖金  

[html]view plain copy
/*求平均奖金小于总平均奖金的部门里面的最高和最低奖金步骤*/   
  
/*第一步所有员工的平均奖金*/  
select AVG(bonus) from T_USER  
  
/*第二步求各部门的平均奖金*/  
select b.groupid,AVG(a.bonus) as avgbonus from T_USER a   
left join T_USER_GROUP b on a.userid=b.userid  
group by groupid  
  
/*第三步求平均值小于总平均值的部门*/  
select s.groupid from(  
select b.groupid,AVG(a.bonus) as avgbonus from T_USER a   
left join T_USER_GROUP b on a.userid=b.userid  
group by groupid  
)s  
where s.avgbonus<(select AVG(bonus) from T_USER)  
  
  
/*第四步求平均值小于总平均值的部门里面的人员的groupid,userid,username,bonus*/  
select tug.groupid,tu.userid,tu.username,tu.bonus from  
(  
select s.groupid from(  
select b.groupid,AVG(a.bonus) as avgbonus from T_USER a   
left join T_USER_GROUP b on a.userid=b.userid  
group by groupid  
)s  
where s.avgbonus<(select AVG(bonus) from T_USER)) p   
inner join T_USER_GROUP tug on tug.groupid=p.groupid  
inner join T_USER tu on tu.userid=tug.userid   
  
/*第五步将第三步得到表的信息存储到虚拟表w中*/  
select f.groupid,f.userid,f.username,f.bonus  into w from  
  
(select tug.groupid,tu.userid,tu.username,tu.bonus from  
(  
select s.groupid from(  
select b.groupid,AVG(a.bonus) as avgbonus from T_USER a   
left join T_USER_GROUP b on a.userid=b.userid  
group by groupid  
)s  
where s.avgbonus<(select AVG(bonus) from T_USER)) p   
inner join T_USER_GROUP tug on tug.groupid=p.groupid  
inner join T_USER tu on tu.userid=tug.userid )f  
  
/*最后查询平均奖金小于总平均值的部门里面的最高和最低奖金获得者的信息*/  
select tr.userid,tr.username,k.maxbonus from (  
select groupid,min(bonus) as maxbonus from w  
group by groupid)k  
inner join T_USER_GROUP tup on tup.groupid=k.groupid  
inner join T_USER tr on tr.userid=tup.userid and tr.bonus=k.maxbonus  

[html]view plain copy
/*查询姓名id为1的职工所在的部门和奖金*/  
select T_USER.userid, t_group.groupid,groupname,bonus   
from T_USER  
inner join T_USER_GROUP on T_USER.userid=T_USER_GROUP.userid  
inner join t_group on T_USER_GROUP.groupid=t_group.groupid  
where T_USER.userid=1  

10.

[html]view plain copy
/*查询生产部门里面奖金大于500的人员的信息，并按照高到低排序*/  
  
select a.groupid,b.userid,username,groupname,bonus   
from t_group a  
left join T_USER_GROUP b on a.groupid=b.groupid  
left join T_USER c on b.userid=c.userid  
where groupname='生产部' and bonus>500  
order by bonus desc  

11.

[html]view plain copy
/*查询id号在8-25之间的职员的所有信息*/  
select groupname,username,bonus  
 from T_USER  
inner join T_USER_GROUP on T_USER.userid=T_USER_GROUP.userid  
inner join t_group on t_group.groupid=T_USER_GROUP.groupid  
where T_USER.userid>8 and T_USER.userid<25  

将min换成max就是求最高奖金，本例子上午关键之处是派生表的灵活运用，例子中的a,b,f,t up ,tr，w,k等都是派生表，灵活使用派生表是数据库中很重要的一个技能，我也还在学习阶段

[html]view plain copy
5./*各部门里奖金最高（低）的面前三名*/  
SELECT  t1.*  
FROM    (select t_group.groupid,T_USER.userid,T_USER.username,t_group.groupname,T_USER.bonus from   
T_USER_GROUP LEFT JOIN T_USER ON T_USER.userid = T_USER_GROUP.userid LEFT JOIN t_group   
ON t_group.groupid = T_USER_GROUP.groupid) t1  
WHERE   t1.userid IN ( SELECT TOP 3  
                            t2.userid  
                     FROM   (select t_group.groupid,T_USER.username,T_USER.userid,t_group.groupname,T_USER.bonus from   
T_USER_GROUP LEFT JOIN T_USER ON T_USER.userid = T_USER_GROUP.userid LEFT JOIN t_group   
ON t_group.groupid = T_USER_GROUP.groupid) t2  
                     WHERE  t2.groupid = t1.groupid  
                     ORDER BY t2.bonus ASC )  
                     ORDER BY t1.groupid DESC ,t1.bonus asc  

[html]view plain copy
6./*部门平均奖金<400*/  
 select *  
 from  
 (select t_group.groupid as "部门编号",t_group.groupname as "部门名字", AVG(T_USER.bonus) AS "平均奖金"   
from t_group left join T_USER_GROUP on t_group.groupid = T_USER_GROUP.groupid  
left join T_USER on T_USER_GROUP.userid = T_USER.userid group by t_group.groupid,t_group.groupname) b  
where  "平均奖金"<400  

[html]view plain copy
7./*部门平均奖金小于所有人的平均奖金*/    
select b.*  
from (select t_group.groupname as "部门", AVG(T_USER.bonus) AS "平均奖金"   
from t_group left join T_USER_GROUP on t_group.groupid = T_USER_GROUP.groupid  
left join T_USER on T_USER_GROUP.userid = T_USER.userid group by t_group.groupname) b  
  
 join   
(select t_group.groupname as "部门",AVG(T_USER.bonus) as "所有人平均奖金" from T_USER,t_group  group by t_group.groupid,t_group.groupname) a on b.部门=a.部门  
where b.平均奖金<a.所有人平均奖金  

[html]view plain copy
/*求平均奖金小于总平均奖金的部门里面的最高和最低奖金步骤*/   
  
/*第一步所有员工的平均奖金*/  
select AVG(bonus) from T_USER  
  
/*第二步求各部门的平均奖金*/  
select b.groupid,AVG(a.bonus) as avgbonus from T_USER a   
left join T_USER_GROUP b on a.userid=b.userid  
group by groupid  
  
/*第三步求平均值小于总平均值的部门*/  
select s.groupid from(  
select b.groupid,AVG(a.bonus) as avgbonus from T_USER a   
left join T_USER_GROUP b on a.userid=b.userid  
group by groupid  
)s  
where s.avgbonus<(select AVG(bonus) from T_USER)  
  
  
/*第四步求平均值小于总平均值的部门里面的人员的groupid,userid,username,bonus*/  
select tug.groupid,tu.userid,tu.username,tu.bonus from  
(  
select s.groupid from(  
select b.groupid,AVG(a.bonus) as avgbonus from T_USER a   
left join T_USER_GROUP b on a.userid=b.userid  
group by groupid  
)s  
where s.avgbonus<(select AVG(bonus) from T_USER)) p   
inner join T_USER_GROUP tug on tug.groupid=p.groupid  
inner join T_USER tu on tu.userid=tug.userid   
  
/*第五步将第三步得到表的信息存储到虚拟表w中*/  
select f.groupid,f.userid,f.username,f.bonus  into w from  
  
(select tug.groupid,tu.userid,tu.username,tu.bonus from  
(  
select s.groupid from(  
select b.groupid,AVG(a.bonus) as avgbonus from T_USER a   
left join T_USER_GROUP b on a.userid=b.userid  
group by groupid  
)s  
where s.avgbonus<(select AVG(bonus) from T_USER)) p   
inner join T_USER_GROUP tug on tug.groupid=p.groupid  
inner join T_USER tu on tu.userid=tug.userid )f  
  
/*最后查询平均奖金小于总平均值的部门里面的最高和最低奖金获得者的信息*/  
select tr.userid,tr.username,k.maxbonus from (  
select groupid,min(bonus) as maxbonus from w  
group by groupid)k  
inner join T_USER_GROUP tup on tup.groupid=k.groupid  
inner join T_USER tr on tr.userid=tup.userid and tr.bonus=k.maxbonus  

[html]view plain copy
/*查询姓名id为1的职工所在的部门和奖金*/  
select T_USER.userid, t_group.groupid,groupname,bonus   
from T_USER  
inner join T_USER_GROUP on T_USER.userid=T_USER_GROUP.userid  
inner join t_group on T_USER_GROUP.groupid=t_group.groupid  
where T_USER.userid=1  

10.

[html]view plain copy
/*查询生产部门里面奖金大于500的人员的信息，并按照高到低排序*/  
  
select a.groupid,b.userid,username,groupname,bonus   
from t_group a  
left join T_USER_GROUP b on a.groupid=b.groupid  
left join T_USER c on b.userid=c.userid  
where groupname='生产部' and bonus>500  
order by bonus desc  

11.

[html]view plain copy
/*查询id号在8-25之间的职员的所有信息*/  
select groupname,username,bonus  
 from T_USER  
inner join T_USER_GROUP on T_USER.userid=T_USER_GROUP.userid  
inner join t_group on t_group.groupid=T_USER_GROUP.groupid  
where T_USER.userid>8 and T_USER.userid<25  

相关推荐