sql学习！！请教

Question

工资下降
select a.personname ,b.imonth1,b.imonth2 from 
persona inner join 
(select id ,
       imonth1=sum(case imonth when 1 then isnull(salary,0) end),
       imonth2=sum(case imonth when 2 then isnull(salary,0) end)
from salary group by id ) b
on a.id=b.id and b.imonth1>b.imonth2工资上涨
select a.personname ,b.imonth1,b.imonth2 from 
persona inner join 
(select id ,
       imonth1=sum(case imonth when 1 then isnull(salary,0) end),
       imonth2=sum(case imonth when 2 then isnull(salary,0) end)
from salary group by id ) b
on a.id=b.id and b.imonth1<b.imonth2

Answer 1

所有人工资
select a.personname ,b.imonth1,b.imonth2 from 
student a inner join 
(select id ,
       imonth1=sum(case imonth when 1 then isnull(salary,0) end),
       imonth2=sum(case imonth when 2 then isnull(salary,0) end)
from salary group by id ) b
on a.id=b.id 

Answer 2

5  漏了合计
select a.personname ,b.imonth1,b.imonth2 ,合计=isnull(b.imonth1,0)+isnull(b.imonth2,0) from 
student a inner join 
(select id ,
       imonth1=sum(case imonth when 1 then isnull(salary,0) end),
       imonth2=sum(case imonth when 2 then isnull(salary,0) end)
from salary group by id ) b
on a.id=b.id 

Answer 3

10
select personname from person where 
personname not in
(select a.personname from student a,salary b
where a.id=b.id and b.imonth=1 )
9和5差不多 把合计列去掉就OK6
select a.personname,
       b.salary 
from person a
inner join 
(select id,sum(salary) salary from salary group by id) b
on a.id=b.id  
order by salary asc7select a.personname,
       b.salary 
from person a
inner join 
(select id,sum(salary) salary from salary group by id) b
on a.id=b.id  
order by salary desc
我的方法不是很好 -。- 等高手来写简洁的 呵呵

Answer 4

8.按月份来查
select t3.id,t3.personcode,t3.personname,t3.imonth,t3.salary,(select count(1) from 
(
select t1.id,t1.personcode,t1.personname,t2.imonth,t2.salary from
person t1 inner join salary t2 on  t1.id = t2.id 
)t4 where salary < t3.salary and imonth = t3.imonth
) as 人数 
from
(
select t1.id,t1.personcode,t1.personname,t2.imonth,t2.salary from
person t1 inner join salary t2 on  t1.id = t2.id 
)t3

Answer 5

8。如果是查每个人的最高工资前面还有几个人select t3.id,t3.personcode,t3.personname,t3.salary,(select count(1) from 
(
select t1.id,t1.personcode,t1.personname,max(t2.salary) as salary from
person t1 inner join salary t2 on  t1.id = t2.id  group by t1.id,t1.personcode,t1.personname
)t4 where salary < t3.salary
) as 人数 
from
(
select t1.id,t1.personcode,t1.personname,max(t2.salary) as salary from
person t1 inner join salary t2 on  t1.id = t2.id group by t1.id,t1.personcode,t1.personname
)t3

Answer 6

晕，我查的都是每个人前面比自己低的人数应该把 "<" 改成　">"

Answer 7

set nocount on
go
declare @person table
(
  ID int,personcode  varchar(10),
  personname varchar(10),leader int
)
declare @salary table
(
  ID int,imonth int,salary int
)insert @person
select 1,'001','李进',1 union
select 2,'002','张三',1 union
select 3,'003','王小平',2 union
select 4,'004','李超',1
insert @salary
select 1,1,1000 union
select 1,2,1500 union
select 2,1,1200 union
select 2,2,1150 union
select 3,2,1000 print '1.那些人的工资在涨？'
select A.id,B.personcode,B.personname,B.leader 
from @salary A  join @person B on A.id=B.id
where exists(select 1 from @salary where id=A.id and imonth>A.imonth and salary>A.salary)print '2.那些人的工资在跌？'
select A.id,B.personcode,B.personname,B.leader,A.salary
from @salary A  join @person B on A.id=B.id
where exists(select 1 from @salary where id=A.id and imonth>A.imonth and salary<A.salary)print '3.按姓名字典顺序列出第二个人的2月份工资？'
select A.id,B.personcode,B.personname,B.leader 
from @salary A 
join 
(
 select top 1 * from @person where id not in(select top 1 id from @person order by personname)  order by personname
)  B on A.id=B.id
where A.imonth=2print '4.列出每个人管几个人？'
select A.id,A.personcode,A.personname,[管辖人数]=(select count(1) from @person where leader=A.id)
from @person Aprint '5.列出所有人的工资，并且列出合计数？'
select B.id,B.personcode,B.personname,B.leader,[salary]=isnull(A.salary,0),[合计]=isnull((select sum(salary) from @salary where id=B.id),0)
from @salary A  right join @person B on A.id=B.idprint '6.按工资从低到高排序列出姓名？'
select A.id,A.personcode,A.personname,A.leader,[salary]=isnull(B.salary,0)
from @person A 
left join 
     ( 
       select id,[salary]=sum(salary) from @salary group by id
     ) B on A.id=B.id
