求救SOS。sql排序问题。字段数据:id,name
id name
空 李四
1 张三
3 王五
空 豆腐
1 豆腐脑
3 冬瓜排序后,ID为空的保持不动
id name
空 李四
1 张三
1 豆腐脑
空 豆腐
3 王五
3 冬瓜
id name
空 李四
1 张三
3 王五
空 豆腐
1 豆腐脑
3 冬瓜排序后,ID为空的保持不动
id name
空 李四
1 张三
1 豆腐脑
空 豆腐
3 王五
3 冬瓜
select * from table order by case when id is null then 100000 else id end;
2楼的朋友,你的方法是把id为空的放到最后面去了。我要实现的是 。id为空的在原行保持不动!!! 也就是说。id为空的字段不会受到排序的影像,在哪行就是那行!
id name
空 李四
1 张三
3 王五
空 豆腐
1 张三adc
1 豆腐脑
3 冬瓜
---
这个排序是什么?
-- Author: happyflystone
-- Date : 2009-03-30 21:11:54
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(id NVARCHAR(1),name NVARCHAR(3))
Go
INSERT INTO ta
SELECT '空','李四' UNION ALL
SELECT '1','张三' UNION ALL
SELECT '3','王五' UNION ALL
SELECT '空','豆腐' UNION ALL
SELECT '1','豆腐脑' UNION ALL
SELECT '3','冬瓜' UNION ALL
SELECT '1','豆腐脑' UNION ALL
SELECT '3','冬瓜'
GO
--Start
alter table ta add rid int identity(1,1)
alter table ta add Pid int
goupdate ta
set pid = rid where id = '空'
go
create CLUSTERED index idx_id on ta(id)
go
declare @j int,@i int
set @j = 1
declare cur cursor for select pid from ta where pid is null order by id
OPEN cur
FETCH cur INTO @i
WHILE @@FETCH_STATUS>=0
BEGIN
while (exists(select 1 from ta where pid = @j))
set @j = @j + 1
UPDATE ta SET pid = @j
WHERE CURRENT OF cur
set @j = @j + 1
FETCH cur INTO @i
END
CLOSE cur
DEALLOCATE cur
SELECT
*
FROM
TA
order by pid,rid--Result:
/*
id name rid Pid
---- ---- ----------- -----------
空 李四 1 1
1 张三 2 2
1 豆腐脑 5 3
空 豆腐 4 4
1 豆腐脑 7 5
3 冬瓜 8 6
3 冬瓜 6 7
3 王五 3 8*/
--End
看我的解法:
原始数据表:
ID USERNAME
Lyn
3 Grubby
1 Fly
Moon
3 Sky
1 TH000/***建张单列的序号表*************************/
create table new_id as
select rownum new_id from tmp_user;/***一句sql实现*****************************/
select b.new_id,id,username from
(select rownum tmp_id,id,username from (select id,username from tmp_user where id is not null order by id)) a,
(select rownum tmp_id,new_id from new_id where new_id not in(
select new_id from (select new_id,username from (select rownum new_id,id,username from tmp_user) where id is null)
)) b where a.tmp_id = b.tmp_id
union select new_id,id,username from (select rownum new_id,id,username from tmp_user) where id is null;
输出结果:
ID USERNAME
Lyn
1 Fly
1 TH000
Moon
3 Grubby
3 Sky
create table t(x int,y varchar2(10));
insert into T (X, Y) values (null, 'A');
insert into T (X, Y) values (1, 'B');
insert into T (X, Y) values (2, 'C');
insert into T (X, Y) values (null, 'D');
insert into T (X, Y) values (1, 'E');
insert into T (X, Y) values (1, 'F');
insert into T (X, Y) values (3, 'G');
insert into T (X, Y) values (3, 'H');
insert into T (X, Y) values (4, 'I');SQL> select * from t;
X Y
----- ------------------------------
A
1 B
2 C
D
1 E
1 F
3 G
3 H
4 ISELECT t.*,
ROW_NUMBER() OVER(ORDER BY ROWID) - 1.1 A,
ROW_NUMBER() OVER(ORDER BY X, ROWID) B
FROM T
ORDER BY NVL2(X, B, A); X Y A B
----- ------------------------------ ---------- ----------
A -0.1 8
1 B 0.9 1
1 E 3.9 2
D 2.9 9
1 F 4.9 3
2 C 1.9 4
3 G 5.9 5
3 H 6.9 6
4 I 7.9 7
---------- ----------
a
2 b
1 c
d
2 e
1 f
3 g
3 h
4 i
d
s X Y
---------- ----------
1 d
4 a已选择13行。TESTUSER at orcl>select t_table_5.*,
2 row_number() over(order by rowid) - 1.1 A,
3 row_number() over(order by X, rowid) B
4 from t_table_5
5 order by nvl2(X,B,A); X Y A B
---------- ---------- ---------- ----------
a -.1 10
1 c 1.9 1
1 f 4.9 2
d 2.9 11
1 d 10.9 3
2 b .9 4
2 e 3.9 5
3 g 5.9 6
3 h 6.9 7
4 i 7.9 8
d 8.9 12 X Y A B
---------- ---------- ---------- ----------
4 a 11.9 9
s 9.9 13已选择13行。
遇到类似的排序,用order by 是不可能实现了,但是只要是用户想要的,老板想要的
报表格式你能说不搞出来吗。实现的方法很多种,只要能达到,查询效率高就可以。
只有用存储过程加临时表可以实现