• 主页

# 麻烦各位帮我看看这个存储过程,急!!

qqok 2004-04-07 11:16:55

mysp_phsTotal --存储过程
companyVIEW --视图
Company--代理商表
Phs_Num--小灵通表

http://www.65288.com/bb.htm
============================================================
1.mysp_phsTotal

CREATE proc mysp_phsTotal
@uptime1 varchar(50),
@uptime2 varchar(50),
@fnote int
as
declare @sql varchar(8000)
set @sql='select companyid as [代理\数量/镇区] '
select @sql=@sql+',sum(case phsBrand when '''+phsBrand+''' then phsNum else 0 end ) '+ phsBrand
from (select distinct phsBrand from companyVIEW ) aaaa
select @sql=@sql+' ,sum(phsNum ) as 累计放号量 into #代理商放号量 From companyVIEW where convert(varchar(50), phsUpdate ,111) between ''' + @uptime1 + ''' and ''' + @uptime2 + ''' and note = ' + convert(varchar(10),@fnote) +' group by companyid order by 累计放号量 desc select * from #代理商放号量 union all select top 1 companyid=''累计放号量'','
select @sql=@sql+ phsBrand+'=(select sum(phsNum ) from companyVIEW where convert(varchar(50), phsUpdate ,111 ) between ''' + @uptime1 + ''' and ''' + @uptime2 + ''' and phsBrand='''+phsBrand+''' and note = ' + convert(varchar(10),@fnote) +'),'
from (select distinct phsBrand from companyVIEW ) aaaa
select @sql=@sql+' sum(phsNum) as 累计放号量 From companyVIEW where convert(varchar(50),phsUpdate ,111) between ''' + @uptime1 + ''' and ''' + @uptime2 + ''' and note = ' + convert(varchar(10),@fnote) +''
exec(@sql)
GO

2.companyVIEW --视图 :即为两个表关联

3.代理商表Company
ID CompanyID note
----------- ---------------------------------
1 平记 1
2 国信 1
3 国懋 1
4 捷通 1
5 科达 1
6 天地 1
22 润讯 2
23 凤宇 2
24 鸿基 2
36 讯联 2
37 讯兴 2
38 耀华 2
39 易昊 2
54 横沥 3
55 天纬 3
66 石龙 3
67 石排 3

4.小灵通表Phs_Num

ID CompanyID PhsBrand PhsNum PhsUpDate
--------------------------------------------------------------
19225 平记 UT318 40 2003-09-04 00:00:00
19226 平记 UT618+ 10 2003-09-04 00:00:00
19227 润讯 700-UC 50 2003-09-04 00:00:00
19228 润讯 UT318 12 2003-09-04 00:00:00
19229 润讯 UT618+ 13 2003-09-04 00:00:00
19230 天地 UT318 10 2003-09-04 00:00:00
19231 天地 UT618 100 2003-09-04 00:00:00
19232 天纬 700-UC 70 2003-09-04 00:00:00
19233 天纬 UT618 123 2003-09-04 00:00:00

5.网页代码

<%
'up_date1 = "2003-05-13 10:46:00"
'up_date2 = "2003-05-13 10:58:00"
up_date1 = wf_DateToChar(request("sdate"),"long")
up_date2 = wf_DateToChar(request("edate"),"long")
CmdSP.ActiveConnection = conn
cmdSp.CommandTimeout=1000
'response.write up_date1&" "&up_date2&" "&fnote
'response.end
Set adoRs = conn.execute("mysp_phsTotal '" & up_date1 & "','" & up_date2 & "','"&fnote&"'")
%>

<table width="775" border="0" cellspacing="0" cellpadding="0" >
<tr>
<td colspan="3"> <div align="center"><%=cc%>代理商放号量统计表</div></td>
</tr>
<tr>
<td height="8" colspan="3"></td>
</tr>
<tr>
<td width="39%" height="22">　日期：<%=wf_DateToChar(now(),"long")%> </td>
<td width="38%"><a href="../town/ok.xls" target="_blank"> </a>累计数日期：<%=up_date1%>-<%=up_date2%></td>
<td width="23%"><a href="total.xls" target="_blank">生成Excel</a></td>
</tr>
</table>
<table width="1000" height="8" border="1" cellpadding="0" cellspacing="0" bordercolordark="#FFFFFF" bordercolorlight="#000000" >
<%
dim n
response.write "<tr>"
for i = 0 to n
If i < n Then
response.write "<td width=100 align='center'>" & adors.fields(i).name & "</td>"
End If
Next

response.write "</tr>"

response.write "<tr>"
for i = 0 to n
If i <> n Then
If aa =0 Then aa = " "
End If
response.write "<td width=50>" & aa & "</td>"
End If
Next
response.write "</tr>"
wend

%>
</table>

...全文
16 点赞 收藏 27

27 条回复

--结果是:
2003/09/04 不在范围内 1 .......

--以你给我的数据为例,执行下面的查询:

select convert(varchar(50),phsupdate,111)
,case when convert(varchar(50),phsupdate,111) between '2003/09/1' and '2004/09/1'
then '在范围内' else '不在范围内' end
,* from companyview
where note=1
and companyid='科达' and phsbrand='[UT218]'

qqok 2004-04-08

CREATE proc mysp_phsTotal
@uptime1 varchar(50),
@uptime2 varchar(50),
@fnote int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+phsBrand+'=sum(case phsBrand when '''+phsBrand+''' then phsNum else 0 end )'
from (select distinct phsBrand from CompanyVIEW where phsBrand<>'[]')a

set @sql='select [代理\数量/镇区]=case grouping(companyid) when 1 then ''累计放号量'' else companyid end'+@sql+'
,累计放号量=sum(phsNum )
from CompanyVIEW
where note='+cast(@fnote as varchar)+'
and phsUpdate between '''+@uptime1+''' and '''+@uptime2+'''
group by CompanyID with rollup'
exec(@sql)
GO

up

qqok 2004-04-08
CREATE proc mysp_phsTotal
@uptime1 varchar(50),
@uptime2 varchar(50),
@fnote int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+phsBrand+'=sum(case phsBrand when '''+phsBrand+''' then phsNum else 0 end )'
from (select distinct phsBrand from CompanyVIEW where phsBrand<>'[]')a

set @sql='select [代理\数量/镇区]=case grouping(companyid) when 1 then ''累计放号量'' else companyid end'+@sql+'
,累计放号量=sum(phsNum )
from CompanyVIEW
where note='+cast(@fnote as varchar)+'
and phsUpdate between '''+@uptime1+''' and '''+@uptime2+'''
group by CompanyID with rollup'
exec(@sql)
GO

qqok 2004-04-08

--跟视图没什么关系啊? 你看我的测试

--测试

--测试数据
create table Company(ID int,CompanyID char(10),note int)
insert company select 1,'平记',1
union all select 2,'国信',1
union all select 3,'国懋',1
union all select 4,'捷通',1
union all select 5,'科达',1
union all select 6,'天地',1
union all select 22,'润讯',2
union all select 23,'凤宇',2
union all select 24,'鸿基',2
union all select 36,'讯联',2
union all select 37,'讯兴',2
union all select 38,'耀华',2
union all select 39,'易昊',2
union all select 54,'横沥',3
union all select 55,'天纬',3
union all select 66,'石龙',3
union all select 67,'石排',3

create table Phs_Num(ID int,CompanyID char(10),PhsBrand varchar(10),PhsNum int,PhsUpDate datetime)
insert Phs_Num select 19225,'平记','UT318',40,'2003-09-04 00:00:00'
union all select 19226,'平记','UT618+',10,'2003-09-04 00:00:00'
union all select 19227,'润讯','700-UC',50,'2003-09-04 00:00:00'
union all select 19228,'润讯','UT318',12,'2003-09-04 00:00:00'
union all select 19229,'润讯','UT618+',13,'2003-09-04 00:00:00'
union all select 19230,'天地','UT318',10,'2003-09-04 00:00:00'
union all select 19231,'天地','UT618',100,'2003-09-04 00:00:00'
union all select 19232,'天纬','700-UC',70,'2003-09-04 00:00:00'
union all select 19233,'天纬','UT618',123,'2003-09-04 00:00:00'
go

--视图
CREATE VIEW dbo.CompanyVIEW
AS
SELECT dbo.Company.note, dbo.Phs_Num.ID, dbo.Phs_Num.PhsNum,
dbo.Phs_Num.PhsUpDate, '[' + dbo.Phs_Num.PhsBrand + ']' as Phsbrand, dbo.Company.CompanyID
FROM dbo.Company INNER JOIN
dbo.Phs_Num ON dbo.Company.CompanyID = dbo.Phs_Num.CompanyID
go

--存储过程
CREATE proc mysp_phsTotal
@uptime1 varchar(50),
@uptime2 varchar(50),
@fnote int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+phsBrand+'=sum(case phsBrand when '''+phsBrand+''' then phsNum else 0 end )'
from (select distinct phsBrand from CompanyVIEW where phsBrand<>'[]')a

set @sql='select [代理\数量/镇区]=case grouping(companyid) when 1 then ''累计放号量'' else companyid end'+@sql+'
,累计放号量=sum(phsNum )
from CompanyVIEW
where note='+cast(@fnote as varchar)+'
and phsUpdate between '''+@uptime1+''' and '''+@uptime2+'''
group by CompanyID with rollup'
exec(@sql)
GO

--调用
exec mysp_phsTotal '2003/09/1','2004/09/1',1
go

--删除测试
drop table Company,Phs_Num
drop view CompanyVIEW
drop proc mysp_phsTotal

/*--测试结果

---------- ----------- ----------- ----------- ----------- -----------

（所影响的行数为 3 行）
--*/

qqok 2004-04-08
Phs_Num 表的 PhsBrand 字段没有空值,还是出错.

CREATE VIEW dbo.CompanyVIEW
AS
SELECT dbo.Company.note, dbo.Phs_Num.ID, dbo.Phs_Num.PhsNum,
dbo.Phs_Num.PhsUpDate, '[' + dbo.Phs_Num.PhsBrand + ']' as Phsbrand, dbo.Company.CompanyID
FROM dbo.Company INNER JOIN
dbo.Phs_Num ON dbo.Company.CompanyID = dbo.Phs_Num.CompanyID

snowcloud2002 2004-04-08

qqok 2004-04-08
TO:zjcxc(: 邹建 :)

qqok 2004-04-07

qqok 2004-04-07
UP UP UP!!!

qqok 2004-04-07
UP

qqok 2004-04-07
exec mysp_phsTotal '2003/09/1','2004/09/1',1
go

qqok 2004-04-07

CCEO 2004-04-07

Phs_Num:（PhsUpDate,CompanyID）

Company：(CompanyID,note) ---数据少，可以不建

ASP

2.8w+

ASP即Active Server Pages，是Microsoft公司开发的服务器端脚本环境。