TSQL有条件地选择特定值

问题描述：

这是＃1644748的后续行动，我成功地回答了我自己的问题，但Quassnoi帮助我意识到这是错误的问题。他给了我一个适用于我的示例数据的解决方案，但是我无法将其插回到父存储过程中，因为我在SQL 2005语法上失败了。所以这里是试图描绘更广阔的画面，并问我实际需要什么。TSQL有条件地选择特定值

这是一个存储过程的一部分，它返回我继承的错误跟踪应用程序中的项目列表。有超过100个字段和26个连接，所以我只提取最相关的位。

SELECT 
tickets.ticketid, 
tickets.tickettype, 
tickets_tickettype_lu.tickettypedesc, 
tickets.stage, 
tickets.position, 
tickets.sponsor, 
tickets.dev, 
tickets.qa, 
DATEDIFF(DAY, ticket_history_assignment.savedate, GETDATE()) as 'daysinqueue' 
FROM 
dbo.tickets WITH (NOLOCK) 
LEFT OUTER JOIN dbo.tickets_tickettype_lu WITH (NOLOCK) ON tickets.tickettype = tickets_tickettype_lu.tickettypeid 
LEFT OUTER JOIN dbo.tickets_history_assignment WITH (NOLOCK) ON tickets_history_assignment.ticketid = tickets.ticketid 
AND tickets_history_assignment.historyid = (
SELECT 
    MAX(historyid) 
FROM 
    dbo.tickets_history_assignment WITH (NOLOCK) 
WHERE 
    tickets_history_assignment.ticketid = tickets.ticketid 
GROUP BY 
    tickets_history_assignment.ticketid 
) 
WHERE 
tickets.sponsor = @sponsor

感兴趣的领域是daysinqueue子查询混乱。该tickets_history_assignment表看起来大致如下

declare @tickets_history_assignment table (
historyid int, 
ticketid int, 
sponsor int, 
dev int, 
qa int, 
savedate datetime 
) 

insert into @tickets_history_assignment values (1521402, 92774,20,14, 20, '2009-10-27 09:17:59.527') 
insert into @tickets_history_assignment values (1521399, 92774,20,14, 42, '2009-08-31 12:07:52.917') 
insert into @tickets_history_assignment values (1521311, 92774,100,14, 42, '2008-12-08 16:15:49.887') 
insert into @tickets_history_assignment values (1521336, 92774,100,14, 42, '2009-01-16 14:27:43.577')

每当票保存，为赞助商，开发和QA的电流值存储在tickets_history_assignment表与ticketid和时间戳。所以有人可以改变qa的价值，但不要让赞助商一个人。

基于所有这些条件，我想知道的是tickets_history_assignment表中记录的历史记录，其中赞助商值最后更改为可以计算daysinqueue的值。如果记录插入到历史记录表中，并且只有qa值发生了变化，我不需要该记录。所以简单地依靠MAX(historyid)不适合我。

Quassnoi想出了以下内容，这似乎与我的示例数据一起工作，但我无法将其插入到较大的查询中，SQL管理器关于WITH语句的母狗。

;WITH rows AS 
     (
     SELECT *, ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY savedate DESC) AS rn 
     FROM @Table 
     ) 
SELECT rl.sponsor, ro.savedate 
FROM rows rl 
CROSS APPLY 
     (
     SELECT TOP 1 rc.savedate 
     FROM rows rc 
     JOIN rows rn 
     ON  rn.ticketid = rc.ticketid 
       AND rn.rn = rc.rn + 1 
       AND rn.sponsor <> rc.sponsor 
     WHERE rc.ticketid = rl.ticketid 
     ORDER BY 
       rc.rn 
     ) ro 
WHERE rl.rn = 1

我的比赛，球队昨天下午和毫无进展，因为我没有从根本上明白是怎么回事以及应如何在更大的范围内。

那么，任何接受者？

UPDATE

好的，这是整个事情。我一直在切换一些表名和列名，试图简化一些事情，所以这里是完整的未经编辑的混乱。

剪断 - 老坏代码

下面是错误：

Msg 102, Level 15, State 1, Procedure usp_GetProjectRecordsByAssignment, Line 159 
Incorrect syntax near ';'. 
Msg 102, Level 15, State 1, Procedure usp_GetProjectRecordsByAssignment, Line 179 
Incorrect syntax near ')'.

行号当然不是正确的，但指

;WITH rows AS

