問題描述

我想知道是否有可能并且更有效地做一些我目前在代碼中做的事情，而不是在 T-SQL 中做.

I am wondering if it is possible and more efficient to do something that I am presently doing in code, to do in T-SQL instead.

我有一個包含課程的數據庫.每門課程都可以有不同的課程，這些課程在不同的地點和不同的獎項中是不同的.

I have a database with courses. Each course can have different offerings which are variations of the course at different locations and at different awards.

這是我的(簡化的)數據庫結構和一些示例數據:

Here's my (simplified) database structure and some sample data:

CREATE TABLE tblCourse (CourseId int, CourseName varchar(50))
CREATE TABLE tblOffering (OfferingId int, CourseId int, LocationId int, AwardId int)
CREATE TABLE tblLocation (LocationId int, LocationName varchar(50))
CREATE TABLE tblAward (AwardId int, AwardName varchar(50))

INSERT INTO tblCourse VALUES (1, 'Course A')
INSERT INTO tblCourse VALUES (2, 'Course B')

INSERT INTO tblOffering VALUES (1, 1, 1, 1)
INSERT INTO tblOffering VALUES (2, 1, 2, 1)
INSERT INTO tblOffering VALUES (3, 1, 3, 1)
INSERT INTO tblOffering VALUES (4, 1, 1, 2)
INSERT INTO tblOffering VALUES (5, 2, 3, 1)

INSERT INTO tblLocation VALUES (1, 'Location A')
INSERT INTO tblLocation VALUES (2, 'Location B')
INSERT INTO tblLocation VALUES (3, 'Location C')

INSERT INTO tblAward VALUES (1, 'Award A')
INSERT INTO tblAward VALUES (2, 'Award B')

我想從 SQL 中檢索的是每個課程/獎項組合的一行.每一行都有每個位置的列，以及該 CourseId/AwardId 組合的課程是否可用.現在會有沒有既定課程/獎勵組合的行.

What I want to retrieve from SQL is a single row for each course/award combination. Each row would have columns for each location and whether a course of that CourseId/AwardId combination was available. There would be now rows for course/award combinations that have no offerings.

來自樣本數據的所需結果將是這樣的記錄集:

The required result, from the sample data, would be a recordset like this:

CourseId | CourseName | AwardId | AwardName | LocationA | LocationB | LocationC
---------+------------+---------+-----------+-----------+-----------+----------
1        | Course A   | 1       | Award A   | True      | True      | True
1        | Course A   | 2       | Award B   | True      | NULL      | NULL
2        | Course B   | 1       | Award A   | NULL      | NULL      | True

(NULL 也可能是 False)

(NULL could also be False)

目前我正在做一個帶有各種 JOINS 的簡單 SELECT 語句，它為每個課程/獎項組合提供多行，然后我遍歷代碼中的所有行并構建所需的結果.但是，我認為這不是那么有效，因為我還需要對結果進行分頁.

At present I am doing a simple SELECT statement with various JOINS which gives me multiple rows for each course/award combination, then I loop through all rows in my code and build the required result. However, I don't think this is so efficient as I also need to page results.

我認為我可以通過創建一個臨時表和一堆單獨的查詢在存儲過程中很容易地做到這一點，但我認為這不會太有效.想知道在 T-SQL 中是否有更好的方法?

I think I could do this fairly easily in a stored procedure by creating a temporary table and a bunch of separate queries, but I don't think that would be too efficient. Wondering if there is a better way of doing it in T-SQL???

所以澄清一下，我正在尋找的是一個 T-SQL 查詢或存儲過程，它將生成上述示例記錄集，并且我可以適應分頁.

So to clarify, what I am looking for is a T-SQL query or stored procedure that will produce the above sample recordset, and which I could adapt paging to.

注意.我使用的是 SQL Server 2008

NB. I am using SQL Server 2008

推薦答案

對于動態列:

DECLARE  @COLUMNS VARCHAR(max)
        ,@query varchar(1024)
        ,@True varchar(6)


SELECT @COLUMNS = 
COALESCE(
@Columns + ',[' + L.LocationName + ']',
'[' + L.LocationName +']'
)
FROM tblLocation L

SELECT @True = '''True'''

SELECT @QUERY = 'SELECT C.CourseName
                 ,A.AwardName
                 , pvt.*
FROM (SELECT O.OfferingID AS OID
            ,O.AwardID AS AID
            ,O.CourseID AS CID
            ,L.LocationName AS LID
       FROM tblOffering O Inner Join tblLocation L on L.LocationID = O.LocationID) AS S
PIVOT
(
    count(oID) For LID IN (' +@COLUMNS+ ')
) As pvt
inner join tblCourse C on C.CourseID = CID
inner join tblAward A on A.AwardID = pvt.AID'

EXEC (@QUERY)
GO

這篇關于T-SQL SELECT 查詢返回多個表的組合結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！