您的位置：程序门 -> vb -> 数据库（包含打印，安装，报表)

早晨起来问一个数据库方面的（按某字段排序)

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

早晨起来问一个数据库方面的（按某字段排序)[已结贴,结贴人:zou_seafarer]

发表于：2007-07-06 08:50:47 楼主

mssql 2000 + vb6.0

现在有一个表（没有key)
row desc fvalue
1 p 15
2 p 20
3 p 23
4 v 15
5 16
6 v 21
7 m 22
8 m 23

需要按指定规则排序比如 v , m ,p 排序后改变行号继续保存在这个表中
可以增加临时表,方便导动数据等,
结果应该是
row desc fvalue
1 v 15 （其中排序关键字段相同的确时候,
2 v 21 行做第二排序关键字)
3 m 22
4 m 23
5 p 15
6 p 20
7 p 23
8 16 （不在指定范围的空格放最后)

发表于：2007-07-06 09:10:091楼得分:0

这个弄起来比较烦，应该可以实现吧？
手边没mssql，想想先

发表于：2007-07-06 09:19:382楼得分:0

2005 实现比较容易点,2000下我把表里所有的v写入一个临时表
然后所有的m,在是所有的p,最后所有的空格数据写入临时表
然后在临时表里面增加一个自加一的自段,再把自加一字段的值付给行号,这样一来就就可以删除自加一字段,再把原表清空,把临时表数据导入,!!

看起来好象这样可以,实际上不能操作,因为

（表里所有的v写入一个临时表,然后所有的m,在是所有的p,最后所有的空格数据写入临时表)

这样写入临时表的数据在表里面的物理存储没有规律,最后写入的空格数据在表中任意出现,并不是追加到表的末尾,这个又比较不好做了!!

发表于：2007-07-06 09:28:523楼得分:0

利用charindex函數來做，不是很複雜。

发表于：2007-07-06 09:29:134楼得分:50

--創建測試環境
create table test
（row int,
[desc] varchar（10),
fvalue int)
insert test select 1, 'p ', 15
union all select 2, 'p ', 20
union all select 3, 'p ', 23
union all select 4, 'v ', 15
union all select 5, ' ', 16
union all select 6, 'v ', 21
union all select 7, 'm ', 22
union all select 8, 'm ', 23
go
--創建存儲過程
create procedure sp_test（@orderby varchar（100))
as
begin
create table #t（row int identity（1, 1), [desc] varchar（10), fvalue int)
insert #t （[desc], fvalue) select [desc], fvalue from test where rtrim（[desc]) != ' ' order by charindex（[desc], @orderby), row
insert #t （[desc], fvalue) select [desc], fvalue from test where rtrim（[desc]) = ' '
select * from #t
drop table #t
end
go
--測試
ＥＸＥＣ sp_test 'v,m,p '
go
--刪除測試環境
drop table test
drop procedure sp_test
--結果
/*
row desc fvalue
1 v 15
2 v 21
3 m 22
4 m 23
5 p 15
6 p 20
7 p 23
8 16
*/

发表于：2007-07-06 09:32:425楼得分:0

比较笨的办法就是在order by语句中通过case语句给对应的字段值前加上一个序号，但这样一来语句会很庞杂，可读性不好。access有个swicth函数写起来还好看一点。sql中不知道有没相应的东东。还有pgsql可以在sql语句中用数组，sql应该也可以吧，这样的话可以将规则放在数组里，通过数组序号排序。手边没sql数据库，你去sql版问一下比较好，那里sql语句牛人多多

发表于：2007-07-06 09:33:526楼得分:0

呵呵，鱼儿来了
我就学习吧

发表于：2007-07-06 09:37:167楼得分:0

paoluo（一天到晚游泳的鱼)

一天到晚游泳的鱼是鲨鱼吗??? 鲨鱼不休息

发表于：2007-07-06 10:12:208楼得分:0

现在就是转化到vb中了,还不晓得如何转化进去呢?

还有的问题就是我的确表中字段30多个
create table #t（row int identity（1, 1), [desc] varchar（10), fvalue int)

是不是要写很多了??

发表于：2007-07-06 10:32:209楼得分:0

现在就是转化到vb中了,还不晓得如何转化进去呢?
-----------------
我覺得直接修改原表的數據不是很好，所以這個存儲過程返回的是一個結果集，你直接在vb中調用存儲過程就可以。

还有的问题就是我的确表中字段30多个
create table #t（row int identity（1, 1), [desc] varchar（10), fvalue int)
--------------

