創建函式以根據輸入引數回傳行集

我需要創建一個函式，其中課程類別（名稱）將用作引數，它將回傳一組與其相關的培訓和參加者的數量。

我不知道從哪里開始。我一開始就做了這樣的事情：

CREATE OR ALTER FUNCTION dbo.f_course_cat (@course_cat_name VARCHAR)  
RETURNS table
AS RETURN 
( SELECT
    CASE WHEN @course_cat_name = 'Business' THEN 
    (SELECT course_name, cc.course_category_id
        FROM dbo.course_category CC
        join dbo.course c on c.course_category_id = CC.course_category_id
        WHERE c.course_category_id = 2)
    END
FROM dbo.course_category CC
join dbo.course c on c.course_category_id = CC.course_category_id);
GO

但是，我收到一個錯誤：

Msg 116, Level 16, State 1, Procedure f_course_cat, Line 10 [Batch Start Line 261]
當子查詢沒有用 EXISTS 引入時，只能在選擇串列中指定一個運算式。

我有 3 張桌子：

SELECT * FROM course_category

course_category_id  course_category_name
2                    Business
1                    Development
3                    Finance & Accounting
4                    IT & Software
6                    Marketing
5                    Personal development

SELECT * FROM course

course_id   course_name                               course_category_id    
1            Agile Kanban: Kanban for Software Development Teams    4   
2            Successful Negotiation: Master Your Negotiating Skill  5   
3            Customer Service: Soft Skills Fundamentals             5   
4            Git & GitHub - The Practical Guide                     1   
5            React - The Complete Guide                             1   
6            Complete C# Masterclass                                1   
7            Learn Parallel Programming with C# and .NET            1   
8            OAuth 2.0 in Spring Boot Applications                  1   
9            Selenium WebDriver with Docker, Jenkins & AWS          1   
10           Microsoft Power BI - A Complete Introduction           2   
11           Facebook Ads B2B: Advertising  Facebook Ads for B2B    6   
12           Business Fundamentals: Marketing Strategy              6   
13           Finance for Non Finance Executives                     3   
14           The Complete Introduction To Accounting and Finance    3   
15           Finance 101: Financial Management & DCF Fundamental    3   
16           Practical Finance                                      3   

SELECT * FROM training_history

employee_id course_id   start_date  end_date    training_status_id
1               13      2020-11-17  NULL         2
2               5       2021-06-02  2021-09-03   3
4               7       2020-12-08  2021-06-25   3
3               9       2021-09-08  NULL         1
1               13      2020-11-17  NULL         2
2               5       2021-06-02  2021-09-03   3
4               7       2020-12-08  2021-06-25   3
3               9       2021-09-08  NULL         1

uj5u.com熱心網友回復：

怎么樣：

CREATE OR ALTER FUNCTION dbo.f_course_cat (@course_cat_name VARCHAR(200)) 
RETURNS table AS 
return
        SELECT course_name, cc.course_category_id
        FROM dbo.course_category CC
        join dbo.course c on c.course_category_id = CC.course_category_id
        WHERE CC.Name = @course_cat_name 

標籤：sql sql服务器 sql-server-2019

上一篇：比較規范化資料的多條記錄

下一篇：獲取整個組沒有值的行