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

在临时表中插入多个SELECT语句

sql-server-2012 sql-server sql

SP1 · 技术社区 · 6 年前

我有以下问题:

Insert into #BidYTDRegions (Code,APAC,EMEA,NAMerica,LAMerica)
    select 'Payroll', Count(*) from DashboardData
    where DataType = 'Bid' and SMHQRegion = 'APAC'  
    and Services like '%Streamline Payroll%',
    Count(*) from DashboardData
    where DataType = 'Bid' and SMHQRegion = 'EMEA'  
    and Services like '%Streamline Payroll%',
    Count(*) from DashboardData
    where DataType = 'Bid' and SMHQRegion = 'N. America'    
    and Services like '%roll%'  ,
    Count(*) from DashboardData
    where DataType = 'Bid' and SMHQRegion = 'L. America'    
    and Services like '%roll%'

我出错了 Incorrect syntax near ','.

我所要做的就是在基于select语句的临时表中插入一些数据。下面是我的临时表

Create table #BidYTDRegions
(   
  Code nvarchar(50), 
  APAC int, 
  APACRatio nvarchar(20),
  EMEA int, 
  EMEARatio nvarchar(20),
  NAMerica int, 
  NAMericaRatio nvarchar(20),
  LAmerica int, 
  LAmericaRatio nvarchar(20),  
)

2 回复 | 直到 6 年前

S3S 6 年前

看起来您需要子查询,具体操作如下:

Insert into #BidYTDRegions (Code,APAC,EMEA,NAMerica,LAMerica)
    select 'Payroll'
        ,(select Count(*) from DashboardData
          where DataType = 'Bid' and SMHQRegion = 'APAC'  
          and Services like '%Streamline Payroll%')
        ,(select Count(*) from DashboardData
          where DataType = 'Bid' and SMHQRegion = 'EMEA'  
          and Services like '%Streamline Payroll%')
        ,(select Count(*) from DashboardData
          where DataType = 'Bid' and SMHQRegion = 'N. America'    
          and Services like '%roll%')
        ,(select Count(*) from DashboardData
          where DataType = 'Bid' and SMHQRegion = 'L. America'    
          and Services like '%roll%')

Gordon Linoff 6 年前

我认为您需要条件聚合:

Insert into #BidYTDRegions (Code, APAC, EMEA, NAMerica, LAMerica)
    select 'Payroll',
            sum(case when SMHQRegion = 'APAC' and Services like '%Streamline Payroll%' then 1 else 0 end),
            sum(case when SMHQRegion = 'EMEA' and Services like '%Streamline Payroll%' then 1 else 0 end),
            sum(case when SMHQRegion = 'N. America' and Services like '%roll%' then 1 else 0 end),
            sum(case when SMHQRegion = 'S. America' and Services like '%roll%' then 1 else 0 end)
    from DashboardData
    where DataType = 'Bid';

我不清楚为什么 Services 对不同地区有不同的比较。如果它是相同的,那么这个条件就可以被分解并转移到 WHERE 条款与 DataType .