使用带有条件的联接操作

Question

在我的数据库中，我有两个正在使用的表。我正在使用Server 2014。

• 一个是“版本”，包含version_id、version_number、start_date、end_date和server_id。
• 另一个是“特性”，它包含feature_id、feature_info和version_id。version_id是“version_id”中的一个外键，它是一个PK。

我试着弄到这样的东西：

"X，Y，Z在1.0版。

“.载于1.2版。

然后，如果一个版本没有新特性：

“版本2.2没有新特性。”

。

现在，我没有任何没有功能的版本，但我计划添加一些来测试这个版本。

下面是我当前使用代码获得的输出(我不关心单数/复数问题，即‘'Version 1.7有电子邮件客户端’)：

1.0版具有网页功能，1.1版有用户界面，1.7版有电子邮件客户端，1.7版有语音控制功能，2.0版有bug修复。

逗号之间正好有7个空格。这一点不太重要，但能够修复还是很好的。

*此外，如果我想将其转换为存储过程，是否只需在变量声明后加上“创建过程x”，然后加上“开始”和“as”，然后在代码末尾加上“end”？

DECLARE @result varchar(1000)

SET @result = ''

BEGIN TRY

    SELECT @result = @result + 'Version ' + version_number + 'has ' +      feature_info + ', ' 
    FROM features join versions on versions.version_id = features.version_id
    SELECT substring(@result, 0, len(@result) - 1)

END TRY

BEGIN CATCH
    PRINT 'THere was an error getting feature info'
END CATCH

Answer 1

您可以使用FOR XML PATH()构造来创建特性列表。

例如：

USE tempdb;

IF OBJECT_ID('dbo.Features') IS NOT NULL
DROP TABLE dbo.Features;
IF OBJECT_ID('dbo.Versions') IS NOT NULL
DROP TABLE dbo.Versions;
GO
CREATE TABLE dbo.Versions
(
    VersionID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , VersionNumber DECIMAL(10,2) NOT NULL
    , StartDate DATETIME NOT NULL
    , EndDate DATETIME NULL
    , ServerID INT NOT NULL
);
CREATE TABLE dbo.Features
(
    FeatureID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FeatureInfo VARCHAR(250) NOT NULL 
    , VersionID INT NOT NULL
        FOREIGN KEY REFERENCES dbo.Versions(VersionID)
);

INSERT INTO dbo.Versions(VersionNumber, StartDate, EndDate, ServerID)
VALUES (1.0,'2016-03-22T00:00:00', '2016-03-23T00:00:00', 1001)
    , (2.0,'2016-03-23T00:00:00', '2016-03-24T00:00:00', 1001)
    , (2.1,'2016-03-24T00:00:00', NULL, 1001);

INSERT INTO dbo.Features (FeatureInfo, VersionID)
VALUES ('blah', 1)
    , ('blee', 1)
    , ('bloh', 2);

;WITH Deets
AS
(
    SELECT VersionNumber = CONVERT(VARCHAR(10), v.VersionNumber, 0)
        , Features = STUFF((SELECT TOP(2147483647) ', ' + f.FeatureInfo
        FROM dbo.Features f
        WHERE f.VersionID = v.VersionID
        ORDER BY f.FeatureID
        FOR XML PATH ('')
        ), 1,2,'')
    FROM dbo.Versions v
)
SELECT 'Version ' + d.VersionNumber + ' has ' + 
    CASE WHEN d.Features IS NULL THEN 'no features' 
        ELSE d.Features 
    END
FROM Deets d;

这将返回一个很好的列表，比如：

请注意上面代码中的几点：

• 我总是指定模式
• 我对SQL关键字使用大写。
• 我用骆驼大小写作为列名。
• 我为日期列指定的值采用ISO格式，这使SQL Server更容易理解，并防止错误。