文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >Select from select with case语句和union all

问Select from select with case语句和union all
EN

Stack Overflow用户
提问于 2011-09-15 22:50:32
回答 4查看 6.1K关注 0票数 0

当我执行查询时

代码语言：javascript
复制
SELECT Settlement_Fees.Participant_Name, Settlement_Fees.Account, Settlement_Fees.Billing_Account, Settlement_Fees.Descr1, Settlement_Fees.Market, Settlement_Fees.Instrum
--, IIf(Settlement_Fees.Instr_Type='Internal','bsinternal',Settlement_Fees.Instr_Type) AS Expr1
,(case when Settlement_Fees.Instr_Type='Internal' then 'bsinternal'
  else Settlement_Fees.Instr_Type
 end )
, Settlement_Fees.Country, Settlement_Fees.Nr_Instr_Business_Unit, Settlement_Fees.Nr_Instr_Account, Settlement_Fees.Avg_EUR_Rate, Settlement_Fees.Fee_Amount_EUR, Settlement_Fees.Value_Date_Adj
FROM Settlement_Fees)
union all
 (select '','',Billing_Account,'','','',
( case when Instr_Type like '%Bridge%' or Instr_Type = '%Internal%' then 'Bszridge/Internal'
else  Instr_Type
end )
 ,'','',Nr_Instr_Account  ,'',Fee_Amount_EUR ,'' 
from Settlement_Fees group by Settlement_Fees.Billing_Account,Settlement_Fees.Instr_Type
,Settlement_Fees.Nr_Instr_Account,Fee_Amount_EUR)
union all 
(select '','',Billing_Account,'','','','Total','','',sum(Nr_Instr_Account),'',sum(Fee_Amount_EUR) ,''
from Settlement_Fees group by Billing_Account

它运行良好。

但当我使用select * from ()执行时，它给出错误“消息102，级别15，状态1，第33行‘)’附近语法不正确。”对于以下查询

代码语言：javascript
复制
SELECT *
FROM  ((SELECT settlement_fees.participant_name,
               settlement_fees.ACCOUNT,
               settlement_fees.billing_account,
               settlement_fees.descr1,
               settlement_fees.market,
               settlement_fees.instrum
               --, IIf(Settlement_Fees.Instr_Type='Internal','bsinternal',Settlement_Fees.Instr_Type) AS Expr1
               ,
               ( CASE
                   WHEN settlement_fees.instr_type = 'Internal' THEN
                   'bsinternal'
                   ELSE settlement_fees.instr_type
                 END ),
               settlement_fees.country,
               settlement_fees.nr_instr_business_unit,
               settlement_fees.nr_instr_account,
               settlement_fees.avg_eur_rate,
               settlement_fees.fee_amount_eur,
               settlement_fees.value_date_adj
        FROM   settlement_fees)
       UNION ALL
       (SELECT '',
               '',
               billing_account,
               '',
               '',
               '',
               ( CASE
                   WHEN instr_type LIKE '%Bridge%'
                         OR instr_type = '%Internal%' THEN 'Bszridge/Internal'
                   ELSE instr_type
                 END ),
               '',
               '',
               nr_instr_account,
               '',
               fee_amount_eur,
               ''
        FROM   settlement_fees
        GROUP  BY settlement_fees.billing_account,
                  settlement_fees.instr_type,
                  settlement_fees.nr_instr_account,
                  fee_amount_eur)
       UNION ALL
       (SELECT '',
               '',
               billing_account,
               '',
               '',
               '',
               'Total',
               '',
               '',
               SUM(nr_instr_account),
               '',
               SUM(fee_amount_eur),
               ''
        FROM   settlement_fees
        GROUP  BY billing_account))
sql
sql-server
tsql
EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2011-09-15 22:57:45

你应该命名你的子查询：

Select * from () subqueryName