和 '）' 字符之后的WHERE rl.rn = 1

分别为

是否有额外超长问题的标签？

更新＃2

这里是任何人完成的查询谁需要这样的：

CREATE PROCEDURE [dbo].[usp_GetProjectRecordsByAssignment] 
(
    @assigned numeric(18,0), 
    @assignedtype numeric(18,0) 
) 
AS 

SET NOCOUNT ON 

WITH rows AS 
     (
     SELECT *, ROW_NUMBER() OVER (PARTITION BY recordid ORDER BY savedate DESC) AS rn 
     FROM projects_history_assignment 
     ) 
SELECT projects_records.recordid, 
     projects_records.recordtype, 
     projects_recordtype_lu.recordtypedesc, 
     projects_records.stage, 
     projects_stage_lu.stagedesc, 
     projects_records.position, 
     projects_position_lu.positiondesc, 
     CASE projects_records.clientrequested 
      WHEN '1' THEN 'Yes' 
      WHEN '0' THEN 'No' 
     END AS clientrequested, 
     projects_records.reportingmethod, 
     projects_reportingmethod_lu.reportingmethoddesc, 
     projects_records.clientaccess, 
     projects_clientaccess_lu.clientaccessdesc, 
     projects_records.clientnumber, 
     projects_records.project, 
     projects_lu.projectdesc, 
     projects_records.version, 
     projects_version_lu.versiondesc, 
     projects_records.projectedversion, 
     projects_version_lu_projected.versiondesc AS projectedversiondesc, 
     projects_records.sitetype, 
     projects_sitetype_lu.sitetypedesc, 
     projects_records.title, 
     projects_records.module, 
     projects_module_lu.moduledesc, 
     projects_records.component, 
     projects_component_lu.componentdesc, 
     projects_records.loginusername, 
     projects_records.loginpassword, 
     projects_records.assistedusername, 
     projects_records.browsername, 
     projects_browsername_lu.browsernamedesc, 
     projects_records.browserversion, 
     projects_records.osname, 
     projects_osname_lu.osnamedesc, 
     projects_records.osversion, 
     projects_records.errortype, 
     projects_errortype_lu.errortypedesc, 
     projects_records.gsipriority, 
     projects_gsipriority_lu.gsiprioritydesc, 
     projects_records.clientpriority, 
     projects_clientpriority_lu.clientprioritydesc, 
     projects_records.scheduledstartdate, 
     projects_records.scheduledcompletiondate, 
     projects_records.projectedhours, 
     projects_records.actualstartdate, 
     projects_records.actualcompletiondate, 
     projects_records.actualhours, 
     CASE projects_records.billclient 
      WHEN '1' THEN 'Yes' 
      WHEN '0' THEN 'No' 
     END AS billclient, 
     projects_records.billamount, 
     projects_records.status, 
     projects_status_lu.statusdesc, 
     CASE CAST(projects_records.assigned AS VARCHAR(5)) 
      WHEN '0' THEN 'N/A' 
      WHEN '10000' THEN 'Unassigned' 
      WHEN '20000' THEN 'Client' 
      WHEN '30000' THEN 'Tech Support' 
      WHEN '40000' THEN 'LMI Tech Support' 
      WHEN '50000' THEN 'Upload' 
      WHEN '60000' THEN 'Spider' 
      WHEN '70000' THEN 'DB Admin' 
      ELSE rtrim(users_assigned.nickname) + ' ' + rtrim(users_assigned.lastname) 
     END AS assigned, 
     CASE CAST(projects_records.assigneddev AS VARCHAR(5)) 
      WHEN '0' THEN 'N/A' 
      WHEN '10000' THEN 'Unassigned' 
      ELSE rtrim(users_assigneddev.nickname) + ' ' + rtrim(users_assigneddev.lastname) 
     END AS assigneddev, 
     CASE CAST(projects_records.assignedqa AS VARCHAR(5)) 
      WHEN '0' THEN 'N/A' 
      WHEN '10000' THEN 'Unassigned' 
      ELSE rtrim(users_assignedqa.nickname) + ' ' + rtrim(users_assignedqa.lastname) 
     END AS assignedqa, 
     CASE CAST(projects_records.assignedsponsor AS VARCHAR(5)) 
      WHEN '0' THEN 'N/A' 
      WHEN '10000' THEN 'Unassigned' 
      ELSE rtrim(users_assignedsponsor.nickname) + ' ' + rtrim(users_assignedsponsor.lastname) 
     END AS assignedsponsor, 
     projects_records.clientcreated, 
     CASE projects_records.clientcreated 
      WHEN '1' THEN 'Yes' 
      WHEN '0' THEN 'No' 
     END AS clientcreateddesc, 
     CASE projects_records.clientcreated 
      WHEN '1' THEN rtrim(clientusers_createuser.firstname) + ' ' + rtrim(clientusers_createuser.lastname) + ' (Client)' 
      ELSE rtrim(users_createuser.nickname) + ' ' + rtrim(users_createuser.lastname) 
     END AS createuser, 
     projects_records.createdate, 
     projects_records.savedate, 
     projects_resolution.sitesaffected, 
     projects_sitesaffected_lu.sitesaffecteddesc, 
     DATEDIFF(DAY, projects_history_assignment.savedate, GETDATE()) as 'daysinqueue', 
     projects_records.iOnHitList, 
     projects_records.changetype 
