create table SampleRecordTest
(
recordid int identity(1,1) primary key,
receiveTime datetime not null,
cardID bigint not null,
beaconID varchar(50) not null,
aRSSI smallint not null,
bRSSI smallint not null,
coordinates varchar(50) ,
updateTime datetime
)
2011-05-10 19:10:49.920 535824124 6 42 55 -258.9684,246.1845 2011-05-10 19:10:52.797 42
2011-05-10 19:10:49.890 535824124 4 24 15 -258.9684,246.1845 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.920 535824124 8 24 24 -258.9684,246.1845 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.920 535824124 10 19 14 -258.9684,246.1845 2011-05-10 19:10:52.797 19
2011-05-10 19:10:49.813 535824124 9 16 9 -258.9684,246.1845 2011-05-10 19:10:52.797 16
2011-05-10 19:10:49.907 535824124 7 10 12 -258.9684,246.1845 2011-05-10 19:10:52.797 12
2011-05-10 19:10:49.920 535825191 6 42 39 -287.1907,213.3415 2011-05-10 19:10:52.797 42
2011-05-10 19:10:49.920 535825191 8 18 24 -287.1907,213.3415 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.890 535825191 4 24 24 -287.1907,213.3415 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.813 535825191 9 14 12 -287.1907,213.3415 2011-05-10 19:10:52.797 14
2011-05-10 19:10:49.920 535825191 10 11 11 -287.1907,213.3415 2011-05-10 19:10:52.797 11
2011-05-10 19:10:49.907 535825191 7 0 9 -287.1907,213.3415 2011-05-10 19:10:52.797 9
2011-05-10 19:10:49.920 535825200 6 48 44 -288.3771,244.7981 2011-05-10 19:10:52.797 48
2011-05-10 19:10:49.890 535825200 4 24 20 -288.3771,244.7981 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.813 535825200 9 9 18 -288.3771,244.7981 2011-05-10 19:10:52.797 18
2011-05-10 19:10:49.920 535825200 8 16 18 -288.3771,244.7981 2011-05-10 19:10:52.797 18
2011-05-10 19:10:49.907 535825200 7 8 13 -288.3771,244.7981 2011-05-10 19:10:52.797 13
2011-05-10 19:10:49.920 535825200 10 11 9 -288.3771,244.7981 2011-05-10 19:10:52.797 11
要求为取一段时间内的cardID的beaconID。
同一张卡号,如果aRSSI大,就取aRSSI大的那列值
如果bRSSI大,就取bRSSI大的那列值
select updateTime,cardID,max(ta.c) as ed from (
select *,case when aRSSI>bRSSI then aRSSI else bRSSI end as c
from SampleRecord where updateTime='2011-05-10 19:10:52.797'
) as ta group by updateTime,cardID order by updateTime,cardID
但是我主要是想取beaconID的值,我的sql语句该怎么改
(
recordid int identity(1,1) primary key,
receiveTime datetime not null,
cardID bigint not null,
beaconID varchar(50) not null,
aRSSI smallint not null,
bRSSI smallint not null,
coordinates varchar(50) ,
updateTime datetime
)
2011-05-10 19:10:49.920 535824124 6 42 55 -258.9684,246.1845 2011-05-10 19:10:52.797 42
2011-05-10 19:10:49.890 535824124 4 24 15 -258.9684,246.1845 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.920 535824124 8 24 24 -258.9684,246.1845 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.920 535824124 10 19 14 -258.9684,246.1845 2011-05-10 19:10:52.797 19
2011-05-10 19:10:49.813 535824124 9 16 9 -258.9684,246.1845 2011-05-10 19:10:52.797 16
2011-05-10 19:10:49.907 535824124 7 10 12 -258.9684,246.1845 2011-05-10 19:10:52.797 12
2011-05-10 19:10:49.920 535825191 6 42 39 -287.1907,213.3415 2011-05-10 19:10:52.797 42
2011-05-10 19:10:49.920 535825191 8 18 24 -287.1907,213.3415 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.890 535825191 4 24 24 -287.1907,213.3415 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.813 535825191 9 14 12 -287.1907,213.3415 2011-05-10 19:10:52.797 14
2011-05-10 19:10:49.920 535825191 10 11 11 -287.1907,213.3415 2011-05-10 19:10:52.797 11
2011-05-10 19:10:49.907 535825191 7 0 9 -287.1907,213.3415 2011-05-10 19:10:52.797 9
2011-05-10 19:10:49.920 535825200 6 48 44 -288.3771,244.7981 2011-05-10 19:10:52.797 48
2011-05-10 19:10:49.890 535825200 4 24 20 -288.3771,244.7981 2011-05-10 19:10:52.797 24
2011-05-10 19:10:49.813 535825200 9 9 18 -288.3771,244.7981 2011-05-10 19:10:52.797 18
2011-05-10 19:10:49.920 535825200 8 16 18 -288.3771,244.7981 2011-05-10 19:10:52.797 18
2011-05-10 19:10:49.907 535825200 7 8 13 -288.3771,244.7981 2011-05-10 19:10:52.797 13
2011-05-10 19:10:49.920 535825200 10 11 9 -288.3771,244.7981 2011-05-10 19:10:52.797 11
要求为取一段时间内的cardID的beaconID。
同一张卡号,如果aRSSI大,就取aRSSI大的那列值
如果bRSSI大,就取bRSSI大的那列值
select updateTime,cardID,max(ta.c) as ed from (
select *,case when aRSSI>bRSSI then aRSSI else bRSSI end as c
from SampleRecord where updateTime='2011-05-10 19:10:52.797'
) as ta group by updateTime,cardID order by updateTime,cardID
但是我主要是想取beaconID的值,我的sql语句该怎么改
select *,case when aRSSI>bRSSI then aRSSI else bRSSI end as c
from SampleRecord where updateTime='2011-05-10 19:10:52.797'
) as ta group by updateTime,cardID order by updateTime,cardID
select * from (
select *,
case when aRSSI>bRSSI then aRSSI else bRSSI end as c
from SampleRecordTest ) as ta where updateTime='2011-05-10 19:10:52.797' and c =(select max(c)from (
select *,
case when aRSSI>bRSSI then aRSSI else bRSSI end as c
from SampleRecordTest where updateTime='2011-05-10 19:10:52.797') as tb where ta.cardID=tb.cardID )这样就行了
(select *,(case when aRSSI>bRSSI then aRSSI else bRSSI end) as c
from SampleRecord ) a where not exists (select 1 from (select *,(case when aRSSI>bRSSI then aRSSI else bRSSI end) as c
from SampleRecord) b on a.updateTime =b.updateTime and a.cardID = b.cardID and a.c<b.c )
以上如果是2005的话最好吧那个select *,(case when aRSSI>bRSSI then aRSSI else bRSSI end) as c
from SampleRecord 用with 语法弄个表。
考虑到可能最大的值是一样的,那么在结果集处加一个distinct
看你这需求是要求出同一个时间点内的同一个cardID ,然后新字段C取最大的这么一条记录
create table SampleRecordTest
(
recordid int identity(1,1) primary key,
receiveTime datetime not null,
cardID bigint not null,
beaconID varchar(50) not null,
aRSSI smallint not null,
bRSSI smallint not null,
coordinates varchar(50) ,
updateTime datetime,
othercol int
)
insert into samplerecordtest select '2011-05-10 19:10:49.920',535824124,'6',42,55,'-258.9684,246.1845','2011-05-10 19:10:52.797',42
insert into samplerecordtest select '2011-05-10 19:10:49.890',535824124,'4',24,15,'-258.9684,246.1845','2011-05-10 19:10:52.797',24
insert into samplerecordtest select '2011-05-10 19:10:49.920',535824124,'8',24,24,'-258.9684,246.1845','2011-05-10 19:10:52.797',24
insert into samplerecordtest select '2011-05-10 19:10:49.920',535824124,'10',19,14,'-258.9684,246.1845','2011-05-10 19:10:52.797',19
insert into samplerecordtest select '2011-05-10 19:10:49.813',535824124,'9',16,9,'-258.9684,246.1845','2011-05-10 19:10:52.797',16
insert into samplerecordtest select '2011-05-10 19:10:49.907',535824124,'7',10,12,'-258.9684,246.1845','2011-05-10 19:10:52.797',12
insert into samplerecordtest select '2011-05-10 19:10:49.920',535825191,'6',42,39,'-287.1907,213.3415','2011-05-10 19:10:52.797',42
insert into samplerecordtest select '2011-05-10 19:10:49.920',535825191,'8',18,24,'-287.1907,213.3415','2011-05-10 19:10:52.797',24
insert into samplerecordtest select '2011-05-10 19:10:49.890',535825191,'4',24,24,'-287.1907,213.3415','2011-05-10 19:10:52.797',24
insert into samplerecordtest select '2011-05-10 19:10:49.813',535825191,'9',14,12,'-287.1907,213.3415','2011-05-10 19:10:52.797',14
insert into samplerecordtest select '2011-05-10 19:10:49.920',535825191,'10',11,11,'-287.1907,213.3415','2011-05-10 19:10:52.797',11
insert into samplerecordtest select '2011-05-10 19:10:49.907',535825191,'7',0,9,'-287.1907,213.3415','2011-05-10 19:10:52.797',9
insert into samplerecordtest select '2011-05-10 19:10:49.920',535825200,'6',48,44,'-288.3771,244.7981','2011-05-10 19:10:52.797',48
insert into samplerecordtest select '2011-05-10 19:10:49.890',535825200,'4',24,20,'-288.3771,244.7981','2011-05-10 19:10:52.797',24
insert into samplerecordtest select '2011-05-10 19:10:49.813',535825200,'9',9,18,'-288.3771,244.7981','2011-05-10 19:10:52.797',18
insert into samplerecordtest select '2011-05-10 19:10:49.920',535825200,'8',16,18,'-288.3771,244.7981','2011-05-10 19:10:52.797',18
insert into samplerecordtest select '2011-05-10 19:10:49.907',535825200,'7',8,13,'-288.3771,244.7981','2011-05-10 19:10:52.797',13
insert into samplerecordtest select '2011-05-10 19:10:49.920',535825200,'10',11,9,'-288.3771,244.7981','2011-05-10 19:10:52.797',11
go
select * from SampleRecordtest a where updateTime='2011-05-10 19:10:52.797' and
not exists (select 1 from SampleRecordTest where cardid=a.cardid and updatetime=a.updatetime and (aRSSI>a.aRSSI or bRSSI>a.bRSSI))
go
drop table SampleRecordTest
/*
recordid receiveTime cardID beaconID aRSSI bRSSI coordinates updateTime othercol
----------- ----------------------- -------------------- -------------------------------------------------- ------ ------ -------------------------------------------------- ----------------------- -----------
1 2011-05-10 19:10:49.920 535824124 6 42 55 -258.9684,246.1845 2011-05-10 19:10:52.797 42
7 2011-05-10 19:10:49.920 535825191 6 42 39 -287.1907,213.3415 2011-05-10 19:10:52.797 42
13 2011-05-10 19:10:49.920 535825200 6 48 44 -288.3771,244.7981 2011-05-10 19:10:52.797 48(3 行受影响)*/
你的这个sql语句好像有问题,如果要求是一个时间段得话,就是说有可能updateTime不相同
你这样写有问题,我的sql也有问题
我重新生成结果吧
insert into SampleRecordTest select
'2011-05-10 19:10:49.813', 535825200, 9, 9, 18, '-288.3771,244.7981', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.813', 535825191, 9, 14, 12, '-287.1907,213.3415', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.813', 535824124, 9, 16, 55, '-258.9684,246.1845', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.890', 535825200, 4, 24, 20, '-288.3771,244.7981', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.890', 535825191, 4, 24, 24, '-287.1907,213.3415', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.890', 535824124, 4, 24, 15, '-258.9684,246.1845', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.907', 535825200, 7, 8, 13, '-288.3771,244.7981', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.907', 535825191, 7, 0, 9, '-287.1907,213.3415', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.907', 535824124, 7, 60, 12, '-258.9684,246.1845', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.920', 535825200, 10, 11, 9, '-288.3771,244.7981', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.920', 535825191, 10, 11, 11, '-287.1907,213.3415', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.920', 535824124, 10, 19, 14, '-258.9684,246.1845', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.920', 535825200, 8, 16, 18, '-288.3771,244.7981', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.920', 535825191, 8, 18, 24, '-287.1907,213.3415', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.920', 535824124, 8, 24, 24, '-258.9684,246.1845', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.920', 535825200, 6, 48, 44, '-288.3771,244.7981', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.920', 535825191, 6, 42, 39, '-287.1907,213.3415', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:49.920', 535824124, 6, 42, 35, '-258.9684,246.1845', '2011-05-10 19:10:52.797' union all select
'2011-05-10 19:10:53.827', 535825200, 9, 6, 18, '-294.8826,254.4427', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.827', 535825191, 9, 12, 14, '-292.9003,214.1926', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.827', 535824124, 9, 15, 9, '-263.5563,249.2901', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.890', 535825200, 8, 15, 16, '-294.8826,254.4427', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.890', 535825191, 8, 17, 24, '-292.9003,214.1926', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.890', 535824124, 8, 24, 24, '-263.5563,249.2901', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.907', 535825200, 6, 48, 43, '-294.8826,254.4427', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.907', 535825191, 6, 41, 39, '-292.9003,214.1926', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.907', 535824124, 6, 42, 34, '-263.5563,249.2901', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.907', 535825200, 7, 57, 15, '-294.8826,254.4427', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.907', 535825191, 7, 0, 7, '-292.9003,214.1926', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.907', 535824124, 7, 5, 13, '-263.5563,249.2901', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.907', 535825200, 10, 9, 7, '-294.8826,254.4427', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.907', 535825191, 10, 12, 61, '-292.9003,214.1926', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.907', 535824124, 10, 18, 12, '-263.5563,249.2901', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.920', 535825200, 4, 21, 17, '-294.8826,254.4427', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.920', 535825191, 4, 24, 24, '-292.9003,214.1926', '2011-05-10 19:10:56.827' union all select
'2011-05-10 19:10:53.920', 535824124, 4, 23, 15, '-263.5563,249.2901', '2011-05-10 19:10:56.82'
(select *,(case when aRSSI>bRSSI then aRSSI else bRSSI end) as c
from SampleRecordTest ) a where not exists (
select 1 from
(select *,(case when aRSSI>bRSSI then aRSSI else bRSSI end) as c from SampleRecordTest) b
where a.updateTime =b.updateTime and a.cardID = b.cardID and a.c<b.c )
and updateTime ='2011-05-10 19:10:52.797'
(select *,(case when aRSSI>bRSSI then aRSSI else bRSSI end) as c
from SampleRecordTest ) a where not exists (
select 1 from
(select *,(case when aRSSI>bRSSI then aRSSI else bRSSI end) as c from SampleRecordTest) b
where convert(varchar,a.updateTime,120) =convert(varchar,b.updateTime,120) and a.cardID = b.cardID and a.c<b.c )
--and updateTime ='2011-05-10 19:10:52.797'
order by updatetime加个convet就可以了。