代码语言：javascript
复制
 select * from(
(SELECT Settlement_Fees.Participant_Name, Settlement_Fees.Account, Settlement_Fees.Billing_Account, Settlement_Fees.Descr1, Settlement_Fees.Market, Settlement_Fees.Instrum
--, IIf(Settlement_Fees.Instr_Type='Internal','bsinternal',Settlement_Fees.Instr_Type) AS Expr1
,(case when Settlement_Fees.Instr_Type='Internal' then 'bsinternal'
  else Settlement_Fees.Instr_Type
 end )
, Settlement_Fees.Country, Settlement_Fees.Nr_Instr_Business_Unit, Settlement_Fees.Nr_Instr_Account, Settlement_Fees.Avg_EUR_Rate, Settlement_Fees.Fee_Amount_EUR, Settlement_Fees.Value_Date_Adj
FROM Settlement_Fees)
union all
 (select '','',Billing_Account,'','','',
( case when Instr_Type like '%Bridge%' or Instr_Type = '%Internal%' then 'Bszridge/Internal'
else  Instr_Type
end )
 ,'','',Nr_Instr_Account  ,'',Fee_Amount_EUR ,'' 
from Settlement_Fees group by Settlement_Fees.Billing_Account,Settlement_Fees.Instr_Type
,Settlement_Fees.Nr_Instr_Account,Fee_Amount_EUR)
union all 
(select '','',Billing_Account,'','','','Total','','',sum(Nr_Instr_Account),'',sum(Fee_Amount_EUR) ,''
from Settlement_Fees group by Billing_Account
)) subqueryName
票数 2
EN

Stack Overflow用户

发布于 2011-09-15 22:52:46

您需要为派生表指定一个表别名。例如，将as t添加到查询的最后。

此外，为了修复注释更改中提出的问题

代码语言：javascript
复制
( CASE
                   WHEN settlement_fees.instr_type = 'Internal' THEN
                   'bsinternal'
                   ELSE settlement_fees.instr_type
                 END )

至

代码语言：javascript
复制
   CASE
       WHEN settlement_fees.instr_type = 'Internal' THEN
       'bsinternal'
       ELSE settlement_fees.instr_type
     END AS Foo
票数 2
EN

Stack Overflow用户

发布于 2011-09-15 22:52:58

你有一组额外的()和别名你的内联视图/派生表也有帮助。

代码语言：javascript
复制
select * from(
    SELECT 
        Settlement_Fees.Participant_Name, 
        Settlement_Fees.Account, 
        Settlement_Fees.Billing_Account, 
        Settlement_Fees.Descr1, 
        Settlement_Fees.Market, 
        Settlement_Fees.Instrum,
        (case when Settlement_Fees.Instr_Type='Internal' then 'bsinternal'
            else Settlement_Fees.Instr_Type
        end ) as SomthingHere, 
        Settlement_Fees.Country, 
        Settlement_Fees.Nr_Instr_Business_Unit, 
        Settlement_Fees.Nr_Instr_Account, 
        Settlement_Fees.Avg_EUR_Rate, 
        Settlement_Fees.Fee_Amount_EUR, 
        Settlement_Fees.Value_Date_Adj
    FROM Settlement_Fees
    UNION ALL select 
        '',
        '',
        Billing_Account,
        '',
        '',
        '',
        ( case when Instr_Type like '%Bridge%' or Instr_Type = '%Internal%' then 'Bszridge/Internal'
        else  Instr_Type
        end ),
        '',
        '',
        Nr_Instr_Account  ,
        '',
        Fee_Amount_EUR ,
        '' 
    from 
        Settlement_Fees 
    group by 
        Settlement_Fees.Billing_Account,
        Settlement_Fees.Instr_Type,
        Settlement_Fees.Nr_Instr_Account,
        Fee_Amount_EUR
    union all select 
        '',
        '',
        Billing_Account,
        '',
        '',
        '',
        'Total',
        '',
        '',
        sum(Nr_Instr_Account),
        '',
        sum(Fee_Amount_EUR) ,
        ''
    from 
        Settlement_Fees 
    group by 
        Billing_Account
) as foo
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/7432864

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档