求一个sql查询语句

[zbking]

表A
ID NAME NET DATATIME
01 A 10 2012-01-01 00:12:00
02 A 10 2012-01-01 00:13:00
03 B 10 2012-01-01 00:14:00
04 B 10 2012-01-01 00:15:00
05 A 10 2012-01-02 00:12:00

期待如下结果
ID NAME NET DATATIME sort（表示为name当天的第几笔数据）
01 A 10 2012-01-01 00:12:00 1
02 A 10 2012-01-01 00:13:00 2
03 B 10 2012-01-01 00:14:00 1
04 B 10 2012-01-01 00:15:00 2
05 A 10 2012-01-02 00:12:00 1

[zbking]

[Quote=引用 8 楼 的回复:]

引用 7 楼 的回复:

不好意思，请问在access下该如何写？感激不尽
select *,
sort=(select count(*) from 表A where name=t.name and [datetime]<=[datetime])
from 表A t
[/Quote]

sort显示错误啊。。。

[ws_hgo]

DECLARE @tb TABLE ([ID] VARCHAR(2),[NAME] VARCHAR(1),[NET] INT,[DATATIME] DATETIME)
INSERT @tb
SELECT '01','A',10,'2012-01-01 00:12:00' UNION ALL
SELECT '02','A',10,'2012-01-01 00:13:00' UNION ALL
SELECT '03','B',10,'2012-01-01 00:14:00' UNION ALL
SELECT '04','B',10,'2012-01-01 00:15:00' UNION ALL
SELECT '05','A',10,'2012-01-02 00:12:00'


SELECT *,
	ROW_NUMBER() OVER(PARTITION BY [NAME],CONVERT(VARCHAR(12),[DATATIME],120) ORDER BY [DATATIME]) [Rank]
FROM @tb
ORDER BY ID


ID   NAME NET         DATATIME                Rank
---- ---- ----------- ----------------------- --------------------
01   A    10          2012-01-01 00:12:00.000 1
02   A    10          2012-01-01 00:13:00.000 2
03   B    10          2012-01-01 00:14:00.000 1
04   B    10          2012-01-01 00:15:00.000 2
05   A    10          2012-01-02 00:12:00.000 1

(5 row(s) affected)

[ws_hgo]

[Quote=引用 7 楼 的回复:]
不好意思，请问在access下该如何写？感激不尽
[/Quote]

DECLARE @tb TABLE ([ID] VARCHAR(2),[NAME] VARCHAR(1),[NET] INT,[DATATIME] DATETIME)
INSERT @tb
SELECT '01','A',10,'2012-01-01 00:12:00' UNION ALL
SELECT '02','A',10,'2012-01-01 00:13:00' UNION ALL
SELECT '03','B',10,'2012-01-01 00:14:00' UNION ALL
SELECT '04','B',10,'2012-01-01 00:15:00' UNION ALL
SELECT '05','A',10,'2012-01-02 00:12:00'


SELECT *,
	(SELECT COUNT(*)+1 FROM @tb WHERE [NAME] = T.[NAME] AND CONVERT(VARCHAR(12),[DATATIME],120) = CONVERT(VARCHAR(12),T.[DATATIME],120) AND  [DATATIME] < T.[DATATIME])
	--ROW_NUMBER() OVER(PARTITION BY [NAME],CONVERT(VARCHAR(12),[DATATIME],120) ORDER BY [DATATIME]) [Rank]
FROM @tb AS T
ORDER BY ID

[ORAClE SE]

row_number() over (partition by name,net,convert(nvarchar(10),datatime,120) order by id)

[筱筱澄]

[Quote=引用 9 楼 的回复:]

引用 8 楼 的回复:

引用 7 楼 的回复:

不好意思，请问在access下该如何写？感激不尽
select *,
sort=(select count(*) from 表A where name=t.name and [datetime]<=[datetime])
from 表A t


sort显示错误啊。。。
[/Quote]

--try
SELECT *,
(
		SELECT COUNT(*) FROM TB WHERE [NAME]=t.[NAME] 
		AND  Format([DATATIME],"yyyy-mm-dd")=Format(t.[DATATIME],"yyyy-mm-dd")
		AND [DATATIME]<=T.[DATATIME]
	  ) as sort 