order by B.salaryprint '7.按工资从高到低排序列出姓名？'
select A.id,A.personcode,A.personname,A.leader,[salary]=isnull(B.salary,0)
from @person A 
left join 
     ( 
       select id,[salary]=sum(salary) from @salary group by id
     ) B on A.id=B.id
order by B.salary descprint '8列出每个人前面还有几个人工资比自己高？'
declare @tmp table(xuhao int identity,id int,salary int)
insert @tmp(id,salary) select id,[salary]=sum(salary) from @salary group by id
select A.id,A.personcode,A.personname,A.leader,B.salary,[比自己工资高的人数]=B.xuhao-1
from @person A
left join @tmp B on A.id=B.idprint '9.今年每个月每个人发多少工资（没有发的为0）？'
select A.id,A.personcode,A.personname,A.leader,M.imonth,[salary]=isnull(B.salary,0)
from @person A
left join 
     (
       select imonth from @salary group by imonth
     )M on 1>0
left join @salary B on A.id=B.id and M.imonth=B.imonthprint '10.那些人1月份没有发工资？'
select A.id,A.personcode,A.personname,A.leader
from @person A where not exists(select 1 from @salary where imonth=1 and id=A.id)--结果
/*1.那些人的工资在涨？
id          personcode personname leader      
----------- ---------- ---------- ----------- 
1           001        李进         12.那些人的工资在跌？
id          personcode personname leader      salary      
----------- ---------- ---------- ----------- ----------- 
2           002        张三         1           12003.按姓名字典顺序列出第二个人的2月份工资？
id          personcode personname leader      
----------- ---------- ---------- ----------- 
1           001        李进         14.列出每个人管几个人？
id          personcode personname 管辖人数        
----------- ---------- ---------- ----------- 
1           001        李进         3
2           002        张三         1
3           003        王小平        0
4           004        李超         05.列出所有人的工资，并且列出合计数？
id          personcode personname leader      salary      合计          
----------- ---------- ---------- ----------- ----------- ----------- 
1           001        李进         1           1000        2500
1           001        李进         1           1500        2500
2           002        张三         1           1200        2350
2           002        张三         1           1150        2350
3           003        王小平        2           1000        1000
4           004        李超         1           0           06.按工资从低到高排序列出姓名？
id          personcode personname leader      salary      
----------- ---------- ---------- ----------- ----------- 
4           004        李超         1           0
3           003        王小平        2           1000
2           002        张三         1           2350
1           001        李进         1           25007.按工资从高到低排序列出姓名？
id          personcode personname leader      salary      
----------- ---------- ---------- ----------- ----------- 
1           001        李进         1           2500
2           002        张三         1           2350
3           003        王小平        2           1000
4           004        李超         1           08列出每个人前面还有几个人工资比自己高？
id          personcode personname leader      salary      比自己工资高的人数   
----------- ---------- ---------- ----------- ----------- ----------- 
1           001        李进         1           2500        0
2           002        张三         1           2350        1
3           003        王小平        2           1000        2
4           004        李超         1           NULL        NULL9.今年每个月每个人发多少工资（没有发的为0）？
id          personcode personname leader      imonth      salary      
----------- ---------- ---------- ----------- ----------- ----------- 
1           001        李进         1           1           1000
1           001        李进         1           2           1500
2           002        张三         1           1           1200
2           002        张三         1           2           1150
3           003        王小平        2           1           0
3           003        王小平        2           2           1000
4           004        李超         1           1           0
4           004        李超         1           2           010.那些人1月份没有发工资？
id          personcode personname leader      
----------- ---------- ---------- ----------- 
3           003        王小平        2
4           004        李超         1
*/

Answer 8

第三个忘了列出工资了，应改为：
print '3.按姓名字典顺序列出第二个人的2月份工资？'
select A.id,B.personcode,B.personname,B.leader,[salary]=(select salary from @salary where id=B.id and imonth=2)
from @salary A 
join 
(
 select top 1 * from @person where id not in(select top 1 id from @person order by personname)  order by personname
)  B on A.id=B.id
where A.imonth=2--结果
/*
3.按姓名字典顺序列出第二个人的2月份工资？
id          personcode personname leader      salary      
----------- ---------- ---------- ----------- ----------- 
1           001        李进         1           1500
*/

Answer 9

