求SQL!!
------------------------------------
name role signtime GROUP
dd admin 2001 1
dv Users 2111 1
sdf admin 2001 1
ds users 1955 1
dd admin 2001 2
dv Users 2001 2
sdf admin 1955 2
ds users 1955 2
想用查询查找得到以下结果
------------------------------------
name role signtime GROUP
sdf admin 2001 1
dd admin 2001 2
就是想查找每个GROUP中,是管理员的,
最迟注册的用户.
[解决办法]
select name,role,sightime,GROUP from t a where not exists
(
select 1 from t where GROUP=a.GROUP and signtime> a.signtime
)
[解决办法]
select name,role,signtime,[GROUP] from test a where not exists
(
select 1 from test where [GROUP]=a.[GROUP] and signtime> a.signtime and role= 'admin '
)
and a.role= 'admin '
[解决办法]
name role signtime GROUP
---------- ---------- ----------- -----------
sdf admin 2001 1
dd admin 2001 2
[解决办法]
name role signtime GROUP
---------- ---------- ----------- -----------
sdf admin 2001 1
dd admin 2001 2
[解决办法]
如果数据中同1组中,signtime只有1个最大的时候
select d.* from (select max(signtime) as signtime,GROUP from table
where role= 'admin '
group by GROUP) t,table d
where t.signtime=d.signtime and t.GROUP=d.GROUP
[解决办法]
select * from tablename a
where exists (select 1 from tablename where group=a.group and role= 'admin ' and a.signtime> signtime)
[解决办法]
select *
from c a
where not exists(select 1 from c where signtime> a.signtime and role= 'admin ') and a.role= 'admin '
返回：
name role signtime group
---------- -------------------- -------- -----------
dd admin 2001 1
sdf admin 2001 1
dd admin 2001 2
（所影响的行数为 3 行）
之所以是3行，是因为楼主所给的数据中有两个2001
[解决办法]
create table #t([name] varchar(10),role varchar(10),signtime char(4), [group] char(1) )
select * from #t
insert into #t values( 'dd ' , 'admin ' , '2001 ' , '1 ')
insert into #t values( 'dv ' , 'users ' , '2011 ' , '1 ')
insert into #t values( 'sdf ' , 'admin ' , '2001 ' , '1 ')
insert into #t values( 'dd ' , 'admin ' , '2001 ' , '2 ')
insert into #t values( 'dv ' , 'users ' , '2001 ' , '2 ')
insert into #t values( 'sdf ' , 'admin ' , '1955 ' , '2 ')
insert into #t values( 'ds ' , 'users ' , '1955 ' , '2 ')
select * from #t a
where not exists (select 1 from #t where [group]=a.[group] and a.role=role and signtime> a.signtime)
and a.role= 'admin '
name role signtime group
---------- ---------- -------- -----
dd admin 2001 1
sdf admin 2001 1
dd admin 2001 2
（所影响的行数为 3 行）