SQL，CONVERT，CAST数据类型? [英] SQL, CONVERT, CAST Data Type?

问题描述

我不确定在此脚本中如何使用CAST或CONVERT.
SQL说:

I am not sure how to use either CAST or CONVERT in this script.
SQL says:

将varchar值"Amazing"转换为数据时，转换失败 输入int.

Conversion failed when converting the varchar value 'Amazing' to data type int.

有人可以告诉我该怎么做吗?

Can someone please tell me how to do this?

USE AdventureWorks2012  
GO

DECLARE @Number01 AS INT
DECLARE @Number02 AS INT
DECLARE @Number03 AS INT
DECLARE @Number04 AS INT
DECLARE @Number05 AS INT
DECLARE @Number06 AS INT
DECLARE @Number07 AS INT
DECLARE @Text01 AS VARCHAR (15)


SET @Number01 = 150
SET @Number02 = 200
SET @Number03 = 350
SET @Number04 = 450
SET @Number05 = 550
SET @Number06 = 650
SET @Number07 = 800
SET @Text01 = 'Amazing'


SELECT  A.ProductID  ,A.Name  ,A.ProductModelID  ,A.ProductNumber ,A.MakeFlag  ,A.Color  ,A.SafetyStockLevel
                ,A.StandardCost ,A.ListPrice,A.DaysToManufacture ,A.SellEndDate ,A.ModifiedDate ,A.ListPrice
                ,B.Name ,B.ListPrice ,B.Adjusted_List_Price ,C.ProductDescriptionID ,C.Description
                ,C.ModifiedDate ,D.ProductID ,D.StartDate ,D.EndDate ,D.ListPrice ,D.ModifiedDate, E.Name 
                ,E.CatalogDescription,E.ModifiedDate ,F.ReferenceOrderID ,F.TransactionDate
                ,F.TransactionID ,F.Quantity
                ,IIF(A.ListPrice >=@Number01,CAST((@Number01 *@Number02 + @Number03) AS nvarchar(50)), 100 + @Number01) AS [Test90]
                ,IIF(A.ListPrice >=@Number02,CAST((@Number01 *@Number02 + @Number03) AS nvarchar(50)), 'GG') AS [Test91]
                ,PATINDEX('%M94B%', A.ProductNumber) AS [Test92]
                ,PATINDEX('%M63S%', A.ProductNumber) AS [Test94]
                ,CASE 
                WHEN A.ProductNumber LIKE '%M94B%' THEN PATINDEX('%M94B%', A.ProductNumber) * 2
                WHEN A.ProductNumber LIKE '%M63S%' THEN  5 * @Number01
                WHEN A.ProductNumber LIKE '%T98U%' THEN @Text01
            END AS [Test95]

FROM  [Production].[Product] AS A
INNER JOIN [Production].[Product_2] AS B
ON A.Name = B.Name

INNER JOIN  [Production].[ProductDescription] AS C
ON A.ProductID = C.ProductDescriptionID

INNER JOIN  [Production].[ProductListPriceHistory] AS D
ON A.ProductID = D.ProductID

INNER JOIN   [Production].[ProductModel] AS E
ON A.ProductModelID = E.ProductModelID

FULL JOIN [Production].[TransactionHistory] AS F
ON A.ProductID = F.ProductID

WHERE A.ProductModelID IS NOT NULL
AND A.Color IS NOT NULL AND F.Quantity IS NOT NULL

推荐答案

尝试一下

USE AdventureWorks2012  
GO

DECLARE @Number01 AS INT
DECLARE @Number02 AS INT
DECLARE @Number03 AS INT
DECLARE @Number04 AS INT
DECLARE @Number05 AS INT
DECLARE @Number06 AS INT
DECLARE @Number07 AS INT
DECLARE @Text01 AS VARCHAR (15)


SET @Number01 = 150
SET @Number02 = 200
SET @Number03 = 350
SET @Number04 = 450
SET @Number05 = 550
SET @Number06 = 650
SET @Number07 = 800
SET @Text01 = 'Amazing'


SELECT  A.ProductID  ,A.Name  ,A.ProductModelID  ,A.ProductNumber ,A.MakeFlag  ,A.Color  ,A.SafetyStockLevel
                ,A.StandardCost ,A.ListPrice,A.DaysToManufacture ,A.SellEndDate ,A.ModifiedDate ,A.ListPrice
                ,B.Name ,B.ListPrice ,B.Adjusted_List_Price ,C.ProductDescriptionID ,C.Description
                ,C.ModifiedDate ,D.ProductID ,D.StartDate ,D.EndDate ,D.ListPrice ,D.ModifiedDate, E.Name 
                ,E.CatalogDescription,E.ModifiedDate ,F.ReferenceOrderID ,F.TransactionDate
                ,F.TransactionID ,F.Quantity
                ,IIF(A.ListPrice >=@Number01,CAST((@Number01 *@Number02 + @Number03) AS nvarchar(50)), 100 + @Number01) AS [Test90]
                ,IIF(A.ListPrice >=@Number02,CAST((@Number01 *@Number02 + @Number03) AS nvarchar(50)), 'GG') AS [Test91]
                ,PATINDEX('%M94B%', A.ProductNumber) AS [Test92]
                ,PATINDEX('%M63S%', A.ProductNumber) AS [Test94]
                ,CASE 
                WHEN A.ProductNumber LIKE '%M94B%' THEN cast(PATINDEX('%M94B%', A.ProductNumber) * 2  as varchar(100))
                WHEN A.ProductNumber LIKE '%M63S%' THEN cast( 5 * @Number01 as varchar(100))
                WHEN A.ProductNumber LIKE '%T98U%' THEN @Text01
            END AS [Test95]

FROM  [Production].[Product] AS A
INNER JOIN [Production].[Product_2] AS B
ON A.Name = B.Name

INNER JOIN  [Production].[ProductDescription] AS C
ON A.ProductID = C.ProductDescriptionID

INNER JOIN  [Production].[ProductListPriceHistory] AS D
ON A.ProductID = D.ProductID

INNER JOIN   [Production].[ProductModel] AS E
ON A.ProductModelID = E.ProductModelID

FULL JOIN [Production].[TransactionHistory] AS F
ON A.ProductID = F.ProductID

WHERE A.ProductModelID IS NOT NULL
AND A.Color IS NOT NULL AND F.Quantity IS NOT NULL

这篇关于SQL，CONVERT，CAST数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！