时间戳

首页 > 解决方案 > 将数据类型 nvarchar 转换为数字转换/转换时出错

sql - 将数据类型 nvarchar 转换为数字转换/转换时出错

问题描述

我怀疑这与报告有关ClientName，这是一种nvarchar数据类型。AUMid是nchar。我不确定如何在不更改数据类型的情况下解决此问题（我不希望这样做）。

SELECT 
    Clients.ClientName, AUM.ManagementFee, 
    SUM(AUM.ManagementFee * AUM.AUM) AS Management_Fee
FROM
    AUM
JOIN 
    Clients ON AUM.AUMid = Clients.AUMid
GROUP BY 
   Clients.ClientName, AUM.ManagementFee

我需要显示Clients.ClientName. 它通过 链接到AUM表格AUM.AUMid。

CREATE TABLE Clients 
(
    ClientID nvarchar(50),
    ClientName nvarchar(50)
    AccountID nchar(10),
    AUMid nchar(10)
);

CREATE TABLE AUM
(
    AUMid nchar(10),
    AUM nvarchar(max),
    ManagementFee(decimal(9,4)
);

删除SUM(AUM.AUM * AUM.ManagementFee) AS Management_Fee允许执行查询。

标签： sqlsql-servertypescastingtype-conversion

解决方案

This is clearly causing your error:

SUM(AUM.ManagementFee * AUM.AUM) AS Management_Fee

because AUM.AUM is a string. That seems like a really bad choice of data types if you want to store a number. However, I suspect that you really just want a direct sum:

SELECT c.ClientName, 
       SUM(a.ManagementFee) AS Management_Fee
FROM Clients c JOIN
     AUM a
     ON a.AUMid = c.AUMid
GROUP BY c.ClientName;

On the other hand, the issue may be that AUM is a comma-separated list of numbers. If that is the case, my first and most sincere advice is to fix the data model. If you cannot do that for some reason, you can parse the string and keep your fingers crossed that it works:

SELECT c.ClientName, 
       SUM(a.ManagementFee * TRY_CONVERT(NUMERIC(38, 6), s.value)) AS Management_Fee
FROM Clients c JOIN
     AUM a
     ON a.AUMid = c.AUMid CROSS APPLY
     string_split(a.aum, ',') s
GROUP BY c.ClientName;

推荐阅读