有这样一个存储过程:CREATE PROCEDURE tingji
@mth int
as
if @mth >12 or @mth <1 return
declare @cc varchar(300)set @cc='select count(*)
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1S" and exchange_i in (32061,540000)'
exec(@cc)
GO执行一下是没有问题的,但我要同时查询不只一个结果,也就是要用 union 连接起来
比如,我要这样写:
CREATE PROCEDURE tingji
@mth int
as
if @mth >12 or @mth <1 return
declare @cc varchar(300)set @cc='select count(*)
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1S" and exchange_i in (32061,540000)
union
select count(*)
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1N"and exchange_i in (32061,540000)'
exec(@cc)
GO------------------------
用这种方法语法检查没问题,但运行起来,是有错误的请问我该怎么修改?
@mth int
as
if @mth >12 or @mth <1 return
declare @cc varchar(300)set @cc='select count(*)
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1S" and exchange_i in (32061,540000)'
exec(@cc)
GO执行一下是没有问题的,但我要同时查询不只一个结果,也就是要用 union 连接起来
比如,我要这样写:
CREATE PROCEDURE tingji
@mth int
as
if @mth >12 or @mth <1 return
declare @cc varchar(300)set @cc='select count(*)
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1S" and exchange_i in (32061,540000)
union
select count(*)
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1N"and exchange_i in (32061,540000)'
exec(@cc)
GO------------------------
用这种方法语法检查没问题,但运行起来,是有错误的请问我该怎么修改?
@mth int
as
if @mth >12 or @mth <1 return
declare @cc varchar(300)set @cc='select count(*)
from ['+'dhzl'+cast(@mth as varchar)+']where (serv_state like '%F1S%' or serv_state like '%F1N%') and exchange_i in (32061,540000)exec(@cc)
GO
我要的是,求,当 serv_state 为 F1S 有多少条记录,然后当serv_state为 F1N 的时候有多少条
记录,然后两条记录同时出现
sum(case when serv_state='F1S' then 1 else 0 end) as F1S,
sum(case when serv_state='F1N' then 1 else 0 end) as F1N
from dhzl
CREATE PROCEDURE tingji
@mth int
as
if @mth >12 or @mth <1 returndeclare @cc varchar(8000)
set @cc='
select count(*) from [dhzl'+cast(@mth as varchar)+'] where serv_state like "F1S%" and exchange_i in (32061,540000)
union
select count(*) from [dhzl'+cast(@mth as varchar)+'] where serv_state like "F1N%" and exchange_i in (32061,540000)'
exec(@cc)
GO
select count(*) from a where serv_state like 'F1N'// 有3条记录
union//合并
select count(*) from a where serv_state like 'F1N'// 有4条记录
执行后结果就为:
3
4但,我现在要使用比较复杂一点的存储过程,如下面的代码:
CREATE PROCEDURE tingji
@mth int
as
if @mth >12 or @mth <1 return
declare @cc varchar(300)set @cc='select count(*)
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1S" and exchange_i in (32061,540000)//当 serv_state 为F1S 时的记录条数
union//合并两个结果
select count(*)
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1N"and exchange_i in (32061,540000)' //当 serv_state 为 F1N 时的记录条数
exec(@cc)
GO
---------------------------------------却会出错,也就时,我上面那个存储过程代码里,使用union会报错,我不知道怎样改,使得那段代码里可以用union 来合并两个结果
@mth int
as
if @mth >12 or @mth <1 return
declare @cc varchar(300)set @cc='select count(*)
from ['+'dhzl'+rtrim(@mth)+'] where serv_state like ''F1S'' and exchange_i in (32061,540000)
union
select count(*)
from ['+'dhzl'+rtrim(@mth)+'] where serv_state like ''F1N'' and exchange_i in (32061,540000)'
exec(@cc)
GO
@mth int
as
if @mth >12 or @mth <1 return
declare @cc varchar(300)set @cc='select count(*) as cnt
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1S" and exchange_i in (32061,540000)//当 serv_state 为F1S 时的记录条数
union//合并两个结果
select count(*) as cnt
from ['+'dhzl'+cast(@mth as varchar)+']where serv_state like "F1N"and exchange_i in (32061,540000)' //当 serv_state 为 F1N 时的记录条数
exec(@cc)
GO
---------------------------------------