如题。有个表Ginfo,里面有500条数据,包括
ID GNUM Gcontent Createtime
1 1 abbaccc 2011-06-22 11:01
2 3 errorcode 2011-06-22 11:12
3 2 cddfdfd 2011-06-22 11:21
4 5 acdfeer 2011-06-22 11:31
5 1 dfdsfdsfs 2011-06-22 11:41
6 1 fgfgf 2011-06-22 11:51
7 2 fhghfghf 2011-06-22 12:01
8 3 hkhjkhjk 2011-06-22 12:11
9 5 rewrewrew 2011-06-22 12:21
10 6 fsdfdsfds 2011-06-22 12:31 .............................
首先根据select Gnum from 【GNUM字典表】
选出来GNUM 分别是 1、2、3、4、5、6
然后对应Ginfo表【按Createtime倒序排列】,分别选出Gum为1的12条记录
Gum为2的12条记录
Gum为3的12条记录
Gum为4的12条记录
Gum为5的12条记录
。
怎样用一条语句就能实现这样的效果呢
ID GNUM Gcontent Createtime
1 1 abbaccc 2011-06-22 11:01
2 3 errorcode 2011-06-22 11:12
3 2 cddfdfd 2011-06-22 11:21
4 5 acdfeer 2011-06-22 11:31
5 1 dfdsfdsfs 2011-06-22 11:41
6 1 fgfgf 2011-06-22 11:51
7 2 fhghfghf 2011-06-22 12:01
8 3 hkhjkhjk 2011-06-22 12:11
9 5 rewrewrew 2011-06-22 12:21
10 6 fsdfdsfds 2011-06-22 12:31 .............................
首先根据select Gnum from 【GNUM字典表】
选出来GNUM 分别是 1、2、3、4、5、6
然后对应Ginfo表【按Createtime倒序排列】,分别选出Gum为1的12条记录
Gum为2的12条记录
Gum为3的12条记录
Gum为4的12条记录
Gum为5的12条记录
。
怎样用一条语句就能实现这样的效果呢
select *
from(
select *,rid=row_number() over (partition by gum order by getdate())
from tb
)t
where rid between 1 and 12
(select top 12 id from ginfo
where gnum =a.gnum order by id desc)
from(
select *,rid=row_number() over (partition by gum order by createtime desc)
from tb
)t
where rid between 1 and 12
还在迷糊中最终版:
select *
from(
select *,rid=row_number() over (partition by gnum order by createtime desc)
from tb
)t
where rid between 1 and 12
查询分析器分析下来提示服务器: 消息 195,级别 15,状态 10,行 3
'row_number' 不是可以识别的 函数名。
select ID ,GNUM ,Gcontent ,Createtime
(
select (select count(id) from Ginfo where a.GNUM=GNUM and ID<a.ID) as rn,
ID ,GNUM ,Gcontent ,Createtime
from Ginfo a
)aa
where aa.rn<12
(select *,(select count(*) as cid from ginfo
where gnum=a.gnum and id<=a.id)cid from ginfo a) tb
where cid<=12
from
(
select (select count(id) from Ginfo where a.GNUM=GNUM and ID<a.ID) as rn,
ID ,GNUM ,Gcontent ,Createtime
from Ginfo a
)aa
where aa.rn<12
谢谢,能执行成功,可是我的按时间倒序取最近时间的前12条的【 order by createtime desc】放哪呢,刚才我在查询分析器里调试里一下,系统提示【服务器: 消息 1033,级别 15,状态 1,行 3 除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。】
select ID ,GNUM ,Gcontent ,Createtime from ( select (select count(id) from Ginfo where a.GNUM=GNUM and Createtime<a.Createtime) as rn, ID ,GNUM ,Gcontent ,Createtime from Ginfo a )aa where aa.rn<12
select * from ginfo a where id in
(select top 12 id from ginfo
where gnum =a.gnum order by id desc)
/*
id gnum col
----------- ----------- ----------
9 1 0.935554
10 1 0.636098
11 1 0.818986
12 1 0.848019
13 1 0.759625
14 1 0.202144
15 1 0.854931
16 1 0.878315
17 1 0.939274
18 1 0.0414026
19 1 0.407121
20 1 0.338978
29 2 0.759555
30 2 0.923851
31 2 0.745778
32 2 0.98098
33 2 0.575696
34 2 0.761428
35 2 0.199116
36 2 0.267532
37 2 0.257583
38 2 0.307173
39 2 0.551253
40 2 0.142581
49 3 0.469466
50 3 0.211833
51 3 0.598286
52 3 0.573389
53 3 0.242558
54 3 0.519369
55 3 0.817102
56 3 0.712947
57 3 0.582257
58 3 0.280573
59 3 0.886921
60 3 0.54501
69 4 0.573586
70 4 0.683838
71 4 0.924704
72 4 0.611109
73 4 0.191571
74 4 0.172738
75 4 0.0228107
76 4 0.346403
77 4 0.488249
78 4 0.395433
79 4 0.795448
80 4 0.613401
89 5 0.954554
90 5 0.839787
91 5 0.0742486
92 5 0.526752
93 5 0.996627
94 5 0.618028
95 5 0.536098
96 5 0.939383
97 5 0.0651936
98 5 0.406695
99 5 0.389707
100 5 0.308491
109 6 0.948397
110 6 0.750722
111 6 0.580022
112 6 0.301126
113 6 0.219698
114 6 0.42343
115 6 0.324516
116 6 0.131337
117 6 0.897478
118 6 0.24919
119 6 0.328923
120 6 0.800135
*/
我本地测试没有问题呀?
create table ginfo(id int identity,gnum int,col varchar(10))
go
insert into ginfo select 1,rand()
go 20
insert into ginfo select 2,rand()
go 20
insert into ginfo select 3,rand()
go 20
insert into ginfo select 4,rand()
go 20
insert into ginfo select 5,rand()
go 20
insert into ginfo select 6,rand()
go 20select * from ginfo a where id in
(select top 12 id from ginfo
where gnum =a.gnum order by id desc)测试数据...
ID GNUM Gcontent Createtime
85 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 2011-07-15 10:12:10
86 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 2011-07-15 10:12:10
87 7 ERROR CODE: 6 2011-07-15 10:12:10
78 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 2011-07-15 9:44:59
79 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 2011-07-15 9:44:59
80 7 ERROR CODE: 6 2011-07-15 9:44:59
77 7 ERROR CODE: 6 2011-07-15 9:44:49
75 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 2011-07-15 9:44:49
76 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 2011-07-15 9:44:49
69 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-15 9:44:21
70 10 ERROR CODE: 0 2011-07-15 9:44:21
67 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:24:28
68 10 ERROR CODE: 0 2011-07-14 18:24:28
65 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:24:20
66 10 ERROR CODE: 0 2011-07-14 18:24:20
63 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:24:12
64 10 ERROR CODE: 0 2011-07-14 18:24:12
61 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:24:03
62 10 ERROR CODE: 0 2011-07-14 18:24:03
59 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:21:55
60 10 ERROR CODE: 0 2011-07-14 18:21:55
57 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:21:20
58 10 ERROR CODE: 0 2011-07-14 18:21:20
55 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:18:19
56 10 ERROR CODE: 0 2011-07-14 18:18:19
53 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:15:45
54 10 ERROR CODE: 0 2011-07-14 18:15:45
51 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:11:45
52 10 ERROR CODE: 0 2011-07-14 18:11:45
49 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 18:08:28
50 10 ERROR CODE: 0 2011-07-14 18:08:28
45 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 2011-07-14 17:15:53
46 7 ERROR CODE: 6 2011-07-14 17:15:53
47 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 2011-07-14 17:15:53
48 10 ERROR CODE: 0 2011-07-14 17:15:53
44 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 2011-07-14 17:15:53
43 7 ERROR CODE: 6 2011-07-14 17:15:42
41 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 2011-07-14 17:15:42
42 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 2011-07-14 17:15:42
37 2 77 39 -71 46000 2502 446D 7 3 33 -96 25 2011-07-13 17:45:53
38 3 ERROR CODE: 0 2011-07-13 17:45:53
时间好像不是最新的
(select top 12 id from ginfo
where gnum =a.gnum order by createtime desc)
这个测试出来的时间是最新的,结果也合适。
id GNUM Gcontent IPCode Createtime
169 10 ERROR CODE: 0 B1 2011-07-15 11:25:49
168 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:25:49
167 10 ERROR CODE: 0 B1 2011-07-15 11:25:46
166 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:25:46
165 10 ERROR CODE: 0 B1 2011-07-15 11:25:42
164 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:25:42
161 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 B1 2011-07-15 11:25:39
162 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 B1 2011-07-15 11:25:39
163 7 ERROR CODE: 6 B1 2011-07-15 11:25:39
158 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 B1 2011-07-15 11:25:36
159 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 B1 2011-07-15 11:25:36
160 7 ERROR CODE: 6 B1 2011-07-15 11:25:36
157 10 ERROR CODE: 0 B1 2011-07-15 11:25:31
156 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:25:31
153 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 B1 2011-07-15 11:25:28
154 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 B1 2011-07-15 11:25:28
155 7 ERROR CODE: 6 B1 2011-07-15 11:25:28
150 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 B1 2011-07-15 11:25:25
151 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 B1 2011-07-15 11:25:25
152 7 ERROR CODE: 6 B1 2011-07-15 11:25:25
149 10 ERROR CODE: 0 B1 2011-07-15 11:25:21
148 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:25:21
147 10 ERROR CODE: 0 B1 2011-07-15 11:25:18
146 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:25:18
145 10 ERROR CODE: 0 B1 2011-07-15 11:24:41
144 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:24:41
143 10 ERROR CODE: 0 B1 2011-07-15 11:24:03
142 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:24:03
141 10 ERROR CODE: 0 B1 2011-07-15 11:24:00
140 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:24:00
139 10 ERROR CODE: 0 B1 2011-07-15 11:23:56
138 5 77 40 -70 46000 2502 446D 7 3 33 -96 26 B1 2011-07-15 11:23:56
135 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 B1 2011-07-15 11:23:50
136 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 B1 2011-07-15 11:23:50
137 7 ERROR CODE: 6 B1 2011-07-15 11:23:50
132 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 B1 2011-07-15 11:23:46
133 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 B1 2011-07-15 11:23:46
134 7 ERROR CODE: 6 B1 2011-07-15 11:23:46
129 2 60 43 -67 46000 2502 446F 7 7 33 -96 29 B1 2011-07-15 11:23:42
130 3 762 28 -82 46000 2502 5A43 4 1 30 -103 21 B1 2011-07-15 11:23:42
FROM (SELECT (SELECT COUNT(id)
FROM Ginfo
WHERE a.GNUM = GNUM AND Createtime > a.Createtime) AS rn, ID,
GNUM, Gcontent, Createtime
FROM Ginfo a) aa
WHERE (rn < 12)
ORDER BY GNUM
这样写就合适了