Question

sql 关于select的查询语句

三个字段id，dt，num，（num中可能是数字，也肯能是单个的字符，例如b）
要求按照dt排序，num中连续同样的字符，只显示第一个，其他以“代替，不连续的仍用字符表示，数字同样的不代替

Answer 1

declare @tab table(id int,dt Date,num nvarchar(100))
insert into @tab
select 1,'2011-1-1','1'
union
select 1,'2011-1-2','e'
union
select 1,'2011-1-3','e'
union
select 1,'2012-1-4','3'
union
select 1,'2012-1-5','4'
union
select 1,'2012-1-6','4'

select *,isnumeric(num) as isNum into #newTab from @tab

select *,ROW_NUMBER() over(partition by num order by dt) as numRowNum
into #numRowNumTab
from #newTab

update #numRowNumTab set num='"' where numRowNum>1 and isNum=0

select id,dt,num from #numRowNumTab

drop table #newTab
drop table #numRowNumTab

Answer 2

难度有点高！呵呵，何必用SQL来处理这些呢，似乎不是SQL的强项，应该使用字符串来处理！

Answer 3

思路是，先排重，然后排序. 排重用关键字：distinct 排序用关键字 ： order by
select distinct num,id,dt from table_name order by dt.排序默认情况下是升序。
select distinct num,id,dt from table_name order by dt desc.降序要加 desc.
希望对你有帮助！

Answer 4

create table tmp(
id int,
dt datetime,
num varchar(100)
)
insert into tmp (id,dt,num)
values(1,'2011-04-01','2')
insert into tmp (id,dt,num)
values(2,'2011-04-01','2')
insert into tmp (id,dt,num)
values(3,'2011-04-02','a')
insert into tmp (id,dt,num)
values(4,'2011-04-03','a')
insert into tmp (id,dt,num)
values(5,'2011-04-04','b')

-- select * from tmp
declare @dt datetime
declare @num varchar(100)
declare @num1 varchar(100)
declare @SQL varchar(1000)

set @num1=''
set @num=''

DECLARE CUR1 CURSOR FOR
select dt,num from tmp
order by dt

create table #t(
dt datetime,
num varchar(100)
)

OPEN CUR1

FETCH NEXT FROM CUR1 INTO @dt,@num

IF @@FETCH_STATUS <> 0
BEGIN
DEALLOCATE CUR1
RAISERROR('没有记录',16,-1)
RETURN
END

WHILE @@FETCH_STATUS = 0
BEGIN
IF @num1=@num and isnumeric(@num)<>1
begin
insert into #t(dt,num)
values(@dt,'')
end
else
begin
insert into #t(dt,num)
values(@dt,@num)
end
set @num1=@num
FETCH NEXT FROM CUR1 INTO @dt,@num
END

CLOSE CUR1
DEALLOCATE CUR1
select * from #t

drop table tmp