项目要求分类排序查询并要求只显示每一类前10条记录~SQL怎么写? 分类导航 项目要求分类排序查询并要求只显示每一类前10条记录~SQL怎么写?

Question

如果表有主健和标识的统计方法
select * from 表A  a
where 主健/标识=(select top 10 主健/标识 from 表A where 列A= a.列A )可以定义desc或asc这样就行了

Answer 1

表的字段
Productid(主键) images Nclassid deteandtime
现在按照Nclassid分类查询出最近(按dateandtime)的图片.如何做?

Answer 2

use test--create table #test(ID int identity(1,1),num int,type int,Create_time datetime)
/*
declare @create_time datetime
select @create_time='2006-01-01'while @create_time <'2006-02-01'
begin
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
insert into #test(num,type,Create_time)
select rand()*100,rand()*10,@create_time
select @create_time=@create_time+rand()+0.5
end 
*/
--select * from #test
declare @sql varchar(8000)
select @sql = ''
select @sql = @sql+'union all select TOP 10 num,type,create_time from #test where type = '+cast(type as varchar(10))+' order by create_time'+char(13) from #test
group by type
select @sql = stuff(@sql,1,10,'')
exec ('select * from ('+@sql+')tb order by type')
--drop table #test 
result :
--------------------
54 0 2006-01-01 00:00:00.000
6 0 2006-01-02 07:12:25.277
80 0 2006-01-03 11:22:38.073
84 0 2006-01-03 11:22:38.073
81 0 2006-01-04 11:47:53.010
83 0 2006-01-05 02:27:02.503
3 0 2006-01-06 13:52:23.860
86 0 2006-01-06 13:52:23.860
65 0 2006-01-06 13:52:23.860
71 0 2006-01-06 13:52:23.860
1 1 2006-01-01 00:00:00.000
62 1 2006-01-01 00:00:00.000
51 1 2006-01-01 00:00:00.000
97 1 2006-01-01 00:00:00.000
49 1 2006-01-01 00:00:00.000
89 1 2006-01-02 07:12:25.277
86 1 2006-01-03 11:22:38.073
56 1 2006-01-03 11:22:38.073
2 1 2006-01-04 11:47:53.010
9 1 2006-01-04 11:47:53.010
5 2 2006-01-01 00:00:00.000
30 2 2006-01-02 07:12:25.277
76 2 2006-01-02 07:12:25.277
71 2 2006-01-02 07:12:25.277
81 2 2006-01-02 07:12:25.277
40 2 2006-01-04 11:47:53.010
41 2 2006-01-04 11:47:53.010
0 2 2006-01-05 02:27:02.503
50 2 2006-01-05 02:27:02.503
54 2 2006-01-06 13:52:23.860
26 3 2006-01-01 00:00:00.000
40 3 2006-01-02 07:12:25.277
2 3 2006-01-03 11:22:38.073
8 3 2006-01-04 11:47:53.010
27 3 2006-01-05 02:27:02.503
10 3 2006-01-06 13:52:23.860
31 3 2006-01-07 17:51:09.600
40 3 2006-01-07 17:51:09.600
97 3 2006-01-08 11:26:11.100
51 3 2006-01-09 12:15:55.737
17 4 2006-01-01 00:00:00.000
62 4 2006-01-01 00:00:00.000
95 4 2006-01-02 07:12:25.277
59 4 2006-01-02 07:12:25.277
60 4 2006-01-02 07:12:25.277
38 4 2006-01-02 07:12:25.277
88 4 2006-01-03 11:22:38.073
39 4 2006-01-03 11:22:38.073
17 4 2006-01-05 02:27:02.503
54 4 2006-01-06 13:52:23.860
48 5 2006-01-02 07:12:25.277
82 5 2006-01-02 07:12:25.277
8 5 2006-01-02 07:12:25.277
13 5 2006-01-03 11:22:38.073
68 5 2006-01-03 11:22:38.073
8 5 2006-01-04 11:47:53.010
4 5 2006-01-05 02:27:02.503
0 5 2006-01-08 11:26:11.100
40 5 2006-01-08 11:26:11.100
77 5 2006-01-09 12:15:55.737
19 6 2006-01-01 00:00:00.000
80 6 2006-01-02 07:12:25.277
55 6 2006-01-03 11:22:38.073
75 6 2006-01-03 11:22:38.073
57 6 2006-01-03 11:22:38.073
89 6 2006-01-04 11:47:53.010
2 6 2006-01-05 02:27:02.503
37 6 2006-01-05 02:27:02.503
85 6 2006-01-05 02:27:02.503
19 6 2006-01-05 02:27:02.503
37 7 2006-01-01 00:00:00.000
72 7 2006-01-01 00:00:00.000
69 7 2006-01-01 00:00:00.000
26 7 2006-01-02 07:12:25.277
48 7 2006-01-03 11:22:38.073
79 7 2006-01-03 11:22:38.073
51 7 2006-01-04 11:47:53.010
78 7 2006-01-04 11:47:53.010
38 7 2006-01-04 11:47:53.010
62 7 2006-01-04 11:47:53.010
74 8 2006-01-01 00:00:00.000
43 8 2006-01-03 11:22:38.073
20 8 2006-01-03 11:22:38.073
72 8 2006-01-03 11:22:38.073
76 8 2006-01-04 11:47:53.010
47 8 2006-01-05 02:27:02.503
70 8 2006-01-06 13:52:23.860
70 8 2006-01-07 17:51:09.600
1 8 2006-01-07 17:51:09.600
68 8 2006-01-07 17:51:09.600
73 9 2006-01-01 00:00:00.000
43 9 2006-01-01 00:00:00.000
53 9 2006-01-02 07:12:25.277
10 9 2006-01-04 11:47:53.010
69 9 2006-01-05 02:27:02.503
58 9 2006-01-07 17:51:09.600
20 9 2006-01-08 11:26:11.100
77 9 2006-01-08 11:26:11.100
65 9 2006-01-08 11:26:11.100
39 9 2006-01-09 12:15:55.737

