如何减去两列varchar（datatype） [英] How to subtract two columns varchar(datatype)

问题描述

此处有谁知道如何在同一个表中减去两列？我有专栏：总金额和现金投标。现在我想减去那2列，并将结果显示为另一列。怎么做？



表：

try7 - imgbb .com [ ^ ]



我尝试过：



SELECT MAX（trans_cash） - MIN（trans_total）FROM tbltransactions;

Anyone here who knows on how to subtract two columns in same table? I have columns: total amount and cash tendered. Now I want to subtract that 2 columns and display the result as a another column. How to do that?

Table:
try7 — imgbb.com[^]

What I have tried:

SELECT MAX(trans_cash) - MIN(trans_total) FROM tbltransactions;

推荐答案

简单：更改数据库。

如果您有数值，则将它们存储在数字列中。如果你不这样做，那么你就会为自己做好准备，然后在你咬紧牙关之前就把它留下来解决实际问题，这个问题会越来越难以解决。



不要将VARCHAR或NVARCHAR用于字符串数据以外的任何内容 - 在INT，FLOAT或DECIMAL列中存储数字，在DATE，DATETIME或DATETIME2列中存储日期。这样的问题就会消失......



您可以通过将NVARCHAR数据转换为整数来修复此特定情况，但查询将失败，如果有的话列包含非数字值...更好地修复数据问题！

Simple: change your DB.
If you have numeric values, then store them in numeric columns. If you don;t then you set yourself up for problems, and the later you leave it before you bite teh bullet and fix the actual problem the worth the problem will get and the harder it is to fix.

Don't use VARCHAR or NVARCHAR for anything other than string data - store numbers in INT, FLOAT, or DECIMAL columns, store dates in DATE, DATETIME, or DATETIME2 columns. And problems like this just melt away...

You could fix it for this specific case by CASTing the NVARCHAR data to an integer, but the query will fail if any column contains a non-numeric value ... much better to fix the data problem instead!

这篇关于如何减去两列varchar（datatype）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！