http://topic.csdn.net/u/20071218/21/9941d141-55b5-4711-88c8-ffad269f0869.html
问题如上.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[QueryCount]
(
@cust_id varchar(20)
)
AS
select isnull(TianShu,'f') TianShu ,
sum(case when flag is null or flag='' then 1 else 0 end) 'c1',--未处理
sum(case when flag ='0' or flag='1' then 1 else 0 end) 'c2',--正在处理中
--sum(case flag when 2 then 1 else 0 end) 'c3',--已回复
sum(case isnull(flag,3) when 3 then 1 else 0 end) 'c4'--无效
from
(
select input_time,flag,isnull(a.TianShu,b.TianShu) TianShu from
(select input_time , flag , TianShu =
case
when datediff(day,input_time,getdate()) between 0 and 7 then 'a'
when datediff(day,input_time,getdate()) between 8 and 15 then 'b'
when datediff(day,input_time,getdate()) between 16 and 30 then 'c'
when datediff(day,input_time,getdate()) between 31 and 90 then 'd'
when datediff(day,input_time,getdate()) > 90 then 'e'
end
from letter_files where input_time is not null and cust_id=rtrim(@cust_id)) a --这里的cust_id=rtrim(@cust_id)不起筛选作用
right join
(select 'a' as TianShu union all select 'b' union all
select 'c' union all select 'd' union all select 'e') b
on a.TianShu = b.TianShu
) t
group by TianShu with rollup
问题如上.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[QueryCount]
(
@cust_id varchar(20)
)
AS
select isnull(TianShu,'f') TianShu ,
sum(case when flag is null or flag='' then 1 else 0 end) 'c1',--未处理
sum(case when flag ='0' or flag='1' then 1 else 0 end) 'c2',--正在处理中
--sum(case flag when 2 then 1 else 0 end) 'c3',--已回复
sum(case isnull(flag,3) when 3 then 1 else 0 end) 'c4'--无效
from
(
select input_time,flag,isnull(a.TianShu,b.TianShu) TianShu from
(select input_time , flag , TianShu =
case
when datediff(day,input_time,getdate()) between 0 and 7 then 'a'
when datediff(day,input_time,getdate()) between 8 and 15 then 'b'
when datediff(day,input_time,getdate()) between 16 and 30 then 'c'
when datediff(day,input_time,getdate()) between 31 and 90 then 'd'
when datediff(day,input_time,getdate()) > 90 then 'e'
end
from letter_files where input_time is not null and cust_id=rtrim(@cust_id)) a --这里的cust_id=rtrim(@cust_id)不起筛选作用
right join
(select 'a' as TianShu union all select 'b' union all
select 'c' union all select 'd' union all select 'e') b
on a.TianShu = b.TianShu
) t
group by TianShu with rollup
解决方案 »
- 数据库操作错误,系统提示如下
- 求一条SQL语句,希望大家帮忙
- 关于数据类型的问题
- 什么情况下触发器不起作用?
- 关于SQL版本的恢复问题?
- 网络中用ODBC建立SQL SEVER 的数据源,应该如何建立呢
- 请教一句SQL语句!谢谢!
- 请问两表关联的查询语句写法,谢谢!
- 请帮忙了:P oracle问题,thanks
- 在sql server 中,我刚开始做了一张bbsUser的表, 后来删除了, 我重新做了一张bbsUser表,只是UBirthty字段改成了UBirthday
- sql查询--时间--问题--散分
- sqlserver2005的备份用sqlserver2000怎么还原啊,已经有了2005的备份但那个机子上只有2000的
我是想利用该表的另一个字段 cust_id 等于存储过程的 @cust_id --传入参数
作条件筛选,不知道为什么加了不起作用,究竟加在哪个地方或如何修改?exec QueryCount 'xxx' 无论xxx为任何值依然把表的所有数据都统计了.奇怪
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[QueryCount]
(
@cust_id varchar(20)
)
AS
select isnull(TianShu,'f') TianShu ,
sum(case when flag is null or flag='' then 1 else 0 end) 'c1',--未处理
sum(case when flag ='0' or flag='1' then 1 else 0 end) 'c2',--正在处理中
--sum(case flag when 2 then 1 else 0 end) 'c3',--已回复
sum(case isnull(flag,3) when 3 then 1 else 0 end) 'c4'--无效
from
(
select input_time,flag,isnull(a.TianShu,b.TianShu) TianShu from
(select input_time , flag , TianShu =
case
when datediff(day,input_time,getdate()) between 0 and 7 then 'a'
when datediff(day,input_time,getdate()) between 8 and 15 then 'b'
when datediff(day,input_time,getdate()) between 16 and 30 then 'c'
when datediff(day,input_time,getdate()) between 31 and 90 then 'd'
when datediff(day,input_time,getdate()) > 90 then 'e'
end
from letter_files where input_time is not null ) a --这里的cust_id=rtrim(@cust_id)不起筛选作用
right join
(select 'a' as TianShu union all select 'b' union all
select 'c' union all select 'd' union all select 'e') b
on a.TianShu = b.TianShu
where cust_id=rtrim(@cust_id) --挪到这里的试试
) t
group by TianShu with rollup
列名 'cust_id' 无效。
列名 'cust_id' 无效。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[QueryCount]
(
@cust_id varchar(20)
)
AS
select isnull(TianShu,'f') TianShu ,
sum(case when flag is null or flag='' then 1 else 0 end) 'c1',--未处理
sum(case when flag ='0' or flag='1' then 1 else 0 end) 'c2',--正在处理中
--sum(case flag when 2 then 1 else 0 end) 'c3',--已回复
sum(case isnull(flag,3) when 3 then 1 else 0 end) 'c4'--无效
from
(
select input_time,flag,isnull(a.TianShu,b.TianShu) TianShu , cust_id from
(select input_time , flag , TianShu =
case
when datediff(day,input_time,getdate()) between 0 and 7 then 'a'
when datediff(day,input_time,getdate()) between 8 and 15 then 'b'
when datediff(day,input_time,getdate()) between 16 and 30 then 'c'
when datediff(day,input_time,getdate()) between 31 and 90 then 'd'
when datediff(day,input_time,getdate()) > 90 then 'e'
end , cust_id
from letter_files where input_time is not null ) a --这里的cust_id=rtrim(@cust_id)不起筛选作用
right join
(select 'a' as TianShu union all select 'b' union all
select 'c' union all select 'd' union all select 'e') b
on a.TianShu = b.TianShu
where cust_id=rtrim(@cust_id) --挪到这里的试试
) t
group by TianShu with rollup
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[QueryCount]
(
@cust_id varchar(20)
)
AS
select isnull(TianShu,'f') TianShu ,
sum(case when flag is null or flag='' then 1 else 0 end) 'c1',--未处理
sum(case when flag ='0' or flag='1' then 1 else 0 end) 'c2',--正在处理中
--sum(case flag when 2 then 1 else 0 end) 'c3',--已回复
sum(case isnull(flag,3) when 3 then 1 else 0 end) 'c4'--无效
from
(
select input_time,flag,isnull(a.TianShu,b.TianShu) TianShu from
(select input_time , flag , TianShu =
case
when datediff(day,input_time,getdate()) between 0 and 7 then 'a'
when datediff(day,input_time,getdate()) between 8 and 15 then 'b'
when datediff(day,input_time,getdate()) between 16 and 30 then 'c'
when datediff(day,input_time,getdate()) between 31 and 90 then 'd'
when datediff(day,input_time,getdate()) > 90 then 'e'
end
from letter_files where input_time is not null) a
right join
(select 'a' as TianShu union all select 'b' union all
select 'c' union all select 'd' union all select 'e') b
on a.TianShu = b.TianShu
where a.cust_id=rtrim(@cust_id) --放这里
) t
group by TianShu with rollup
列名 'cust_id' 无效。
---------
right join--表连接这里