时间戳

首页 > 解决方案 > 集成一个值表函数

sql-server - 集成一个值表函数

问题描述

感谢这个论坛上的帮助，我知道使用值表函数来获取声明函数以某种方式工作，以便以后可以使用它来创建视图。我只是不知道如何连接这些点。值表函数如下所示：

USE [m_DEV]
GO

--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO

--CREATE VIEW [dbo].[Entries] AS

    CREATE FUNCTION [Manual_COUNTRIES_2019]()
    RETURNS TABLE
    RETURN
        SELECT *
        FROM (       
       VALUES --risk_1
            (1,'AX'),(1,'AY'),(1,'AQ'),(1,'BQ')
            --risk_2 
            ,(2,'AA'),(2,'AI'),(2,'AX')
            -- @risk_3 
            ,(3,'QE'),(3,'QT')
            -- @risk_4 
            ,(4,'AA'),(4,'AB'),(4,'AS'),(4,'AZ')
            -- @risk_5 
            ,(5,'BB'),(5,'BC'),(5,'CD')
    
         ) AS X (RiskNum, Code);
       RETURN
     END;

    
    WITH Lead AS (
        SELECT 
            CASE 
                WHEN a.[10_2_1_Country] IN (SELECT Code from 1 ) THEN '0'   
                WHEN a.[10_2_1_Country] IN (SELECT Code from 2 ) THEN '0.5'   
                WHEN a.[10_2_1_Country] IN (SELECT Code from 3 ) THEN '1'   
                WHEN a.[10_2_1_Country] IN (SELECT Code from 4 ) THEN '2'   
                WHEN a.[10_2_1_Country] IN (SELECT Code from 5 ) THEN '3'
            END AS 'risk_country1'        
        FROM  [dbo].[Manual_Entries_19] a
    )

你如何(RiskNum, Code)与IN (SELECT Code from 1 ) 我尝试不同的变化。我是否需要调用函数表：[Manual_COUNTRIES_2019]？但是怎么做？

CREATE VIEW [dbo].[Entries] AS需要坐在 哪里CREATE FUNCTION [Manual_COUNTRIES_2019]()？- 我怀疑它会按照现在的方式工作。

标签： sql-servertsql

解决方案

您可以从 TVF 获取值，如下所示：

CASE 
    WHEN a.[10_2_1_Country] IN (SELECT Code FROM dbo.Manual_COUNTRIES_2019() WHERE RiskNum = 1) THEN '0'   
    WHEN a.[10_2_1_Country] IN (SELECT Code FROM dbo.Manual_COUNTRIES_2019() WHERE RiskNum = 2) THEN '0.5'   
    WHEN a.[10_2_1_Country] IN (SELECT Code FROM dbo.Manual_COUNTRIES_2019() WHERE RiskNum = 3) THEN '1'   
    WHEN a.[10_2_1_Country] IN (SELECT Code FROM dbo.Manual_COUNTRIES_2019() WHERE RiskNum = 4) THEN '2'   
    WHEN a.[10_2_1_Country] IN (SELECT Code FROM dbo.Manual_COUNTRIES_2019() WHERE RiskNum = 5) THEN '3'
END AS 'risk_country1'

推荐阅读