SQL 使用 ORDER BY 和 UNION 不能正确对数字进行排序（例如 10 在 8 之前）
我尝试寻找答案，并阅读了该网站上的许多帖子，但仍然找不到我正在寻找的答案。
我正在尝试对一系列真正查找的数字和一个不是真正查找的数字进行排序，当我不需要添加假 * 时，我可以很好地排序，但不能在之后添加。
我已经尝试过
SELECT DISTINCT MasterTable.ClassName, MasterTable.ClassYear
FROM MasterTable
UNION ALL
SELECT DISTINCT "*" As [ClassName], "1" As [ClassYear]
FROM MasterTable
ORDER BY MasterTable.ClassYear;
，
SELECT DISTINCT MasterTable.ClassName, MasterTable.ClassYear
FROM (
SELECT DISTINCT MasterTable.ClassName, MasterTable.ClassYear FROM MasterTable
UNION
SELECT DISTINCT "*" As [ClassName], "1" As [ClassYear] FROM MasterTable
)
ORDER BY MasterTable.ClassYear;
但两者都将 ClassYear 返回为 1, 10, 12, 8...而不是 1,8,10, 12...。
任何帮助将不胜感激， 谢谢 ：）
I've tried looking for the answer, and read many threads on this site, but still can't find the answer I'm looking for.
I am trying to sort a series of numbers that are real look-ups and also one * which isn't, I can sort fine when I don't need to add the fake * but not after.
I have tried
SELECT DISTINCT MasterTable.ClassName, MasterTable.ClassYear
FROM MasterTable
UNION ALL
SELECT DISTINCT "*" As [ClassName], "1" As [ClassYear]
FROM MasterTable
ORDER BY MasterTable.ClassYear;
And
SELECT DISTINCT MasterTable.ClassName, MasterTable.ClassYear
FROM (
SELECT DISTINCT MasterTable.ClassName, MasterTable.ClassYear FROM MasterTable
UNION
SELECT DISTINCT "*" As [ClassName], "1" As [ClassYear] FROM MasterTable
)
ORDER BY MasterTable.ClassYear;
But both return the ClassYear as 1, 10, 12, 8... rather than 1, 8, 10, 12....
Any help would be much appreciated,
Thanks :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
MasterTable.ClassYear 是 varchar，因此它将作为字符串排序。
您必须在查询中对其进行转换或修复列类型。
对于第二个子句，您也只需要：
但是，您可以“作弊”并执行此操作。这里 1 将是 int 并将强制从第一个子句转换为 int 因为
MasterTable.ClassYear is varchar so it will sort as a string.
You'll have to convert it in the query or fix the column type.
For the 2nd clause, you also need only:
However, you can "cheat" and do this. Here 1 will be int and will force a conversion to int from the 1st clause because
它的属性将这些值排序为字符串。如果您希望它们按数字顺序排列，请尝试使用
Cast(MasterTable.ClassYear AS int)，无论是在 select 中还是在 order by 中，或者两者兼而有之，具体取决于您最终构建查询的方式。并且不要写
SELECT ..., "1" As [ClassYear]，而是写SELECT ..., 1 As [ClassYear]。It's property sorting those values as strings. If you want them in numerical order, try something like
Cast(MasterTable.ClassYear AS int), either in the select or in the order by, or both, depending on how you end up structuring your query.And instead of
SELECT ..., "1" As [ClassYear], writeSELECT ..., 1 As [ClassYear].您将年份作为字符串而不是数字返回。这意味着它是按文本排序的，而不是按数字排序。
要么以数字形式返回年份，要么在排序时将值转换为数字。
You are returning the year as a string, not a number. That means that it's sorted as text, not numerically.
Either return the year as a number, or convert the value into a number when sorting it.