为什么这个 Sql 语句“order by”不显示？适当地

发布于 2024-11-02 16:16:12 字数 918 浏览 11 评论 0原文

我有 sql:

SELECT t.TagText, COUNT(*) AS NumVidsWithTag 
FROM tbl_Tag t
INNER JOIN tbl_Video_Tag_Xref x ON t.TagId=x.TagId
INNER JOIN tbl_Video v ON x.VideoId=v.VideoId
WHERE 6 IS NULL OR EXISTS (SELECT * FROM tbl_Video_Tag_Xref WHERE VideoId=6 AND TagId=t.TagId) 
GROUP BY t.TagText

然后我将 sql 更改为

SELECT t.TagText, COUNT(*) AS NumVidsWithTag 
FROM tbl_Tag t
INNER JOIN tbl_Video_Tag_Xref x ON t.TagId=x.TagId
INNER JOIN tbl_Video v ON x.VideoId=v.VideoId
WHERE 6 IS NULL OR EXISTS (SELECT * FROM tbl_Video_Tag_Xref WHERE VideoId=6 AND TagId=t.TagId) 
GROUP BY t.TagText 
ORDER BY t.TagText ASC

它仍然返回为：

我很困惑为什么这不能正确排序？

原文

I have the sql:

SELECT t.TagText, COUNT(*) AS NumVidsWithTag 
FROM tbl_Tag t
INNER JOIN tbl_Video_Tag_Xref x ON t.TagId=x.TagId
INNER JOIN tbl_Video v ON x.VideoId=v.VideoId
WHERE 6 IS NULL OR EXISTS (SELECT * FROM tbl_Video_Tag_Xref WHERE VideoId=6 AND TagId=t.TagId) 
GROUP BY t.TagText

Which returns:

Then I changed the sql to

SELECT t.TagText, COUNT(*) AS NumVidsWithTag 
FROM tbl_Tag t
INNER JOIN tbl_Video_Tag_Xref x ON t.TagId=x.TagId
INNER JOIN tbl_Video v ON x.VideoId=v.VideoId
WHERE 6 IS NULL OR EXISTS (SELECT * FROM tbl_Video_Tag_Xref WHERE VideoId=6 AND TagId=t.TagId) 
GROUP BY t.TagText 
ORDER BY t.TagText ASC

And it still comes back as:

Im confused as to why this wont sort properly?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤独患者 2024-11-09 16:16:12

它已正确排序。

TagText 是一个字符串，因此“11”“小于”“2”，例如

要按数字排序，请将 ORDER BY 更改为

ORDER BY CONVERT(INT, t.TagText) ASC

It is sorted properly.

TagText is a string, so "11" is 'smaller than' "2", for instance

To sort it numerically, change your ORDER BY to

ORDER BY CONVERT(INT, t.TagText) ASC

回复收藏 0 原文

小矜持 2024-11-09 16:16:12

看起来 TagText 是一个 varchar 字段，而不是一个数字。 SQL 将根据文本排序规则对 varchar 字段进行排序 - Aa 在 B 之前，因此 19 在 2 之前。

回复收藏 0 原文

前事休说 2024-11-09 16:16:12

TagText 是一个字符字段（nvarchar？），并且按字母数字顺序排序。

您需要将其转换为数字格式才能排序。

SELECT t.TagText, COUNT(*) AS NumVidsWithTag 
FROM tbl_Tag t
INNER JOIN tbl_Video_Tag_Xref x ON t.TagId=x.TagId
INNER JOIN tbl_Video v ON x.VideoId=v.VideoIdWHERE 6 IS NULL OR EXISTS (SELECT * FROM tbl_Video_Tag_Xref 
WHERE VideoId=6 AND TagId=t.TagId) 
GROUP BY t.TagText ORDER BY CONVERT(INT, t.TagText) ASC

TagText is a character field (nvarchar?) and is being sorted alphanumerically.

You will need to convert it to numeric format in order to sort.

SELECT t.TagText, COUNT(*) AS NumVidsWithTag 
FROM tbl_Tag t
INNER JOIN tbl_Video_Tag_Xref x ON t.TagId=x.TagId
INNER JOIN tbl_Video v ON x.VideoId=v.VideoIdWHERE 6 IS NULL OR EXISTS (SELECT * FROM tbl_Video_Tag_Xref 
WHERE VideoId=6 AND TagId=t.TagId) 
GROUP BY t.TagText ORDER BY CONVERT(INT, t.TagText) ASC

回复收藏 0 原文

徒留西风 2024-11-09 16:16:12

假设 tbl_Tag.TagText 列数据类型为 varchar 或类似的类型。

在这种情况下，这是正确排序的 - 按字母数字排序，19 在 2 之前； 100 将出现在 11 之前。

您需要将数据类型更改为数字类型。

您将能够在不更改表架构的情况下执行此操作：

SELECT t.TagText, COUNT(*) AS NumVidsWithTag 
FROM tbl_Tag tINNER JOIN tbl_Video_Tag_Xref x ON t.TagId=x.TagId
    INNER JOIN tbl_Video v ON x.VideoId=v.VideoId
WHERE 6 IS NULL OR EXISTS (SELECT * FROM tbl_Video_Tag_Xref WHERE VideoId=6 AND TagId=t.TagId) 
GROUP BY t.TagText 
ORDER BY CONVERT(int, t.TagText) ASC

本质上，只需更改 ORDER BY 子句即可将 TagText 列转换为 int 类型

Assumedly tbl_Tag.TagText columns datatype is varchar or something similar.

In which case, this is sorting correctly - sorting alphanumerically, 19 comes before 2; and 100 would come before 11.

You will need to change the datatype to a numeric type.

You will be able to do this without changing the schema of the table:

SELECT t.TagText, COUNT(*) AS NumVidsWithTag 
FROM tbl_Tag tINNER JOIN tbl_Video_Tag_Xref x ON t.TagId=x.TagId
    INNER JOIN tbl_Video v ON x.VideoId=v.VideoId
WHERE 6 IS NULL OR EXISTS (SELECT * FROM tbl_Video_Tag_Xref WHERE VideoId=6 AND TagId=t.TagId) 
GROUP BY t.TagText 
ORDER BY CONVERT(int, t.TagText) ASC

Essentially, just change the ORDER BY clause to convert the TagText column to type int

回复收藏 0 原文