你原表中的第一列是否是自增列？還是普通int列？

如果你的原表的第一列不是自增列，就需要在建表的時候將列一一寫出來，就和你建造你的原表一樣，也不算太麻煩。

发表于：2007-07-06 10:48:4410楼得分:0

sql = "select " 'creat empty table 复制表结构
sql = sql & "* into "
sql = sql & temp_table
sql = sql & " from " & test
sql = sql & " where "
sql = sql & " 1=2 "
if runsql（sql) <> r_ok then 'ｓｑｌ実行
call errmsg（1) 'エラーメッセージ
exit function 'サブルーチンの終了
end if

call creattabsql（ "#t ") '建立一个临时表,和原表一模一样,包括默认值等
sql = "alter table #t alter column fewcrow int identity（1,1) " 修改临时表的行字段为自增字段,原表是普通的int字段
if runsql（sql) <> r_ok then 'ｓｑｌ実行
call errmsg（1) '
exit function '
end if

sql = "create procedure sp_test（@orderby varchar（100)) "
sql = sql & " as "
sql = sql & " begin "
'sql = sql & " create table #t（row int identity（1, 1), [desc] varchar（10), fvalue int) "
sql = sql & " insert #t （*) select * from test where rtrim（[fewcdia]) != ' ' order by charindex（[fewcdia], @orderby), fewcrow "
sql = sql & " insert #t （*) select * from test where rtrim（[fewcdia]) = ' ' order by fewcrow "
sql = sql & " select * into " & temp_table & " from #t " '把结果导入一个表中
sql = sql & " drop table #t "
sql = sql & " end "
if runsql（sql) <> r_ok then 'ｓｑｌ実行
call errmsg（1) '
exit function '
end if

sql = "ＥＸＥＣ sp_test 'v,m,p ' "
if runsql（sql) <> r_ok then 'ｓｑｌ実行
call errmsg（1) '
exit function '
end if

这样我想temp_table表中就是我们的结果了!不知道是否可行?

发表于：2007-07-06 10:56:4211楼得分:0

不是這麼寫的，你直接在數據庫中建立存儲過程，不需要用vb代碼去建立這個存儲過程。

你在vb中只需要調用sp_test這個存儲過程，並得到存儲過程的返回集即可。

你那樣改列為自增列是有問題的。

另外，存儲過程需要做小部分修改，將建表的語句改為和你實際表一樣即可，不要去管原表中的row是否是自增列了。

发表于：2007-07-06 11:27:5912楼得分:0

也许是表有问题,应用的结果是

按照制定规律排列,不过每16条后面就有3条空格数据,和我以前做的另外方法一样,
a
a
a
a
a
a
a
a
.
.
.
b（16条)
3条空格
b
b
b

发表于：2007-07-06 11:31:4313楼得分:0

沒看明白，什麼意思？

固定幾行之後要有幾行空格？

发表于：2007-07-06 12:58:4614楼得分:0

