Coldfusion 动态变量 (?) 解析为 SQL 查询

Question

这是我第一次尝试将变量解析为 CF 查询，但我遇到了一些小问题。

总而言之，我正在创建一个按运算符(operator)按周划分的销售额数据透视表。手动，没有麻烦，但我只想要几周的一部分，而不是全部。同样，如果我想要硬编码周数，问题就来了，但当我尝试将周数解析为 SQL 查询以创建动态周数的子集时，问题就来了。

CFDUMP 告诉我查询是根据我发送给它的内容执行的，但是当涉及到输出字段值(星期)时，它采用变量名称值，而不是字段值是否有意义？

我知道我不应该将字段名称作为值，但我现在仍在尝试进行测试。通过手动查询，我在周数前加上“W”，例如W9，但是当我尝试这样做时，我得到了

手动查询

SELECT UserName,
       ISNULL([W6], 0) AS [W6],
       ISNULL([W7], 0) AS [W7],
       ISNULL([W8], 0) AS [W8],
       ISNULL([W9], 0) AS [W9],
       ISNULL([W10], 0) AS [W10]
  FROM (   SELECT CASE
                       WHEN SUBSTRING(Username, 1, 3) = 'd.S' THEN 'DS'
                       WHEN SUBSTRING(Username, 1, 3) = 'p.R' THEN 'PR'
                       WHEN SUBSTRING(Username, 1, 3) = 'j.G' THEN 'JG'
                       WHEN SUBSTRING(Username, 1, 3) = 'b.c' THEN 'BC'
                       ELSE 'Other' END AS Username,
                  CONCAT('W', DATEPART(isowk, ERCFullAuditDate)) as XWeek,
                  COUNT(1) [SalesCount]
             FROM [ERC-Transactions].[dbo].[ERC-Audit]
            WHERE (   ERCModule = 'Carriage Return on Account'
                AND   ERCFullAuditDate >= DATEADD(week, -4, GETDATE())
                 OR   (   ERCFullAuditDate <= DATEADD(week, -52, convert(datetime, GETDATE()))
                    and   ERCFullAuditDate >= DATEADD(week, -56, convert(datetime, GETDATE()))))
            GROUP BY DATEPART(isowk, ERCFullAuditDate),
                     UserName) ST
  PIVOT (   SUM(SalesCount)
            for XWeek in ([W6], [W7], [W8], [W9], [W10])) as StorePivot

以上产生了这个结果。

COLDFUSION 动态查询

现在，当我尝试做同样的事情时，但通过将变量解析到查询中，CFDUMP 产生了正确的值，但正如我所说，当我尝试输出它时，我得到的是字段名称而不是值。

老实说，我有两个问题需要解决。变量字段名称，而且当我将“W”的串联添加到周数时，我看到“将数据类型 nvarchar 转换为 int 时出错”。我想我可能需要一个cfqueryparam，但我不确定。

<cfset WEEK_2 = DATETImeFormat(DateAdd("ww",-2,now()),"w")>
           
<cfoutput>Week: #WEEK_2#</cfoutput> (This is the value of the WEEK_2 variable)<br>       

<cfset XX = "">
<cfset XX = XX & "SELECT UserName, ">
<cfset XX = XX & "ISNULL([#WEEK_2#], 0) AS [#WEEK_2#] ">
<cfset XX = XX & "FROM (   SELECT CASE ">
<cfset XX = XX & "WHEN SUBSTRING(Username,1,3) = 'd.S' THEN 'DS' ">
<cfset XX = XX & "WHEN SUBSTRING(Username,1,3) = 'p.R' THEN 'PR' ">
<cfset XX = XX & "WHEN SUBSTRING(Username,1,3) = 'j.G' THEN 'JG' ">
<cfset XX = XX & "WHEN SUBSTRING(Username,1,3) = 'b.c' THEN 'BC' ">
<cfset XX = XX & "ELSE 'Other' END AS Username, ">
<cfset XX = XX & "DATEPART(isowk, ERCFullAuditDate) as XWeek, ">
<cfset XX = XX & "COUNT(1) [SalesCount] ">
<cfset XX = XX & "FROM [EBS-ERC-Transactions].[dbo].[ERC-Audit] ">
<cfset XX = XX & "WHERE (   ERCModule = 'Carriage Return on Account' ">
<cfset XX = XX & "AND   ERCFullAuditDate >= DATEADD(week, -4, GETDATE()) ">
<cfset XX = XX & "OR   (   ERCFullAuditDate <= DATEADD(week, -52, convert(datetime, GETDATE())) ">
<cfset XX = XX & "and   ERCFullAuditDate >= DATEADD(week, -56, convert(datetime, GETDATE())))) ">
<cfset XX = XX & "GROUP BY DATEPART(isowk, ERCFullAuditDate), ">
<cfset XX = XX & "UserName) ST ">
<cfset XX = XX & "PIVOT (   SUM(SalesCount) ">
<cfset XX = XX & "for XWeek in ([#WEEK_2#])) as StorePivot ">

