两列的乘法 [英] Multiplication of two columns

问题描述

我已经阅读了excel表并将其插入到sql的表中。现在我想执行两列的乘法并将结果存储在第三列中。我面临的问题是，从excel表中读取这些列的列是nvarchar数据类型。现在我无法在int或float中转换这些数据类型。我运行查询选择（a）*（b）作为来自table1的Tot 。我得到错误操作数数据类型nvarchar对于乘法运算符无效。。我怎么能避免这个？我无法更改sql表中的日期类型。在excel表中我也将这些列格式化为数字。但是在读入sql server时，数据类型仍然是varchar。

I have read an excel sheet and inserted it into an table in sql. now i want to perform multiplication of two columns and store the result in 3rd column. the problem i m facing is since the table is read from an excel sheet those columns are in nvarchar data type. now i m not able to convert those data type in int or float. while i run the query select (a) * (b) as Tot from table1. i m getting the error as Operand data type nvarchar is invalid for multiply operator.. how can i avoid this?. i m not able to change the date type in sql table. In excel sheet also i've formatted those columns as number. but still while reading into sql server the data type is still varchar.

推荐答案

看看例子。



以下查询：

Have a look at examples.

Below query:

SELECT A*B AS C
FROM (
	SELECT '123' AS A, '2' AS B
	) AS T

引发错误：

raises error:

Msg 8117, Level 16, State 1, Line 2
Operand data type varchar is invalid for multiply operator.

为什么？因为varchar数据类型。在这种情况下，数字存储为文本!!!



此查询：

Why? Because of varchar data type. In this case numbers are stored as a text!!!

This query:

SELECT CONVERT(INT,A)*CONVERT(INT,B) AS C
FROM (
	SELECT '123' AS A, '2' AS B
	) AS T

返回： 246



结论：使用适当的数据类型 [ ^ ]！

尝试：

Try:

SELECT CONVERT(INT, a) * CONVERT(INT, b) AS Tot FROM Table1

[edit]忘记Table1的1 - OriginalGriff [/ edit]

[edit]Forgot the "1" of "Table1" - OriginalGriff[/edit]

你可以使用 SQL CAST 运算符，请参阅文档 [ ^ ]例如。

You may use the SQL CAST operator, see the documentation[^] for examples.

这篇关于两列的乘法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！