sql - 在 SQL Server 的 SELECT 中更改列数据类型

Question

我想将 categoriID 中的列值从数字更改为文本。

这可能吗？

SELECT name, CAST(categoriID AS char(10)) 
FROM customer
WHERE categoriID = 1 AS 'new_text'

这是我想要的图片链接:http://i.imgur.com/EFWNH3w.png

Answer 1

1) 最简单的解决方案是一个简单的连接:

SELECT  c.name, c.categoryID, category.name AS category_name
FROM    customer c
INNER JOIN -- or LEFT JOIN if categoryID allows NULLs
(
SELECT 1, 'First category' UNION ALL
SELECT 2, 'Second category' UNION ALL
SELECT 3, 'Third category'
) category(categoryID, name) ON c.categoryID = category.categoryID

如果类别列表很小，是静态的，并且只有这个查询需要它，我会使用这个解决方案。

2) 否则，我会这样创建一个新表

CREATE TABLE category -- or dbo.cateogory (note: you should use object's/table's schema)
(
    categoryID INT NOT NULL,
        CONSTRAINT PK_category_categoryID PRIMARY KEY(categoryID),
    name NVARCHAR(50) NOT NULL -- you should use the propper type (varchar maybe) and max length (100 maybe)
    --,      CONSTRAINT IUN_category_name UNIQUE(name) -- uncomment this line if you want to have unique categories (nu duplicate values in column [name])
);
GO

另外，我会创建一个外键，以确保 [customer] 表中的类别也存在于 [category] ​​表中:

ALTER TABLE customer 
ADD CONSTRAINT FK_customer_categoryID 
FOREIGN KEY (categoryID) REFERENCES category(categoryID)
GO

INSERT category (categoryID, name)
SELECT 1, 'First category' UNION ALL
SELECT 2, 'Second category' UNION ALL
SELECT 3, 'Third category'
GO

你的查询将是

SELECT  c.name, c.categoryID, ctg.name AS category_name
FROM    customer c
INNER JOIN ctg ON c.categoryID = ctg.categoryID -- or LEFT JOIN if c.categoryID allows NULLs 

我会使用解决方案#2。