有如下内容的表:
字段1 字段2 字段3
A a1 1
A a2 2
B b1 3
B b2 4
B b3 5
C c1 6
C c2 7目的:要实现字段1分组有序的显示。A-B-C或C-B-A曾试过如下方法但都没得到想要的结果:
SELECT 字段1 FROM TBName GROUP BY 字段1 ORDER BY 字段1//此语句不能执行
SELECT 字段1 FROM (SELECT * FROM TBName GROUP BY 字段1) ORDER BY 字段1//此语句不能执行
SELECT 字段1 FROM (SELECT * FROM TBName ORDER BY 字段1) GROUP BY 字段1//此语句能执行但结果是无序的。
请指点一二...
字段1 字段2 字段3
A a1 1
A a2 2
B b1 3
B b2 4
B b3 5
C c1 6
C c2 7目的:要实现字段1分组有序的显示。A-B-C或C-B-A曾试过如下方法但都没得到想要的结果:
SELECT 字段1 FROM TBName GROUP BY 字段1 ORDER BY 字段1//此语句不能执行
SELECT 字段1 FROM (SELECT * FROM TBName GROUP BY 字段1) ORDER BY 字段1//此语句不能执行
SELECT 字段1 FROM (SELECT * FROM TBName ORDER BY 字段1) GROUP BY 字段1//此语句能执行但结果是无序的。
请指点一二...
字段1 字段2 字段3
A a1 1
A a2 2
B b1 3
B b2 4
B b3 5
C c1 6
C c2 7 目的:要实现字段1分组按字段3有序的显示。曾试过如下方法但都没得到想要的结果:
SELECT 字段1 FROM TBName GROUP BY 字段1 ORDER BY 字段3//此语句不能执行
SELECT 字段1 FROM (SELECT * FROM TBName GROUP BY 字段1) ORDER BY 字段3//此语句不能执行
SELECT 字段1 FROM (SELECT * FROM TBName ORDER BY 字段3) GROUP BY 字段1//此语句能执行但结果是无序的。
请指点一二...
字段1
A
B
C
A
A
B
B
B
C
C
用:onejune4450 的办法并不能完全解决问题。因为输出结果要按字段3有序输出
字段1 字段2 字段3
A a1 1
A a2 2
C c1 3
C c2 4
B b1 5
B b2 6
B b3 7这样的话我所要得到的应该是:
A
C
B
DECLARE @T TABLE(COLUMN1 CHAR(2),COLUMN2 CHAR(6),COLUMN3 INT)
INSERT INTO @T VALUES('A','a1',1)
INSERT INTO @T VALUES('A','a2',2)
INSERT INTO @T VALUES('C','c1',3)
INSERT INTO @T VALUES('C','c2',4)
INSERT INTO @T VALUES('B','b1',5)
INSERT INTO @T VALUES('B','b2',6)
INSERT INTO @T VALUES('B','b3',7)DECLARE @C1 CHAR(2)
DECLARE @T_C1 TABLE(C1 CHAR(2))
DECLARE CUR_CS CURSOR FOR SELECT COLUMN1 FROM @T
OPEN CUR_CS
FETCH NEXT FROM CUR_CS INTO @C1
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO @T_C1 SELECT @C1 WHERE @C1 NOT IN (SELECT * FROM @T_C1)
FETCH NEXT FROM CUR_CS INTO @C1
END
CLOSE CUR_CS
DEALLOCATE CUR_CSSELECT * FROM @T_C1
DECLARE @T TABLE(COLUMN1 CHAR(2),COLUMN2 CHAR(6),COLUMN3 INT)
INSERT INTO @T VALUES('A','a1',1)
INSERT INTO @T VALUES('A','a2',2)
INSERT INTO @T VALUES('C','c1',3)
INSERT INTO @T VALUES('C','c2',4)
INSERT INTO @T VALUES('B','b1',5)
INSERT INTO @T VALUES('B','b2',6)
INSERT INTO @T VALUES('B','b3',7)DECLARE @C1 CHAR(2)
DECLARE @T_C1 TABLE(C1 CHAR(2))
DECLARE CUR_CS CURSOR FOR SELECT COLUMN1 FROM @T
OPEN CUR_CS
FETCH NEXT FROM CUR_CS INTO @C1
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO @T_C1 SELECT @C1 WHERE @C1 NOT IN (SELECT * FROM @T_C1)
FETCH NEXT FROM CUR_CS INTO @C1
END
CLOSE CUR_CS
DEALLOCATE CUR_CSSELECT * FROM @T_C1
(SELECT col1,MAX(col3),RANK() OVER(ORDER BY MAX(col3)) AS RK FROM yourtable GROUP BY col1) TAB