我的union all sql如下:
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('a')
结果如下:
1
1
0
想要执行对上面的结果进行sum,也就是对1,1,0 进行sum,按照下面的语句执行会出现错误:
select sum(a) from (
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('a')
)
错误如下:
消息 102,级别 15,状态 1,第 7 行
')' 附近有语法错误。我的环境是SqlServer2005, 请问可以在union all 之后进行sum么? 不行的话,谁能指教一个合适的方法。谢谢
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('a')
结果如下:
1
1
0
想要执行对上面的结果进行sum,也就是对1,1,0 进行sum,按照下面的语句执行会出现错误:
select sum(a) from (
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('a')
)
错误如下:
消息 102,级别 15,状态 1,第 7 行
')' 附近有语法错误。我的环境是SqlServer2005, 请问可以在union all 之后进行sum么? 不行的话,谁能指教一个合适的方法。谢谢
解决方案 »
- 这种语法是什么意思??CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
- 求助:sql server 2008 新建数据库路径的问题
- 关于sql server访问access的问题
- 跨表UPDATE CASE 求助
- 调用存储过程
- 求从Access导数据到SQL Server的SQL语句!
- 近日在做一练习时遇到了一个问题具体情况请进正文!!!谢谢浏览!
- 如何生成1万个卡号和密码?
- 如何在一个查询中调用两种排序
- 提高两表间之批量insert\update的效率(望各大高手多来发表高见)
- 求助:请问如下sql如何优化 多谢
- 帮忙看下这条SQL语句错在什么地方。。
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('a')
) as t
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('a')
) tmp
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('a')
) AS T
select sum(a) from (
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('N','1','2','3','4','5','6','7')
union all
select case isnull(sum(1),0) when 0 then 0 else 1 end as a from card_info where app_id=370 and card_type='贷记卡' and pay_in_last_12m in ('a')
) t