sub sp_sort（s_table as string, d_table, f_key as string, optional s_key as string)
dim sql as string
dim strothercol as string
strothercol = "[fewcid], "
strothercol = strothercol & "[fewcdatmasid] , "
strothercol = strothercol & "[fewcdatno] , "
' strothercol = strothercol & "[fewcrow] , "
'+++++++++++++++++++++++++++++++++++++++20070709+++++++++++++++++++++++++++++++++
strothercol = strothercol & "[fewcdia] , "
strothercol = strothercol & "[fewcpage] , "
strothercol = strothercol & "[fewcfr] , "
'+++++++++++++++++++++++++++++++++++++++20070709+++++++++++++++++++++++++++++++++
strothercol = strothercol & "[fewcgaimasid] , "
strothercol = strothercol & "[fewccircuit] , "
strothercol = strothercol & "[fewcdivision] , "
strothercol = strothercol & "[fewcdivno] , "
strothercol = strothercol & "[fewcdivcheck] , "
strothercol = strothercol & "[fewcdekid1], "
strothercol = strothercol & "[fewcdekid2], "
strothercol = strothercol & "[fewcdestination1] , "
strothercol = strothercol & "[fewcdestination2] , "
strothercol = strothercol & "[fewcclrmasid] , "
strothercol = strothercol & "[fewccutprint], "
strothercol = strothercol & "[fewcmark] , "
strothercol = strothercol & "[fewclength] , "
strothercol = strothercol & "[fewcforceprint] , "
strothercol = strothercol & "[fewcsetflag1] , "
strothercol = strothercol & "[fewcforergb1] , "
strothercol = strothercol & "[fewcbackrgb1] , "
strothercol = strothercol & "[fewcsetflag2] , "
strothercol = strothercol & "[fewcforergb2] , "
strothercol = strothercol & "[fewcbackrgb2] , "
strothercol = strothercol & "[fewcsetflag3] , "
strothercol = strothercol & "[fewcforergb3] , "
strothercol = strothercol & "[fewcbackrgb3] , "
strothercol = strothercol & "[fewcsetflag4] , "
strothercol = strothercol & "[fewcforergb4] , "
strothercol = strothercol & "[fewcbackrgb4] , "
strothercol = strothercol & "[fewcsetflag5] , "
strothercol = strothercol & "[fewcforergb5] , "
strothercol = strothercol & "[fewcbackrgb5] , "
strothercol = strothercol & "[fewcsetflag6] , "
strothercol = strothercol & "[fewcforergb6] , "
strothercol = strothercol & "[fewcbackrgb6] , "
strothercol = strothercol & "[fewcsetflag7] , "
strothercol = strothercol & "[fewcforergb7] , "
strothercol = strothercol & "[fewcbackrgb7] , "
strothercol = strothercol & "[fewcsetflag8] , "
strothercol = strothercol & "[fewcforergb8] , "
strothercol = strothercol & "[fewcbackrgb8] , "
strothercol = strothercol & "[fewcsetflag9] , "
strothercol = strothercol & "[fewcforergb9] , "
strothercol = strothercol & "[fewcbackrgb9] , "
strothercol = strothercol & "[fewcsetflag10] , "
strothercol = strothercol & "[fewcforergb10] , "
strothercol = strothercol & "[fewcbackrgb10] , "
strothercol = strothercol & "[fewcsetflag11] , "
strothercol = strothercol & "[fewcforergb11] , "
strothercol = strothercol & "[fewcbackrgb11] , "
strothercol = strothercol & "[fewcsetflag12] , "
strothercol = strothercol & "[fewcforergb12] , "
strothercol = strothercol & "[fewcbackrgb12] , "
strothercol = strothercol & "[fewcprint], "
strothercol = strothercol & "[fewcprintover], "
strothercol = strothercol & "[fewccutprintover], "
strothercol = strothercol & "[fewcdemprintover], "
strothercol = strothercol & "[fewcupdivision] , "
strothercol = strothercol & "[fewcupdivposition] , "
strothercol = strothercol & "[fewcdowndivision] , "
strothercol = strothercol & "[fewcdowndivposition], "
strothercol = strothercol & "[fewc01division] , "
strothercol = strothercol & "[fewc01divposition], "
strothercol = strothercol & "[fewc02division] , "
strothercol = strothercol & "[fewc02divposition], "
strothercol = strothercol & "[fewc03division] , "
strothercol = strothercol & "[fewc03divposition], "
strothercol = strothercol & "[fewc04division] , "
strothercol = strothercol & "[fewc04divposition], "
strothercol = strothercol & "[fewc05division] , "
strothercol = strothercol & "[fewc05divposition], "
strothercol = strothercol & "[fewc06division] , "
strothercol = strothercol & "[fewc06divposition], "
strothercol = strothercol & "[fewc07division] , "
strothercol = strothercol & "[fewc07divposition], "
strothercol = strothercol & "[fewc08division] , "
strothercol = strothercol & "[fewc08divposition], "
strothercol = strothercol & "[fewc09division] , "
strothercol = strothercol & "[fewc09divposition], "
strothercol = strothercol & "[fewc10division] , "
strothercol = strothercol & "[fewc10divposition], "
strothercol = strothercol & "[fewc11division] , "
strothercol = strothercol & "[fewc11divposition], "
strothercol = strothercol & "[fewc12division] , "
strothercol = strothercol & "[fewc12divposition], "
strothercol = strothercol & "[fewc13division] , "
strothercol = strothercol & "[fewc13divposition], "
strothercol = strothercol & "[fewc14division] , "
strothercol = strothercol & "[fewc14divposition], "
strothercol = strothercol & "[fewc15division] , "
strothercol = strothercol & "[fewc15divposition], "
strothercol = strothercol & "[fewc16division] , "
strothercol = strothercol & "[fewc16divposition], "
strothercol = strothercol & "[fewc17division] , "
strothercol = strothercol & "[fewc17divposition], "
strothercol = strothercol & "[fewc18division] , "
strothercol = strothercol & "[fewc18divposition], "
strothercol = strothercol & "[fewc19division] , "
strothercol = strothercol & "[fewc19divposition], "
strothercol = strothercol & "[fewc20division] , "
strothercol = strothercol & "[fewc20divposition], "
strothercol = strothercol & "[fewc21division] , "
strothercol = strothercol & "[fewc21divposition], "
strothercol = strothercol & "[fewc22division] , "
strothercol = strothercol & "[fewc22divposition], "
strothercol = strothercol & "[fewc23division] , "
strothercol = strothercol & "[fewc23divposition], "
strothercol = strothercol & "[fewc24division] , "
strothercol = strothercol & "[fewc24divposition], "
strothercol = strothercol & "[fewc25division] , "
strothercol = strothercol & "[fewc25divposition], "
strothercol = strothercol & "[fewc26division] , "
strothercol = strothercol & "[fewc26divposition], "
strothercol = strothercol & "[fewc27division] , "
strothercol = strothercol & "[fewc27divposition], "
strothercol = strothercol & "[fewc28division] , "
strothercol = strothercol & "[fewc28divposition], "
strothercol = strothercol & "[fewc29division] , "
strothercol = strothercol & "[fewc29divposition], "
strothercol = strothercol & "[fewc30division] , "
strothercol = strothercol & "[fewc30divposition], "
strothercol = strothercol & "[fewcdeletekbn] , "
strothercol = strothercol & "[fewcmikakunin] , "
strothercol = strothercol & "[fewccreatedate] , "
strothercol = strothercol & "[fewccreatename] , "
strothercol = strothercol & "[fewcupdatedate] , "
strothercol = strothercol & "[fewcupdatename] "