1把以上两个表格创建
create table person
([id] int not null primary key ,
personcode nvarchar(10) not null,
personname nvarchar(20) not null,
birthday datetime,
education nvarchar(30),
leader int null
)create table salary
([id] int not null ,
imonth int  not null,
salary money not null
)
-------------------------------------------------
2清空以上两个表格
delete from person
delete from salarydelete person
from (select * from person) a
where person.id=a.id
-------------------------------------------------
3删除以上两个表格
drop table person
drop table salaryselect * from person 
select * from salary 
-------------------------------------------------
4按照示范例子插入以上记录
insert into dbo.person
  values(1,'001','李进','','',1)
insert into dbo.person 
  values(2,'002','张三','','',1)
insert into dbo.person
  values(3,'003','王金平','','',2)
insert  dbo.person 
  values(4,'004','李超','','',1)insert into salary
      select 1,1,1000
union select 1,2,1500
union select 2,1,1200
union select 2,2,1150
union select 3,2,1000
-------------------------------------------------
5统计有多少个人
select count(*) as 统计人数 from person 
-------------------------------------------------
6统计每个人平均工资？
select personname  姓名, avg(salary) 平均工资 
from person join salary
on person.id=salary.id
group by personname
-------------------------------------------------
7按姓名列出每个人发多少工资？
select personname  姓名, sum(salary) 工资 
from person join salary
on person.id=salary.id
group by personname
-------------------------------------------------
8今年每个月每个人发多少工资？（没有发的为0）
select A.id,A.personcode,A.personname,A.leader,M.imonth,[salary]=isnull(B.salary,0)
from person A
left join 
     (
       select imonth from salary group by imonth
     )M on 1>0
left join salary B on A.id=B.id and M.imonth=B.imonth
-------------------------------------------------
9哪些人1月份没有发工资？
select personname 姓名
from person 
where personname not in(
select personname from 
person join salary on
person.id=salary.id
where  imonth=1)
-------------------------------------------------
10哪些人工资在涨？
select a.personname 姓名,b.imonth2-b.imonth1 涨幅
from person a inner join
(
select id,imonth1=sum(case imonth when 1 then isnull(salary,0) end),
          imonth2=sum(case imonth when 2 then isnull(salary,0) end)
from salary group by id)bon a.id=b.id where b.imonth1<b.imonth2
-------------------------------------------------
11哪些人工资在跌？
select a.personname 姓名,b.imonth1-b.imonth2  跌幅
from person a inner join
(
select id,imonth1=sum(case imonth when 1 then isnull(salary,0) end),
          imonth2=sum(case imonth when 2 then isnull(salary,0) end)
from salary group by id)bon a.id=b.id where b.imonth1>b.imonth2
-------------------------------------------------
12按姓名字典顺序列出第二个人的2月份工资？
select A.id 序号,B.personcode 人员编号,B.personname 姓名,a.imonth 月份,salary 工资
from salary A 
join 
(
select top 1 * from person 
where id not in
(select top 1 id from person order by personname)  order by personname
)  B on A.id=B.id
where A.imonth=2-------------------------------------------------
13按“姓”统计平均工资
select substring(personname,1,1) 姓,avg(salary) 平均工资
from person join salary on
person.id=salary.id
group by substring(personname,1,1)select left(personname,1) 姓,avg(salary) 平均工资
from person join salary on
person.id=salary.id
group by left(personname,1)
-------------------------------------------------
14把每个人的姓名前面加上“LH”
select 'LH'+personname from person
-------------------------------------------------
15列出每个人管几个人
select A.id,A.personcode 员工编号,A.personname 员工姓名,
[管辖人数]=(select  count(1) from person where leader=A.id)
from person A
-------------------------------------------------
16列出所有人的工资，并列出合计数
select B.personcode 姓名编号 ,B.personname 姓名,
[工资]=isnull(A.salary,0),
[合计]=isnull((select sum(salary) from salary where id=B.id),0)
from salary A  right join person B on A.id=B.id-------------------------------------------------
17按工资从低到高排序列出姓名
select a.personname  姓名
from (select personname,sum(salary)as 工资
from person left join salary on person.id=salary.id group by personname) aorder by a.工资 asc
-------------------------------------------------
18按工资从高到低排序列出姓名
select a.personname  姓名
from (select personname,sum(salary)as 工资
from person left join salary on person.id=salary.id group by personname) aorder by a.工资 desc
-------------------------------------------------
19列出每个人前面还有几个人工资比自己高？
declare @tmp table(序号 int identity(1,1),id int,salary int)
insert @tmp(id,salary) 
select id,[salary]=sum(salary) from salary group by id
select A.id 序号,A.personcode 编号,A.personname 姓名,B.salary 工资,[比自己工资高的人数]=B.序号-1
from person A
left join @tmp B on A.id=B.id
-------------------------------------------------
20列出每个人的姓名的第三个字（如果没有就写“无”）
select case  substring(personname,3,1) when ''then '无' else  substring(personname,3,1) end
from person