FROM 
    dbo.projects_records WITH (NOLOCK) 
    LEFT OUTER JOIN dbo.projects_recordtype_lu WITH (NOLOCK) ON projects_records.recordtype = projects_recordtype_lu.recordtypeid 
    LEFT OUTER JOIN dbo.projects_stage_lu WITH (NOLOCK) ON projects_records.stage = projects_stage_lu.stageid 
    LEFT OUTER JOIN dbo.projects_position_lu WITH (NOLOCK) ON projects_records.position = projects_position_lu.positionid 
    LEFT OUTER JOIN dbo.projects_reportingmethod_lu WITH (NOLOCK) ON projects_records.reportingmethod = projects_reportingmethod_lu.reportingmethodid 
    LEFT OUTER JOIN dbo.projects_lu WITH (NOLOCK) ON projects_records.project = projects_lu.projectid 
    LEFT OUTER JOIN dbo.projects_version_lu WITH (NOLOCK) ON projects_records.version = projects_version_lu.versionid 
    LEFT OUTER JOIN dbo.projects_version_lu projects_version_lu_projected WITH (NOLOCK) ON projects_records.projectedversion = projects_version_lu_projected.versionid 
    LEFT OUTER JOIN dbo.projects_sitetype_lu WITH (NOLOCK) ON projects_records.sitetype = projects_sitetype_lu.sitetypeid 
    LEFT OUTER JOIN dbo.projects_module_lu WITH (NOLOCK) ON projects_records.module = projects_module_lu.moduleid 
    LEFT OUTER JOIN dbo.projects_component_lu WITH (NOLOCK) ON projects_records.component = projects_component_lu.componentid 
    LEFT OUTER JOIN dbo.projects_browsername_lu WITH (NOLOCK) ON projects_records.browsername = projects_browsername_lu.browsernameid 
    LEFT OUTER JOIN dbo.projects_osname_lu WITH (NOLOCK) ON projects_records.osname = projects_osname_lu.osnameid 
    LEFT OUTER JOIN dbo.projects_errortype_lu WITH (NOLOCK) ON projects_records.errortype = projects_errortype_lu.errortypeid 
    LEFT OUTER JOIN dbo.projects_resolution WITH (NOLOCK) ON projects_records.recordid = projects_resolution.recordid 
    LEFT OUTER JOIN dbo.projects_sitesaffected_lu WITH (NOLOCK) ON projects_resolution.sitesaffected = projects_sitesaffected_lu.sitesaffectedid 
    LEFT OUTER JOIN dbo.projects_gsipriority_lu WITH (NOLOCK) ON projects_records.gsipriority = projects_gsipriority_lu.gsipriorityid 
    LEFT OUTER JOIN dbo.projects_clientpriority_lu WITH (NOLOCK) ON projects_records.clientpriority = projects_clientpriority_lu.clientpriorityid 
    LEFT OUTER JOIN dbo.projects_status_lu WITH (NOLOCK) ON projects_records.status = projects_status_lu.statusid 
    LEFT OUTER JOIN dbo.projects_clientaccess_lu WITH (NOLOCK) ON projects_records.clientaccess = projects_clientaccess_lu.clientaccessid 
    LEFT OUTER JOIN dbo.users users_assigned WITH (NOLOCK) ON projects_records.assigned = users_assigned.userid 
    LEFT OUTER JOIN dbo.users users_assigneddev WITH (NOLOCK) ON projects_records.assigneddev = users_assigneddev.userid 
    LEFT OUTER JOIN dbo.users users_assignedqa WITH (NOLOCK) ON projects_records.assignedqa = users_assignedqa.userid 
    LEFT OUTER JOIN dbo.users users_assignedsponsor WITH (NOLOCK) ON projects_records.assignedsponsor = users_assignedsponsor.userid 
    LEFT OUTER JOIN dbo.users users_createuser WITH (NOLOCK) ON projects_records.createuser = users_createuser.userid 
    LEFT OUTER JOIN dbo.clientusers clientusers_createuser WITH (NOLOCK) ON projects_records.createuser = clientusers_createuser.userid 
    LEFT OUTER JOIN dbo.projects_history_assignment WITH (NOLOCK) ON projects_history_assignment.recordid = projects_records.recordid 
    AND projects_history_assignment.historyid = (
     SELECT ro.historyid 
     FROM rows rl 
     CROSS APPLY 
       (
       SELECT TOP 1 rc.historyid 
       FROM rows rc 
       JOIN rows rn 
       ON  rn.recordid = rc.recordid 
         AND rn.rn = rc.rn + 1 
         AND rn.assigned <> rc.assigned 
       WHERE rc.recordid = rl.recordid 
       ORDER BY 
         rc.rn 
       ) ro 
     WHERE rl.rn = 1 
       AND rl.recordid = projects_records.recordid 
    ) 

