一个复杂sql 查询的写法,大家讨论

linus_liu2000 2009-08-19 12:16:53
有一个表accompy(RobID,BankID,share,date)
每条记录是说某个强盗,参与了某次对某个银行的抢劫,并分了多少钱
** date项的属性是date,即年月日
我想做两个查询
1)记录中的最近的一年,参与抢劫的盗匪平均分了多少钱(最近一年并不是指2008年,而是说数据库中最近的一年,得找出date项中年份的最大记录)
2)银行被抢钱数量最多的年份参与抢劫的盗匪平均分了多少钱
...全文
119 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
gw6328 2009-08-19
  • 打赏
  • 举报
回复
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 
gw6328 2009-08-19
  • 打赏
  • 举报
回复
[codeSQL]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 t[/code]
kk706 2009-08-19
  • 打赏
  • 举报
回复
上面的错了吧,就两个强盗一年抢10回,avg求得是除20的平均数,应该除2吧。
jwdream2008 2009-08-19
  • 打赏
  • 举报
回复
1.select avg(share) as '平均分的钱数' from accompy  where datediff(d,date,max(date))<=365
lihan6415151528 2009-08-19
  • 打赏
  • 举报
回复
SELECT SHARE FROM ACCOMPY WHERE [date]=(select max([date]) from accompy)
昵称被占用了 2009-08-19
  • 打赏
  • 举报
回复
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)

SQL77 2009-08-19
  • 打赏
  • 举报
回复
...
百年树人 2009-08-19
  • 打赏
  • 举报
回复
...
jwdream2008 2009-08-19
  • 打赏
  • 举报
回复
1.select avg(share) as '平均分的钱数' from accompy  where datediff(d,date,max(date))=365
xiariweiyang 2009-08-19
  • 打赏
  • 举报
回复
select avg(share) from accompy where left(date,4) in
(select top 1 left(date,4) from accompy order by left(date,4) desc )
zc_0101 2009-08-19
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 lqs_dg2007 的回复:]
楼上的写的语句是不是和要求不符呀

[/Quote]
嗯,我理解错了,我认为他的平均是一年的总的分红数了
LQS_DG2007 2009-08-19
  • 打赏
  • 举报
回复
--查询最近一年,强盗平均抢了多少钱
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)
LQS_DG2007 2009-08-19
  • 打赏
  • 举报
回复
楼上的写的语句是不是和要求不符呀
zc_0101 2009-08-19
  • 打赏
  • 举报
回复
--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步,却依然追不上地球的自传=========
--======= 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

*/

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