时间戳

首页 > 解决方案 > 如何将列 - 在表变量中并且具有作用于其上的函数 - 连接到永久表中的列？

sql-server - 如何将列 - 在表变量中并且具有作用于其上的函数 - 连接到永久表中的列？

问题描述

这是我用来创建表变量的代码

DECLARE @attribute_table  
TABLE (attributeId VARCHAR(100) NOT NULL, EventId VARCHAR(100) NOT NULL, EVENT_TEACHERS VARCHAR(100) NOT NULL);

INSERT INTO @attribute_table 
SELECT attributeID, EventId, attributeValue as EVENT_TEACHERS
FROM dbo.retreatSuiteEventAttributes
WHERE attributeName LIKE '%EVENT_TEACHERS%'


select t1.attributeId, t1.EventId, i.EVENT_TEACHERS
from  @attribute_table t1
outer apply dbo.teacherstring3(t1.EVENT_TEACHERS, '|') i

（teacherstring3 是一个函数，它基本上将 EVENT_TEACHER 值拆分为不同的行并删除了分隔符）

我得到的答案只是修改了上面的 select 语句。我想要的是将该语句的结果加入（或集成）到下面完整显示的主选择语句中。

我想使用具有作用于它的函数的列-> i.EVENT_TEACHERS (FK) 列加入到另一个（永久）表，该表在下面显示的 SELECT 语句中具有相应的 TeacherIds (PK)。我该怎么做呢？

这是我在创建表变量后执行的完整查询（我将同时执行它们）。它不起作用，我在 GROUP 附近的语法也不正确。

 SELECT FORMAT(b.eventStartDate, 'd') as 'Start Date',
dbo.CategoryString(CAST(attributeValue as VARCHAR)) as Categories, 
b.EventName as 'Event Name', 
d.FirstName + ' ' + d.LastName as Teacher, 
b.spacesAvailable as Capacity, 
SUM(a.numberOfPlaces) as 'Participants', 
FORMAT(CAST(SUM(a.numberOfPlaces) as FLOAT)/CAST(b.spacesAvailable as FLOAT), 'p') as Occupancy,
FORMAT(SUM(a.totalCost), 'C') as Cost,
FORMAT(SUM(a.totalPaid), 'C') as Paid,
FORMAT(SUM(a.discountAmount), 'C') as Scholarships,
FORMAT(SUM(a.Donation), 'C') as Gifts,
FORMAT(SUM(a.totalPaid)/SUM(a.numberOfPlaces), 'C') as 'Revenue Per Seat'
  FROM [dbo].[RetreatSuiteRegistrations] a join [dbo].[RetreatSuiteEvents] b on a.EventId = b.EventId AND a.registrationStatusId=1 AND b.spacesAvailable > 0
  JOIN [dbo].[RetreatSuiteEventAttributes] c on a.EventId = c.EventId AND c.attributeName='EVENT_CATEGORIES'
  JOIN @attribute_table t1 outer apply dbo.teacherstring3(t1.EVENT_TEACHERS, '|') i
  JOIN [dbo].[RetreatSuiteTeacher] d on  i.EVENT_TEACHERS = d.TeacherID
  GROUP BY b.eventStartDate, dbo.CategoryString(CAST(attributeValue as VARCHAR)), b.EventName, d.FirstName + ' ' + d.LastName, b.spacesAvailable
  ORDER BY b.eventStartDate desc

    DROP Function IF EXISTS CategoryString
go

标签： sql-servertsql

解决方案

也许只是简单地添加一个愚蠢的子查询，如：

SELECT
  FORMAT(b.eventStartDate, 'd')                                                          AS [Start Date]
  , dbo.CategoryString(CAST(attributeValue AS VARCHAR))                                  AS Categories
  , b.EventName                                                                          AS [Event Name]
  , d.FirstName + ' ' + d.LastName                                                       AS Teacher
  , b.spacesAvailable                                                                    AS Capacity
  , SUM(a.numberOfPlaces)                                                                AS Participants
  , FORMAT(CAST(SUM(a.numberOfPlaces) AS FLOAT) / CAST(b.spacesAvailable AS FLOAT), 'p') AS Occupancy
  , FORMAT(SUM(a.totalCost), 'C')                                                        AS Cost
  , FORMAT(SUM(a.totalPaid), 'C')                                                        AS Paid
  , FORMAT(SUM(a.discountAmount), 'C')                                                   AS Scholarships
  , FORMAT(SUM(a.Donation), 'C')                                                         AS Gifts
  , FORMAT(SUM(a.totalPaid) / SUM(a.numberOfPlaces), 'C')                                AS [Revenue Per Seat]
FROM dbo.RetreatSuiteRegistrations                                   a
  JOIN dbo.RetreatSuiteEvents                                        b ON a.EventId = b.EventId
                                                                         AND a.registrationStatusId = 1
                                                                         AND b.spacesAvailable > 0
  JOIN dbo.RetreatSuiteEventAttributes                               c ON a.EventId = c.EventId
                                                                         AND c.attributeName = 'EVENT_CATEGORIES'
  JOIN (SELECT
          t1.attributeId
          , t1.EventId
          , i.EVENT_TEACHERS
        FROM @attribute_table                                    t1
          OUTER APPLY dbo.teacherstring3(t1.EVENT_TEACHERS, '|') i ) AS MyTest ON c.EventId = MyTest.EVENT_TEACHERS
  JOIN dbo.RetreatSuiteTeacher                                       d ON MyTest.EVENT_TEACHERS = d.TeacherID
GROUP BY
  b.eventStartDate
  , dbo.CategoryString(CAST(attributeValue AS VARCHAR))
  , b.EventName
  , d.FirstName + ' ' + d.LastName
  , b.spacesAvailable
ORDER BY b.eventStartDate DESC;

DROP FUNCTION IF EXISTS CategoryString;
GO

推荐阅读