34,575
社区成员
发帖
与我相关
我的任务
分享
select avg(t.share) as '平均分钱' from (select robid,sum(share) as share from accompy where datediff(d,date,max(date)) <=365 group by robid) as
1.select avg(share) as '平均分的钱数' from accompy where datediff(d,date,max(date))<=365
SELECT SHARE FROM ACCOMPY WHERE [date]=(select max([date]) from accompy)
select avg(share)
from accompy where year([date]) = (select max(year([date]) from accompy )
select avg(share)
from accompy where year([date]) = (select top 1 year([date] from accompy group by year([date] order by sum(share) desc)
1.select avg(share) as '平均分的钱数' from accompy where datediff(d,date,max(date))=365
--查询最近一年,强盗平均抢了多少钱
select sum(share)/(select count(*) from (select distinct RobId from accompy where year(date)=(select year(max(date)) from accompy)) t)
from accompy
where year(date)=(select year(max(date)) from accompy)
--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步,却依然追不上地球的自传=========
--======= By: zc_0101 At:2009-08-19 13:06:40=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: [accompy]
if object_id('[accompy]') is not null drop table [accompy]
create table [accompy] (RobID varchar(2),BankID varchar(2),share int,date datetime)
insert into [accompy]
select 'R1','B1',100000,'2009-1-2' union all
select 'R1','B2',300000,'2009-1-2' union all
select 'R2','B1',500000,'2009-3-3' union all
select 'R2','B2',200000,'2009-3-3' union all
select 'R1','B1',100000,'2009-1-2' union all
select 'R1','B1',900000,'2003-1-2' union all
select 'R2','B2',900000,'2003-1-2' union all
select 'R1','B1',800000,'2003-1-3' union all
select 'R2','B2',900000,'2003-1-3' union all
select 'R1','B1',100000,'2004-1-2'
select * from [accompy]
----------------查询------------
--查询一:
SELECT ROBID,SUM(SHARE) 分得钱数,MAX(YEAR(DATE)) 年份 FROM [accompy] WHERE YEAR(DATE)=(SELECT MAX(YEAR(DATE)) FROM [accompy]) GROUP BY ROBID
--查询二:
SELECT ROBID,SUM(SHARE) 分得钱数,MAX(YEAR(DATE)) 年份 FROM [accompy] WHERE YEAR(DATE)=
(SELECT A.最多年份 FROM
(
SELECT YEAR(DATE) 最多年份,ROW=ROW_NUMBER() OVER (ORDER BY SUM(SHARE) DESC) FROM [accompy] GROUP BY YEAR(DATE)
) A WHERE A.ROW=1) GROUP BY ROBID
----------------结果--------------
/*
ROBID 分得钱数 年份
R1 500000 2009
R2 700000 2009
ROBID 分得钱数 年份
R1 1700000 2003
R2 1800000 2003
*/