select id=identity(1,1),[date] as d,danj into #t from tablename order by [date]select d,danj from #t a where danj<>( select top 1 danj from #t where id<(a.id-1) order by id desc )drop table #t
select * From Table a where id_ylr_m=(select top 1 b.id_ylr_m From Table b where a.danj=b.danj)
哦,我的那个不对...... sorry
同意 Cityfire(青团子) create table tb(dt varchar(10),dj dec(10,2)) insert tb values('2004-01-05',98) insert tb values('2004-01-17',100) insert tb values('2004-01-20',100) insert tb values('2004-02-13',105) select * From Tb a where dt=(select top 1 b.dt From Tb b where a.dj=b.dj )drop table tb--结果: dt dj ---------- ------------ 2004-01-05 98.00 2004-01-17 100.00 2004-02-13 105.00
CREATE TABLE t(d datetime,danj numeric(10,2)) insert into t select '2004-01-15', 98.00 union all select '2004-01-17', 100.00 union all select '2004-01-20', 100.00 union all select '2004-02-13', 105.00 union all select '2004-02-25', 90.00 union all select '2004-03-05', 90.00 union all select '2004-03-18', 98.00 union all select '2004-03-23', 105.00 union all select '2004-04-10', 105.00 union all select '2004-04-19', 93.00 union all select '2004-04-26', 93.00 GO select * from t GO select d,danj from ( select (select count(*) from t where d <= a.d) id,* from t a ) a where id = (select top 1 id from (select (select count(*) from t where d <= a.d) id,* from t a) aa where danj = a.danj and id in (a.id,a.id-1,a.id+1) order by d) GO DROP table t
-- 有点小错误: select d,danj from ( select (select count(*) from t where d <= a.d) id,* from t a ) a where id = (select top 1 id from (select (select count(*) from t where d <= a.d) id,* from t a) aa where danj = a.danj and id in (a.id,a.id - 1) order by d) GO
select date,danj top1 from table order by date
小二,我那个确实不对。 比如 create table tb(dt varchar(10),dj dec(10,2)) insert tb values('2004-01-05',98) insert tb values('2004-01-17',100) insert tb values('2004-01-20',100) insert tb values('2004-02-13',105) insert tb values('2004-02-19',100) select * From Tb a where dt=(select top 1 b.dt From Tb b where a.dj=b.dj )drop table tb--结果: dt dj ---------- ------------ 2004-01-05 98.00 2004-01-17 100.00 2004-02-13 105.00--应该结果: dt dj ---------- ------------ 2004-01-05 98.00 2004-01-17 100.00 2004-02-13 105.00 2004-02-19 100.00
测试过: SELECT * FROM Table WHERE (date NOT IN (SELECT date FROM (SELECT date,danj,(SELECT TOP 1 danj FROM Table WHERE date < a.date ORDER BY date DESC) AS danj1 FROM Table a) b WHERE (danj= danj1)))
sorry
create table tb(dt varchar(10),dj dec(10,2))
insert tb values('2004-01-05',98)
insert tb values('2004-01-17',100)
insert tb values('2004-01-20',100)
insert tb values('2004-02-13',105)
select * From Tb a where dt=(select top 1 b.dt From Tb b where a.dj=b.dj )drop table tb--结果:
dt dj
---------- ------------
2004-01-05 98.00
2004-01-17 100.00
2004-02-13 105.00
CREATE TABLE t(d datetime,danj numeric(10,2))
insert into t
select
'2004-01-15', 98.00
union all select '2004-01-17', 100.00
union all select '2004-01-20', 100.00
union all select '2004-02-13', 105.00
union all select '2004-02-25', 90.00
union all select '2004-03-05', 90.00
union all select '2004-03-18', 98.00
union all select '2004-03-23', 105.00
union all select '2004-04-10', 105.00
union all select '2004-04-19', 93.00
union all select '2004-04-26', 93.00
GO
select * from t
GO
select d,danj from
(
select (select count(*) from t where d <= a.d) id,* from t a
) a
where id = (select top 1 id from (select (select count(*) from t where d <= a.d) id,* from t a) aa where danj = a.danj
and id in (a.id,a.id-1,a.id+1)
order by d)
GO
DROP table t
select d,danj from
(
select (select count(*) from t where d <= a.d) id,* from t a
) a
where id = (select top 1 id from (select (select count(*) from t where d <= a.d) id,* from t a) aa where danj = a.danj
and id in (a.id,a.id - 1)
order by d)
GO
比如
create table tb(dt varchar(10),dj dec(10,2))
insert tb values('2004-01-05',98)
insert tb values('2004-01-17',100)
insert tb values('2004-01-20',100)
insert tb values('2004-02-13',105)
insert tb values('2004-02-19',100)
select * From Tb a where dt=(select top 1 b.dt From Tb b where a.dj=b.dj )drop table tb--结果:
dt dj
---------- ------------
2004-01-05 98.00
2004-01-17 100.00
2004-02-13 105.00--应该结果:
dt dj
---------- ------------
2004-01-05 98.00
2004-01-17 100.00
2004-02-13 105.00
2004-02-19 100.00
SELECT *
FROM Table
WHERE (date NOT IN
(SELECT date
FROM (SELECT date,danj,(SELECT TOP 1 danj FROM Table WHERE date < a.date ORDER BY date DESC) AS danj1
FROM Table a) b
WHERE (danj= danj1)))
我的测试结果如下:
dt dj
---------------------
2004-07-23 206.7000
2004-07-23 206.7000
2004-07-23 195.0000
2004-07-23 145.0000
2004-07-24 195.0000
2004-07-23 206.7000
2004-07-24 195.0000
2004-07-24 195.0000
2004-07-24 3.0000
2004-07-24 230.0000
2004-07-24 195.0000
2004-07-24 195.0000
2004-07-26 206.7000
2004-07-26 206.7000
2004-07-26 206.7000(所影响的行数为 15 行)