sql server 2005中的Max（）函数 [英] Max() Funcation in sql server 2005

问题描述

大家好，

感谢您阅读我的帖子，



让我先向您介绍一下。我有一个表Activity_id

，在表中我有一个列名activity_idnvarchar（6）

它只包含从1到201的序列号。



现在问题。

从activity_id中选择max（activity_id）

此查询返回值99 这是不正确的。

从activity_id中选择max（cast（activity_id as int））

此查询返回值201。这是正确的。



我可以认为它是因为nvarchar并且转换为int。但是你会详细说明如何比较nvarchar值，使99大于201.



谢谢，

Ahsan Ashfaq

Hello all,
Thanks for reading my thread,

let me brief you first. I have a table "Activity_id"
and in the table i have a column name "activity_id" nvarchar(6)
it just contains Serial No.s from 1 to 201.

Now the issue.
select max(activity_id) from activity_id
This query returns value "99" which is incorrect.
select max(cast(activity_id as int)) from activity_id
This query returns the value "201". which is correct.

I can reckon that its because of nvarchar and casting to int. but will u plz elaborate how nvarchar values are compared so that 99 is greater than 201.

Thanks,
Ahsan Ashfaq

推荐答案

首先，

如果activity_id包含从1到201的序列号，那为什么它是'nvarchar（6）'类型的列？糟糕的数据库设计！



你对查询结果的疑问必须是这样的：

activity_id comparsion的第一个字符后跟第二个，3 ...

因此，在99和201：

99-> 1st = 9

201-> 1st = 2



9> 2 =>因此，这导致99而不是201.



而不是在查询中强制转换，最好将列类型更改为所需的值类型。

Firstly,
If "activity_id" contains serial nos from 1 to 201 then why is it a 'nvarchar(6)' type of column? Bad DB design!

Well the doubt you have regarding the query result must be something like:
first character of activity_id comparsion followed by 2nd, 3rd...
So, in 99 & 201:
99-> 1st = 9
201-> 1st = 2

9 > 2 => thus this results in 99 instead of 201.

Instead of casting in the query, best would be to change the column type to desired value types.

这篇关于sql server 2005中的Max（）函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！