--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([col1] [nvarchar](10),[col2] [int]) INSERT INTO [tb] SELECT 'a','1' UNION ALL SELECT 'a','2' UNION ALL SELECT 'b','1' UNION ALL SELECT 'b','2' UNION ALL SELECT 'b','3' -->SQL查询如下: SELECT rn = ROW_NUMBER()OVER(ORDER BY GETDATE()), col1, SUM(col2) col2 FROM tb GROUP BY col1 /* rn col1 col2 -------------------- ---------- ----------- 1 a 3 2 b 6(2 行受影响) */--2kSELECT rn = IDENTITY(INT), col1, SUM(col2) col2 INTO # FROM tb GROUP BY col1 SELECT * FROM # DROP TABLE # /* rn col1 col2 -------------------- ---------- ----------- 1 a 3 2 b 6(2 行受影响) */
如果sql2000用2楼的 如果sql2005用1楼的
楼主的意思是在分组内排序吧? 如果是这样的话,在2005可以用row_number() over (partition by *** order by ***)
没错,就是先分组再排序。在2000中怎样实现?4楼的好像不行: SELECT rn = IDENTITY(INT), col1, SUM(col2) col2 INTO # FROM tb GROUP BY col1
我要将问题重新描述一次。 2000里 ,分组内排序,怎样实现? 某表中的字段和数据: col1 col2 a b a b b 现在我想要得到: col1 col2 a 1 a 2 b 1 b 2 b 3 这种应该是分组内排序的问题吧,求解。谢谢
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb (id int,col1 varchar(10)) INSERT INTO tb SELECT 1,'a' UNION ALL SELECT 2,'b' UNION ALL SELECT 3,'a' UNION ALL SELECT 4,'b' UNION ALL SELECT 5,'b' select * from tb /* id col1 ----------- ---------- 1 a 2 b 3 a 4 b 5 b(5 row(s) affected)*/--sql2000对于源表,要求有原始的排序才好组内排序,如果没有的,要先加上。select a.col1, (select count(1) from tb b where a.col1 = b.col1 and b.id <= a.id) as col2 from tb a order by col1,col2 /* col1 col2 ---------- ----------- a 1 a 2 b 1 b 2 b 3(5 row(s) affected) */
from tb
group by col1
add id int identity(1,1)
jf.....................................................
牛人真多,真快
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([col1] [nvarchar](10),[col2] [int])
INSERT INTO [tb]
SELECT 'a','1' UNION ALL
SELECT 'a','2' UNION ALL
SELECT 'b','1' UNION ALL
SELECT 'b','2' UNION ALL
SELECT 'b','3'
-->SQL查询如下:
SELECT rn = ROW_NUMBER()OVER(ORDER BY GETDATE()), col1, SUM(col2) col2
FROM tb
GROUP BY col1
/*
rn col1 col2
-------------------- ---------- -----------
1 a 3
2 b 6(2 行受影响)
*/--2kSELECT rn = IDENTITY(INT), col1, SUM(col2) col2 INTO #
FROM tb
GROUP BY col1
SELECT * FROM #
DROP TABLE #
/*
rn col1 col2
-------------------- ---------- -----------
1 a 3
2 b 6(2 行受影响)
*/
如果sql2005用1楼的
如果是这样的话,在2005可以用row_number() over (partition by *** order by ***)
SELECT rn = IDENTITY(INT), col1, SUM(col2) col2 INTO #
FROM tb
GROUP BY col1
(前些日子情况稍好些.)实在忍无可忍,只好发帖了质问了.环境: windows2003 + IE6请问:是我系统(设置)的问题,还是CSDN开发人员水平太烂,居然烂到如此地步?另外:发此帖登录三次,内容重写两遍(因为发贴后又要我登录,然后写的内容就无踪影了,只好重写).在此对CSDN这种机制发表自己强烈的愤怒和进行强烈的抗议!如有类似经历者,请指点我该如何操作,谢谢.最后,我居然发不出帖!!!请小F或roy_888帮我发出此帖,谢谢!
2000里 ,分组内排序,怎样实现?
某表中的字段和数据:
col1 col2
a
b
a
b
b
现在我想要得到:
col1 col2
a 1
a 2
b 1
b 2
b 3
这种应该是分组内排序的问题吧,求解。谢谢
DROP TABLE tb
GO
CREATE TABLE tb (id int,col1 varchar(10))
INSERT INTO tb
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 4,'b' UNION ALL
SELECT 5,'b' select * from tb
/*
id col1
----------- ----------
1 a
2 b
3 a
4 b
5 b(5 row(s) affected)*/--sql2000对于源表,要求有原始的排序才好组内排序,如果没有的,要先加上。select
a.col1,
(select count(1) from tb b where a.col1 = b.col1 and b.id <= a.id) as col2
from tb a
order by col1,col2
/*
col1 col2
---------- -----------
a 1
a 2
b 1
b 2
b 3(5 row(s) affected)
*/
多谢你的方法,我今天上午想了很长时间,可是没有想出来.还是你做到了.今天又学到东西了.
CSDN真是个学习的好地方.