WHERE 
    (@assignedtype='0' and projects_records.assigned = @assigned) 
OR (@assignedtype='1' and projects_records.assigneddev = @assigned) 
OR (@assignedtype='2' and projects_records.assignedqa = @assigned) 
OR (@assignedtype='3' and projects_records.assignedsponsor = @assigned) 
OR (@assignedtype='4' and projects_records.createuser = @assigned)

“关于WITH语句的婊子”不是非常具有描述性。什么是确切的错误信息？ – Quassnoi 2009-10-30 15:40:54

哦，对不起..它是“关键字'WITH'附近的语法不正确。”这就是为什么我没有提出具体的信息，因为它是模糊的。我用你的名字取代了AND tickets_history_assignment.historyid =（）中的查询。那么先说'''附近的语法错误。然后我删除了';'然后它不喜欢'WITH'。 – Dzejms 2009-10-30 15:48:50

我只是复制粘贴的声明，它完美的作品。您可以在这里复制并粘贴** Management Studio窗口的准确**内容吗？我的意思是确切的，这是处理语法错误的唯一方法。 – Quassnoi 2009-10-30 16:03:17

答

WITH rows AS 
     (
     SELECT *, ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY savedate DESC) AS rn 
     FROM @Table 
     ) 
SELECT projects_records.recordid, 
     projects_records.recordtype, 
     /* skipped */ 
     AND projects_history_assignment.historyid = (
     SELECT ro.historyid 
     FROM rows rl 
     CROSS APPLY 
       (
       SELECT TOP 1 rc.savedate 
       FROM rows rc 
       JOIN rows rn 
       ON  rn.recordid = rc.recordid 
         AND rn.rn = rc.rn + 1 
         AND rn.assigned <> rc.assigned 
       WHERE rc.recordid = rl.recordid 
       ORDER BY 
         rc.rn 
       ) ro 
     WHERE rl.rn = 1 
       AND rl.recordid = projects_records.recordid 
     )

好吧，让它工作，但它现在需要25秒运行查询，而不是.2之前。我想我会试着想想另一种存储这些数据的方式。也许我会添加另一个位列，以确定分配的用户是否正在更改或什么。感谢您的全力帮助，并为您的麻烦感到抱歉。 – Dzejms 2009-10-30 17:24:10

'@ Dzejms'：这里的子查询是关联的，并且对每一行都进行了重新评估，这就是为什么它需要这么长时间。您需要用JOIN替换它。 – Quassnoi 2009-10-30 17:27:09

我不知道用一个连接替换哪个子查询，或者我将如何完成此操作。你是指'CROSS APPLY'部分？ – Dzejms 2009-10-30 17:38:00

答

好吧，如果这查询不工作了 - 你总是可以考虑在tickets_history_assignment触发表。您可以使用INSTEAD OF INSERT, UPDATE，在进行任何更改之前触发，以便您可以完全控制将要更改的内容以及要执行的操作。

TSQL有条件地选择特定值

相关推荐