发表于：2007-07-06 13:22:0215楼得分:0

这个涵数代码太多,不要看上面的代码了

paoluo（一天到晚游泳的鱼) 现在也不晓的哪里出问题了,返回的记录集合是

p,v m,空格,排列
参数输入的是v,m,p , 第二关键字提前作用了,好象,代码如下:
if s_key = vbnullstring then
sql = sql & " insert #t （ " & strothercol & ") select " & strothercol & " from " & s_table & " where rtrim（[ " & f_key & "]) != ' ' order by charindex（[ " & f_key & "], @orderby),fewcrow "
sql = sql & " insert #t （ " & strothercol & ") select " & strothercol & " from " & s_table & " where rtrim（[ " & f_key & "]) = ' ' order by fewcrow "
else
sql = sql & " insert #t （ " & strothercol & ") select " & strothercol & " from " & s_table & " where rtrim（[ " & f_key & "]) != ' ' order by charindex（[ " & f_key & "], @orderby), " & s_key & " , fewcrow "
sql = sql & " insert #t （ " & strothercol & ") select " & strothercol & " from " & s_table & " where rtrim（[ " & f_key & "]) = ' ' order by " & s_key & ",fewcrow "
end if

发表于：2007-07-06 13:22:4716楼得分:0

@orderby = "v,m,p "

发表于：2007-07-06 14:03:0917楼得分:0

暈，都說了不用在vb中寫這些代碼，存儲過程的代碼直接在數據庫中寫即可。

生成了存儲過程，在vb中直接調用存儲過程，得到存儲過程的結果集即可。

发表于：2007-07-06 15:50:5618楼得分:0

呵呵,数据库不归我做哈]

数据库别人设置好了,我只管写代码!

发表于：2007-07-06 15:53:2319楼得分:0

insert #t （[desc], fvalue) select [desc], fvalue from test where rtrim（[desc]) != ' ' order by charindex（[desc], @orderby), row

我用循环了!!现在循环解决问题了,谢谢鲨鱼

for i = 0 to ubound（sort)
insert #t （[desc], fvalue) select [desc], fvalue from test where rtrim（[desc]) = 'sort（i) ' order by row
runsql（)................
next

发表于：2007-07-06 15:55:3020楼得分:0

算了，如果你實現了，就ok了。

其實這種東西，我習慣直接用數據庫來做。

ps：

我不是鯊魚，是魚：)

发表于：2007-07-06 16:00:5721楼得分:0

特别复杂的我就写存储过程了

发表于：2007-07-06 16:14:2322楼得分:0

xiexie

发表于：2007-07-07 08:25:1823楼得分:0

看看~

下一篇：请各位大侠支招vb+access的问题!!
上一篇：vb 中 datareport控件如何动态显示图片

其他资讯