FROM [tb] AS t

[Sky_Cat]

select datetime into #temp from 表A group by datetime
declare @i int,
@datetime varchar(20)
set @i=0
while（select count（*） from 表A）<>0
begin
select @datetime=datetime from #temp
update 表A set sort=@i+1 where datetime=@datetime
delete from 表A where datetime=@datetime
end
drop table #temp

[Sky_Cat]

[Quote=引用 14 楼 的回复:]

select datetime into #temp from 表A group by datetime
declare @i int,
@datetime varchar(20)
set @i=0
while（select count（*） from 表A）<>0
begin
select @datetime=datetime from #temp
update ……
[/Quote]
select datetime into #temp from 表A group by datetime
declare @i int,
@datetime varchar(20)
set @i=0
while（select count（*） from 表A）<>0
begin
select @datetime=datetime from #temp
update 表A set sort=@i+1 where datetime=@datetime
delete from #temp where datetime=@datetime
end
drop table #temp

[Sky_Cat]

select datetime into #temp from 表A group by datetime
declare @i int,
@datetime varchar(20)
set @i=0
while（select count（*） from #temp）<>0
begin
select @datetime=datetime from #temp
update 表A set sort=@i+1 where datetime=@datetime
delete from #temp where datetime=@datetime
end
drop table #temp

[zbking]

sort 表示为name当天的第几笔数据 按datatime排序的顺序

[chuanzhang5687]

select id,name,net datatime,sort = row_number() over (partition by name,net,convert(nvarchar(10),datatime,120) order by id)
from tb
order by id

[快溜]

select *,
sort=row_number() over(partition by name order by [datetime])
from 表A

[筱筱澄]

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([ID] VARCHAR(2),[NAME] VARCHAR(1),[NET] INT,[DATATIME] DATETIME)
INSERT [tb]
SELECT '01','A',10,'2012-01-01 00:12:00' UNION ALL
SELECT '02','A',10,'2012-01-01 00:13:00' UNION ALL
SELECT '03','B',10,'2012-01-01 00:14:00' UNION ALL
SELECT '04','B',10,'2012-01-01 00:15:00' UNION ALL
SELECT '05','A',10,'2012-01-02 00:12:00'
--------------开始查询--------------------------

SELECT *,sort =ROW_NUMBER()OVER(PARTITION BY[NAME],CONVERT(VARCHAR(10),[DATATIME],120) ORDER BY [DATATIME] )  FROM [tb]
ORDER BY id
----------------结果----------------------------
/* 
ID   NAME NET         DATATIME                sort
---- ---- ----------- ----------------------- --------------------
01   A    10          2012-01-01 00:12:00.000 1
02   A    10          2012-01-01 00:13:00.000 2
03   B    10          2012-01-01 00:14:00.000 1
04   B    10          2012-01-01 00:15:00.000 2
05   A    10          2012-01-02 00:12:00.000 1

(5 行受影响)

*/

[chuanzhang5687]

create table tb
(
	id	nvarchar(2),
	name nvarchar(20),
	net	 int,
	datatime datetime
)


insert into  tb values('01','A',10,'2012-01-01 00:12:00')
insert into  tb values('02','A',10,'2012-01-01 00:13:00')
insert into  tb values('03','B',10,'2012-01-01 00:14:00')
insert into  tb values('04','B',10,'2012-01-01 00:15:00')
insert into  tb values('05','A',10,'2012-01-02 00:12:00')



select id,name,net datatime,
sort = row_number() over (partition by name,net,convert(nvarchar(10),datatime,120) order by id)
from tb
order by id
/*
id,name,datatime,sort
01,A,10,1
02,A,10,2
03,B,10,1
04,B,10,2
05,A,10,1

(5 行受影响)

[zhazhuzhao]

2楼正解！
恭喜2楼！

[zbking]

不好意思，请问在access下该如何写？感激不尽

[快溜]

[Quote=引用 7 楼 的回复:]

不好意思，请问在access下该如何写？感激不尽
[/Quote]select *,
sort=(select count(*) from 表A where name=t.name and [datetime]<=[datetime])
from 表A t