T-SQL SELECT 查询返回多个表的组合结果

问题描述

我想知道是否有可能并且更有效地做一些我目前在代码中做的事情，而不是在 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模板网！