基于 varchar 的相关子查询的数字排序
让我说,我有一个包含一些通用数据持有者列的表(因此,原始代码的开发人员似乎选择使用字符串类型)。这些列可以保存所有数字、字母数字或它们的组合。
让我说我有一个相关的子查询,例如;
SOME_CORELATED_QUERY=(SOME SELECT SQL)
假设我有一个 order by 子句;
order by ID,SOME_CORELATED_QUERY
现在,由于这些字段都是基于 varchar 的,所以默认情况下我无法对它们进行数字排序。
我可以为我的相关子查询尝试这个;
SOME_CORELATED_QUERY=cast((SOME SELECT SQL) as int)
但上面的问题是,并非所有数据都是数字,我会收到错误;
Conversion failed when converting the varchar value '304L' to data type int.
所以我在这里找到了一个解决方案,它基本上使用了对我来说可行的空间问题,但问题是我无法将其应用于相关的子查询。
如果我尝试将它应用到我的表列 ID 上,一切正常;
order by space(50-len(ID))+ID, SOME_CORELATED_QUERY
但这并不能作为解决方案,因为我需要对 SOME_CORELATED_QUERY 进行数字排序。我尝试一下;
order by space(50-len(SOME_CORELATED_QUERY))+SOME_CORELATED_QUERY
但我收到一条错误消息,因为我的相关子查询在某种程度上不被 SQL 函数(例如 space、cast、convert 等)识别:
Msg 207, Level 16, State 1, Line 48
Invalid column name 'SOME_CORELATED_QUERY'.
Msg 207, Level 16, State 1, Line 48
Invalid column name 'SOME_CORELATED_QUERY'.
所以基本上我需要在排序时使用 SQL 函数 space、cast、convert 来排序-时间。
注意:更改数据库布局、字段类型是不可能的,因为布局是在我使用的程序上硬编码的,我只是为用户创建基于 SQL 的报告。
- 我也在研究mssql2005。
更新:感谢 Brad,我更新了问题文本以使用“相关子查询”,因为它是比虚拟列更好的描述。
Update2:这是所要求的完整sql;
select ID, S.STOCK_ID ,S.STOCK_CODE,S.STOK_SHORT_NAME,S.UNIT1_NAME UNIT_NAME,
PROPERTY_VALUE_NAME1=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_1),
PROPERTY_NAME1=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT, STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY1_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME2=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_2),
PROPERTY_NAME2=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY2_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME3=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_3),
PROPERTY_NAME3=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY3_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME4=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_4),
PROPERTY_NAME4=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY4_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME5=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_5),
PROPERTY_NAME5=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY5_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME6=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_6),
PROPERTY_NAME6=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY6_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME7=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_7),
PROPERTY_NAME7=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY7_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
isnull(SUM(case IN_OUT when 1 then UNIT1_VALUE else 0 end),0) IN_VALUE,
isnull(SUM(case IN_OUT when 2 then UNIT1_VALUE else 0 end),0) OUT_VALUE
from STOCK_PROPERTY_DETAILS a, STOCK b, STOCK_TRANSACTION_ROWS DHS, STOCK S, WAREHOUSE D
where a.STOCK_ID=b.STOCK_ID and b.STOCK_ID *= DHS.SHD_ID and DHS.SHD = 1 and ID>0 and B.UPPER_STOCK_ID=S.STOCK_ID and D.WAREHOUSE_ID*=DHS.WAREHOUSE_ID
group by S.STOCK_ID ,S.STOCK_CODE,S.STOK_SHORT_NAME,S.UNIT1_NAME, PROPERTY_VALUE_ID_1,PROPERTY_VALUE_ID_2,PROPERTY_VALUE_ID_3,PROPERTY_VALUE_ID_4,PROPERTY_VALUE_ID_5,PROPERTY_VALUE_ID_6,PROPERTY_VALUE_ID_7,ID
having 1=1 and ((isnull(SUM(case IN_OUT when 1 then UNIT1_VALUE else 0 end),0)-isnull(SUM(case IN_OUT when 2 then UNIT1_VALUE else 0 end),0)<>0 /*##ZERO#*/) or (not(1=1 /*##ZERO#*/)))
order by ID,PROPERTY_VALUE_NAME1,PROPERTY_VALUE_NAME2,PROPERTY_VALUE_NAME3,PROPERTY_VALUE_NAME4,PROPERTY_VALUE_NAME5,PROPERTY_VALUE_NAME6,PROPERTY_VALUE_NAME7
我想对 PROPERTY_VALUE_NAME1、PROPERTY_VALUE_NAME2 等进行排序。
let me say that i've a table with some generic-data-holder columns (so it seems developer of the original code selected to use string type). These columns can hold all numerics, alphanumerics or a mix of them.
Let me say i've a co-related subquery like;
SOME_CORELATED_QUERY=(SOME SELECT SQL)
and let's say that i've an order by clause;
order by ID,SOME_CORELATED_QUERY
now as these fields are all varchar based, i can't get them numerically sorted by default.
I can try this for my co-related subquery;
SOME_CORELATED_QUERY=cast((SOME SELECT SQL) as int)
but the problem with above is that, not all my data is numeric and i'll get an error;
Conversion failed when converting the varchar value '304L' to data type int.
So i found a solution here which basicly uses spaces which is viable for my problem but the problem is that i can't apply it to co-related subquery.
If i try to apply it on my table column ID, it all works okay;
order by space(50-len(ID))+ID, SOME_CORELATED_QUERY
but that doesn't work as a solution because i need a numeric sort on my SOME_CORELATED_QUERY. I try it like;
order by space(50-len(SOME_CORELATED_QUERY))+SOME_CORELATED_QUERY
but i get an error message because somewhat my co-related subquery is not recognized by SQL functions like space,cast, convert etc:
Msg 207, Level 16, State 1, Line 48
Invalid column name 'SOME_CORELATED_QUERY'.
Msg 207, Level 16, State 1, Line 48
Invalid column name 'SOME_CORELATED_QUERY'.
So basically i need way to use SQL function space,cast,convert on my co-related subquery on sort-time.
Note: Changing the database layout, field-type is not possible as the layout is hard-coded on the program i use, which i'm just creating SQL based reports for user.
- I'm also working on mssql2005.
Update: Thanks to Brad, i updated the question text to use 'co-related subqueries' as it was a better description than virtual columns.
Update2: Here's the full-sql as it's asked for;
select ID, S.STOCK_ID ,S.STOCK_CODE,S.STOK_SHORT_NAME,S.UNIT1_NAME UNIT_NAME,
PROPERTY_VALUE_NAME1=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_1),
PROPERTY_NAME1=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT, STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY1_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME2=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_2),
PROPERTY_NAME2=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY2_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME3=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_3),
PROPERTY_NAME3=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY3_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME4=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_4),
PROPERTY_NAME4=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY4_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME5=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_5),
PROPERTY_NAME5=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY5_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME6=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_6),
PROPERTY_NAME6=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY6_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME7=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_7),
PROPERTY_NAME7=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY7_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
isnull(SUM(case IN_OUT when 1 then UNIT1_VALUE else 0 end),0) IN_VALUE,
isnull(SUM(case IN_OUT when 2 then UNIT1_VALUE else 0 end),0) OUT_VALUE
from STOCK_PROPERTY_DETAILS a, STOCK b, STOCK_TRANSACTION_ROWS DHS, STOCK S, WAREHOUSE D
where a.STOCK_ID=b.STOCK_ID and b.STOCK_ID *= DHS.SHD_ID and DHS.SHD = 1 and ID>0 and B.UPPER_STOCK_ID=S.STOCK_ID and D.WAREHOUSE_ID*=DHS.WAREHOUSE_ID
group by S.STOCK_ID ,S.STOCK_CODE,S.STOK_SHORT_NAME,S.UNIT1_NAME, PROPERTY_VALUE_ID_1,PROPERTY_VALUE_ID_2,PROPERTY_VALUE_ID_3,PROPERTY_VALUE_ID_4,PROPERTY_VALUE_ID_5,PROPERTY_VALUE_ID_6,PROPERTY_VALUE_ID_7,ID
having 1=1 and ((isnull(SUM(case IN_OUT when 1 then UNIT1_VALUE else 0 end),0)-isnull(SUM(case IN_OUT when 2 then UNIT1_VALUE else 0 end),0)<>0 /*##ZERO#*/) or (not(1=1 /*##ZERO#*/)))
order by ID,PROPERTY_VALUE_NAME1,PROPERTY_VALUE_NAME2,PROPERTY_VALUE_NAME3,PROPERTY_VALUE_NAME4,PROPERTY_VALUE_NAME5,PROPERTY_VALUE_NAME6,PROPERTY_VALUE_NAME7
And i want to sort on PROPERTY_VALUE_NAME1, PROPERTY_VALUE_NAME2 and so..
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我无法测试它,因为我没有手头上的实例,但如果它允许您在其上使用 case 语句,类似这样的事情可能会起作用
希望这会以两种方式完成,第一种对数字中的数值进行排序排序并将 alpha 内容集中在 -1 的组中,然后由我们的第二部分按 alpha 排序
I can't test it as I don't have an instance to hand but if it will allow you to use a case statement on it something like this may work
Hopefully this would do it in two sorts, the first sorting numeric values in numeric order and lumping the alpha stuff in a group at -1 which would then get sorted by alpha by our second part
对于字符数据的数字排序,请使用 ORDER BY LEN(columnName), columnName。
For numeric ordering on character data use
ORDER BY LEN(columnName), columnName
.