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

[ZOU_SEAFARER]

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 (不在指定范围的空格放最后)

[ZOU_SEAFARER]

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

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

[ZOU_SEAFARER]

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

[paoluo]

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

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

PS：

我不是鯊魚，是魚 ：)

[clear_zero]

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

[ZOU_SEAFARER]

XIEXIE

[vbman2003]

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

[ZOU_SEAFARER]

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

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

(表里所有的V写入一个临时表,然后所有的M,在是所有的P,最后所有的空格数据写入临时表)

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

[paoluo]

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

[paoluo]

--創建測試環境
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
--測試
EXEC 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
*/

[vbman2003]

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

[vbman2003]

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

[ZOU_SEAFARER]

paoluo(一天到晚游泳的鱼)



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

[ZOU_SEAFARER]

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

还有 的问题就是我的确表中字段30多个
Create Table #T(ROW Int Identity(1, 1), [DESC] Varchar(10), fValue Int)


是不是要写很多了??

[paoluo]

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




还有 的问题就是我的确表中字段30多个
Create Table #T(ROW Int Identity(1, 1), [DESC] Varchar(10), fValue Int)
--------------

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

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

[ZOU_SEAFARER]

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 = "EXEC SP_TEST 'V,M,P'"
If RunSQL(SQL) <> R_OK Then 'ＳＱＬ実行
Call ErrMsg(1) '
Exit Function '
End If

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

[paoluo]

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

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



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

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

[ZOU_SEAFARER]

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


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

[paoluo]

沒看明白，什麼意思？

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

[ZOU_SEAFARER]

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] "

[洋溢2016]

看看~