1Select ID from tb A
Inner Join (Select Max(sell) As sell,Parent from tb Where parent<>'' Group By Parent) B
On A.sell=B.sell And A.Parent=B.Parent
Order By A.ID
Inner Join (Select Max(sell) As sell,Parent from tb Where parent<>'' Group By Parent) B
On A.sell=B.sell And A.Parent=B.Parent
Order By A.ID
解决方案 »
- sql 05 row_number无法识别(同时装了sql2000)
- 怎么查询 数据库的 collation 默认值?
- select case问题
- access 查询升级到sql视图 的问题
- ASP+SQL2000数据恢复问题。
- [求助]莫名奇妙的锁问题
- 求一 sql语句
- 取出字符串中的数字,如何处理?解决马上给分
- 一个关于存储过程执行效率的问题,请大家帮忙看看
- 高难度多版本BOM成本计算SQL2000(coleling兄请进)
- 想写个批处理,写完之后发现一个问题,'insert into'时候指定一个列名,可是这个列名有能不存在。如何能在插入之前?
- object has been closed 这个问题怎么解决?
应该改成
insert into tb(name,sell,parent,station) values('aa',3000,'','组长')
insert into tb(name,sell,parent,station) values('bb',4000,1,'成员')
insert into tb(name,sell,parent,station) values('cc',2500,1,'成员')
insert into tb(name,sell,parent,station) values('dd',3200,1,'成员')
insert into tb(name,sell,parent,station) values('ee',4000,'','组长')
insert into tb(name,sell,parent,station) values('ff',3000,5,'成员')
insert into tb(name,sell,parent,station) values('gg',5000,5,'成员')
insert into tb(name,sell,parent,station) values('hh',2800,'','组长')
insert into tb(name,sell,parent,station) values('ii',3000,8,'成员')
insert into tb(name,sell,parent,station) values('jj',3500,8,'成员')
insert into tb(name,sell,parent,station) values('kk',2000,8,'成员')
才对把?
insert into tb(name,sell,parent,station) values('aa',9000,'','组长')
insert into tb(name,sell,parent,station) values('bb',4000,1,'成员')
insert into tb(name,sell,parent,station) values('cc',2500,1,'成员')
insert into tb(name,sell,parent,station) values('dd',3200,1,'成员')
insert into tb(name,sell,parent,station) values('ee',4000,'','组长')
insert into tb(name,sell,parent,station) values('ff',3000,2,'成员')
insert into tb(name,sell,parent,station) values('gg',5000,2,'成员')
insert into tb(name,sell,parent,station) values('hh',2800,'','组长')
insert into tb(name,sell,parent,station) values('ii',3000,3,'成员')
insert into tb(name,sell,parent,station) values('jj',3500,3,'成员')
insert into tb(name,sell,parent,station) values('kk',2000,3,'成员')
declare @li_parent int
set @li_parent=0update tb set @li_parent=(case parent when '' then @li_parent+1 else @li_parent end),parent=(case parent when '' then @li_parent else parent end)
--问题一 查询出每个小组sell最大的员工id?
select id from tb t where not exists (select * from tb b where t.parent=b.parent and t.sell<b.sell)
/*id
-----------
1
7
10*/
--问题二 查询出每个小组中成员比组长sell大的记录?
select * from tb t where t.station='成员' and exists (select * from tb b where t.parent=b.parent and t.sell>b.sell and b.station='组长')
/*ID name sell parent station
----------- ---------- ----------- ----------- ----------
7 gg 5000 2 成员
9 ii 3000 3 成员
10 jj 3500 3 成员*/drop table tb