<cfquery name = "QueryTest" dataSource = "EBSERC"> 
    #PreserveSingleQuotes(XX)# 
</cfquery>

<br>

<cfoutput Query="QueryTest">
    #UserName#, #WEEK_2#<br>
</cfoutput>

<br>
<cfdump var="#QueryTest#" />

这就是结果......

最后，如前所述，我想将“W”连接到周数字段。

任何正确方向的指导或指导将不胜感激，并感谢您抽出宝贵时间。

非常感谢您的阅读。

Answer 1

评论太长了。

由于列标签是动态的并且与数据库密切相关，所以我可能会在数据库端完成这一切。最好在存储过程中。

如果您使用的是 SQL Server 2017+，则可以使用 CTE 生成所需日期范围内的周数。那么STRING_AGG()用于将结果转换为逗号分隔的列表。

; WITH dates AS (
   -- Generate range between -56 and -52 weeks ago
   SELECT DateAdd(wk, -56, GETDATE()) AS WeekDate
   UNION ALL
   SELECT DateAdd(wk, 1, WeekDate)
   FROM   dates
   WHERE  DateAdd(wk, 1, WeekDate) <= DateAdd(wk, -52, GETDATE())
)
, dateLabels AS (
  -- extract week number and construct label "W1","W2",etc..
  SELECT  DATEPART(isowk, WeekDate) AS WeekNum
          , QUOTENAME( CONCAT('W', DATEPART(isowk, WeekDate) )) AS WeekLabel
  FROM   dates
)
-- convert to comma separated lists
SELECT STRING_AGG( WeekLabel, ',') 
            WITHIN GROUP (ORDER BY WeekNum) AS PivotColumns
      , STRING_AGG( CONCAT('ISNULL(', WeekLabel, ',0) AS ', WeekLabel ), ',') 
            WITHIN GROUP (ORDER BY WeekNum) AS SelectColumns
FROM  dateLabels
;

结果看起来像这样(减去任何换行)

PivotColumns	SelectColumns
[W6],[W7],[W8],[W9],[W10]	ISNULL([W6],0) AS [W6],ISNULL([W7],0) AS [W7],ISNULL([W8],0) AS [W8],ISNULL([W9],0) AS [W9],ISNULL([W10],0) AS [W10]

然后只需将这两个列表插入 SQL 查询即可。在 CF 中:

<cfscript>
   // ...
   sqlString = "
        SELECT UserName 
               , #SelectColumns#
        FROM (   

                ... 

             ) ST
             PIVOT 
             (   
                SUM(SalesCount)
                FOR XWeek IN ( #PivotColumns# )

             ) AS StorePivot
    ";

    qPivot = queryExecute( sqlString );
    // ...
</cfscript>

结果:

SELECT UserName
      , ISNULL([W6],0) AS [W6]
      , ISNULL([W7],0) AS [W7]
      , ISNULL([W8],0) AS [W8]
      , ISNULL([W9],0) AS [W9]
      , ISNULL([W10],0) AS [W10]
FROM (   

      ... 
      
   ) ST
   PIVOT (   
          SUM(SalesCount)
          FOR XWeek IN ( 
              [W6],[W7],[W8],[W9],[W10]
          )
  ) AS StorePivot

---

WHERE 哦，括号在哪里？

关于原始查询的一些观察:

当前的 WHERE 子句不正确。混合使用 AND/OR 运算符时，必须使用括号来确保运算顺序。 概念上，当前查询是这样做的:

   WHERE Condition1 AND Condition2 OR Condition3 

这里应该用括号:

   WHERE Condition1 AND ( Condition2 OR Condition3 )

GETDATE()已经返回一个 datetime 值，所以这里不需要再次将它转换为 datetime:

DATEADD(week, -52, convert(datetime, GETDATE()))

最后，不需要所有的串联。字符串通常可以跨越多行，如果需要，总是有 cfsavecontent。删除所有的 & 将大大提高可读性。