如何能实现SQL语句,可以将搜索到的语句,先滤除掉重复的,再隔一条取一条呢?
如: 有表 data ,字段有 时间、数值。时间 数值
2011-1-1 5
2011-1-1 5
2011-1-2 5.3
2011-1-2 5.3
2011-1-3 4.9
2011-1-4 5.6
2011-1-5 6
2011-1-6 3.5最后显示为:时间 数值
2011-1-1 5
2011-1-3 4.9
2011-1-5 6
如: 有表 data ,字段有 时间、数值。时间 数值
2011-1-1 5
2011-1-1 5
2011-1-2 5.3
2011-1-2 5.3
2011-1-3 4.9
2011-1-4 5.6
2011-1-5 6
2011-1-6 3.5最后显示为:时间 数值
2011-1-1 5
2011-1-3 4.9
2011-1-5 6
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U')) --U 代表你查询的是表
DROP TABLE [TB]
GO---->建表
create table [TB]([时间] datetime,[数值] numeric(2,1))
insert [TB]
select '2011-1-1',5 union all
select '2011-1-1',5 union all
select '2011-1-2',5.3 union all
select '2011-1-2',5.3 union all
select '2011-1-3',4.9 union all
select '2011-1-4',5.6 union all
select '2011-1-5',6 union all
select '2011-1-6',3.5
GO--> 查询结果
with dd as(
SELECT distinct * FROM [TB])
,ss as (select *,ROW_NUMBER()over(order by getdate()) as px from dd)
select * from ss
where px%2=1
--> 删除表格
--DROP TABLE [TB]
as
(
Select *,rid=ROW_NUMBER () over ( order by [时间],[数值]) From (select distinct * from [ta]) a
)
select * from cte where rid%2 =1