| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛 | 
登录
注册
用户控制面板
搜索
道具
风格
短消息
论坛展区
帮助
编程论坛 → WEB开发 → 『 ASP技术论坛 』 → 一个 SQL 查询问题...
我的收件箱(0)
欢迎加入我们，一同切磋技术
用户名：   
 
密　码：  
共有 373 人关注过本帖
标题：一个 SQL 查询问题...
取消只看楼主 加入收藏
sm105096496
Rank: 1
等　级：新手上路
帖　子：58
专家分：0
注　册：2007-7-11
楼主
收藏
 问题点数：0 回复次数：0 
一个 SQL 查询问题...

/*
用途：向网站用户显示发布的拍卖信息
说明：@oper=1 --待开盘
@oper=2 --交易中
@oper =3 --已交割
@intsyle=0 --全部品种
@intstyle=1 --空间交易
@intstyle=2 --短信交易
@intstyle=3 --月亮交易
@oper=4 --未结算
@oper=5 --根据交易时间段
Made by:Huazhen
*/
CREATE PROCEDURE [2006_DisplayExchangesForUser]
@oper int=0,
@intstyle smallint=0,
@memberid int=0,
@att smallint=0,
@start nvarchar(50)='',
@end nvarchar(50)=''
AS
declare @s datetime,@e datetime
set @start=isnull(@start,'')
set @end=isnull(@end,'')
if @oper=1 --待开盘
begin
if @att=0
begin
if @intstyle>0 --指定交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and

intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime))>0
group by b.mainid,a.mainid,exgNo,cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and

intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime))>0
group by b.mainid,a.mainid,exgNo,
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else if @intstyle=0 --所有交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime))>0
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @e and @s)
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
end
else if @att=1
begin
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)

as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum(b.sonprice*b.sonnum)/sum(b.sonnum)) as

avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a inner join [2006_stock_attention] c on a.mainid=c.mainid inner join [2006_stock_son]

b on a.mainid=b.mainid
where a.isopen=1 and a.intyear is not null and a.intmonth is not null and a.intday is not null and

c.userid=@memberid
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)

as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
end
else if @oper=2
begin
if @att=0
begin
if @intstyle>0 --指定交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and

intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as

nvarchar)+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and

intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as

nvarchar)+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else if @intstyle=0 --所有交易品种
begin

if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as

nvarchar)+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
begin
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as

nvarchar)+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
end
end
else if @att=1
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)

as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum(b.sonprice*b.sonnum)/sum(b.sonnum)) as

avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a inner join [2006_stock_attention] c on a.mainid=c.mainid inner join [2006_stock_son]

b on a.mainid=b.mainid
where a.isopen=1 and a.intyear is not null and a.intmonth is not null and a.intday is not

null and c.userid=@memberid
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)

as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else if @oper=3
begin
if @att=0
begin
if @intstyle>0 --指定交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and

intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as

nvarchar)+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and

intday is not null
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as

nvarchar)+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else if @intstyle=0 --所有交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as

nvarchar)+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum

(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as

nvarchar)+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast

(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
end
else if @att=1
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)

as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum(b.sonprice*b.sonnum)/sum(b.sonnum)) as

avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a inner join [2006_stock_attention] c on a.mainid=c.mainid inner join [2006_stock_son]

b on a.mainid=b.mainid
where a.isopen=1 and a.intyear is not null and a.intmonth is not null and a.intday is not null and

c.userid=@memberid
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)

+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)

as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as

nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
GO

搜索更多相关主题的帖子: 查询　SQL　
2007-08-07 13:57
快速回复：一个 SQL 查询问题...
数据加载中...
 
　　　



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有，并保留所有权利。
Powered by Discuz, Processed in 0.032490 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved