代码之家 › 专栏 › 技术社区 › User987

SQL Server:将选定列表中的列与别名相乘[重复]

multiplying select sql-server-2008 sql-server sql

-3

User987 · 技术社区 · 8 年前

SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
    MIN(t.Title) AS Title,
    t.ItemID, ' + @selectedColumn + ' as SelectedColumnSales ' + ', 
    t.CurrentPrice,
    (t.CurrentPrice * t.SelectedColumnSales) as TotalRevenuePerItem
FROM 
    dbo.SearchedUserItems t

我遇到问题的部分是:

  (t.CurrentPrice * t.SelectedColumnSales) as TotalRevenuePerItem

DECLARE @selectedColumn NVARCHAR(500)

    IF(@SelectedRange=7)
       SET @selectedColumn = 't.SevenDaySales'
    ELSE IF (@SelectedRange=14)
       SET @selectedColumn='t.FourteenDaySales'
    ELSE IF (@SelectedRange=21)
       SET @selectedColumn='t.TwentyOneDaySales'
    ELSE IF (@SelectedRange=30)
       SET @selectedColumn='t.ThirtyDaySales'

现在,要获得每项列的收入,我需要将这两个列相乘,如上所述,但查询抛出以下错误:

内部异常:SqlException:列名“SelectedColumnSales”无效。

如何将“选择”列表中的“动态”列与“静态”列相乘?

有人能帮我吗?

4 回复 | 直到 8 年前

JamieD77 8 年前

如果你正在写一个动态查询,看起来你是这样的,你可以把变量放在你的方程中

 declare @sql nvarchar(max)
 set @sql = 'select 
    COUNT(*) OVER () AS TotalRowsFound,
        MIN(t.Title) AS Title
        , t.ItemID
        ,' + @selectedColumn + ' as SelectedColumnSales ' + 
        ', t.CurrentPrice
         , (t.CurrentPrice * ' + @selectedColumn + ') as TotalRevenuePerItem

    FROM 
        dbo.SearchedUserItems t'
exec(@sql)

John Cappelletti 8 年前

CHOOSE()

假设:

Item               Index
SevenDaySales      0
FourteenDaySales   1
TwentyOneDaySales  2
ThirtyDaySales     3

那你就可以

SELECT COUNT(*) OVER () AS TotalRowsFound
      ,MIN(t.Title) AS Title
      ,t.ItemID
      ,choose(@Index+1,t.SevenDaySales,t.FourteenDaySales,t.TwentyOneDaySales,t.ThirtyDaySales) as SelectedColumnSales  
      ,t.CurrentPrice
      ,(t.CurrentPrice * choose(@Index+1,t.SevenDaySales,t.FourteenDaySales,t.TwentyOneDaySales,t.ThirtyDaySales)) as TotalRevenuePerItem
 FROM  dbo.SearchedUserItems t

@Index+1 @SelectedRange/7

ScaisEdge 8 年前

您可以尝试使用case when子句直接在查询中登录,而不是使用字符串concat

 SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
        MIN(t.Title) AS Title
        , t.ItemID
        , case when @SelectedRange=7 then t.SevenDaySales 
              when @SelectedRange=14 then t.FourteenDaySales
              when @SelectedRange=21 then t.TwentyOneDaySales
              when (@SelectedRange=30 then t.ThirtyDaySales end 
         as SelectedColumnSales  
        , t.CurrentPrice
         , (t.CurrentPrice * t.SelectedColumnSales) as TotalRevenuePerItem

    FROM 
        dbo.SearchedUserItems t

jarlh 8 年前

select dt.TotalRowsFound, dt.Title, ... ,
      (dt.CurrentPrice * dt.SelectedColumnSales) as TotalRevenuePerItem
from
(
  SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
        MIN(t.Title) AS Title
        , t.ItemID
        ,' + @selectedColumn + ' as SelectedColumnSales ' + 
        ', t.CurrentPrice
    FROM 
        dbo.SearchedUserItems t
) dt