Answer 3

分类取前N个记录的SQL语句
有一个表AAA，结构如下：
类别编号   说明     排序
  a          aa      1
  a          aa2     2
  a          aa3     3
  b          bb      1
  b          bb2     2
  b          bb3     3
  c          cc      1
  c          cc2     2
  c          cc3     3
需要查询出来的结果是每个类别的头2条记录，按排序进行排序，结果如下：
类别编号   说明     排序
  a          aa      1
  a          aa2     2
  b          bb      1
  b          bb2     2
  c          cc      1
  c          cc2     2
谢谢各位了！只要测试通过马上给分！
if object_id('pubs..t1') is not null
   drop table t1
gocreate table t1(
类别编号 varchar(10),
说明 varchar(10),
排序 int
)
insert into t1(类别编号,说明,排序) values('a','aa',1)
insert into t1(类别编号,说明,排序) values('a','aa2',2)
insert into t1(类别编号,说明,排序) values('a','aa3',3)
insert into t1(类别编号,说明,排序) values('b','bb',1)
insert into t1(类别编号,说明,排序) values('b','bb2',2)
insert into t1(类别编号,说明,排序) values('b','bb3',3)
insert into t1(类别编号,说明,排序) values('c','cc',1)
insert into t1(类别编号,说明,排序) values('c','cc2',2)
insert into t1(类别编号,说明,排序) values('c','cc3',3)select * from t1 as t
where (select count(*) from t1 where 类别编号 = t.类别编号 and 排序 < t.排序) < 2drop table t1
类别编号       说明         排序          
---------- ---------- ----------- 
a          aa         1
a          aa2        2
b          bb         1
b          bb2        2
c          cc         1
c          cc2        2（所影响的行数为 6 行）

Answer 4

productid  Nclassid  images                                      dateandtime
23   2    ../upimages/2006112415463319634.jpg 2006-11-24 00:00:00
13   1    ../upimages/200210251152894591.jpg 2002-10-25 00:00:00
21   1    ../upimages/200210251171374227.jpg 2002-10-25 00:00:00
15   3    ../upimages/20021025118643951.jpg 花 2002-10-25 00:00:00
16   3   ../upimages/200210251205746110.jpg 花 2002-10-25 00:00:00
18   1   ../upimages/200210251233982035.jpg 花 2002-10-25 00:00:00
现在按照Nclassid分类查询出最近(按dateandtime)的图片.如何做?有的分类没有加上时间啊。