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
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
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
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
1\提供硬件质量
2\优化查询语句,加索引等.
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
有时间的朋友请帮我看下,诚信第一,我会给相应的补偿,QQ105096496 可以直接联系我,包括RMB,只要成功就OK 比我的速度快就可以,路过的朋友留下你的脚印帮我UP 谢谢!!
@oper=1 --待开盘
@oper=2 --交易中
@oper =3 --已交割
分开来看就很容易看懂.....有心者 与我联系
针对这个查询,建立group by后所有字段的复合索引会提高效率
单表只能尽量多的建立,
建议建立试图后在试图上建立符合索引会更好cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar) 这个感觉有点问题哦 应该变成 left(intyear+10000,4 ),left(intmonth +100,2 )这样吧
这个存储过程很多地方需要优化。
现在的运行时间是多少。
-------------------------------
楼主,做人低调点....