--這樣? declare @t table([date] datetime,[Name] varchar(10)) insert @t select '2008-01-01','A' insert @t select '2008-01-01','B' insert @t select '2008-01-01','C' insert @t select '2009-01-01','D' insert @t select '2009-01-01','E' insert @t select '2010-01-01','F' select * ,排序=(select count(*)+1 from @t t1 where t1.[date]=t2.[date] and t1.[name]<t2.[name]) from @t t2/* date Name 排序 ------------------------------------------------------ ---------- ----------- 2008-01-01 00:00:00.000 A 1 2008-01-01 00:00:00.000 B 2 2008-01-01 00:00:00.000 C 3 2009-01-01 00:00:00.000 D 1 2009-01-01 00:00:00.000 E 2 2010-01-01 00:00:00.000 F 1(影響 6 個資料列)*/
谢谢大家!! 您看这个有没有办法优化一下 速度太慢了!! select '1' as iszf, (select count(*) from (select top 100 percent ht_hz.id from ht_hz left join tjjxmb on ht_hz.gcbm=xmbh left join fkjhbgzk on fkjhbgzk.htbm=ht_hz.htbm where gcnd=convert(varchar(4),getdate(),120) and tjjxmb.isdelete='0' and ht_hz.ztxx='YTKFQJCSS' and ((select count(*) from Fhjh_zjzf where Fhjh_zjzf.htbm=ht_hz.htbm)+(select count(*) from FkjhW_zjzf where FkjhW_zjzf.htbm=ht_hz.htbm) =(select count(*) from tb_pingzheng where tb_pingzheng.kmdm=ht_hz.fzbm)) and ht_hz.gcbm not in(select f_gcbh from gcxmzz) and ht_hz.gcbm not in (select ht_hz.gcbm from gcxmzz_ht,ht_hz where ht_hz.htbm=f_htbh) and ht_hz.fzbm in (select ht_hz.fzbm from ht_hz group by ht_hz.fzbm having count(*)=1) and ht_hz.htfzr='张少晨' order by ht_hz.id ) as ss where a.id>=id) as mxcode,a.* from (select top 100 percent htmc,yf=(select companyname from xt_wldwbm where yf=companyid), jhkgrq,isnull(ysje,0) as tzje, ybje=isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0),qbje=(ysje-isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0)), bcje=(yfjeing-isnull((select sum(sqje) from Fhjh_zjzf where ht_hz.htbm=Fhjh_zjzf.htbm),0)), cwbk=isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0), ht_hz.htbm,ht_hz.fzbm,ht_hz.htfzr,ht_hz.id ,'一'as bz,isfq from ht_hz left join tjjxmb on ht_hz.gcbm=xmbh left join fkjhbgzk on fkjhbgzk.htbm=ht_hz.htbm where gcnd=convert(varchar(4),getdate(),120) and tjjxmb.isdelete='0' and ht_hz.ztxx='YTKFQJCSS' and ht_hz.gcbm not in(select f_gcbh from gcxmzz) and ((select count(*) from Fhjh_zjzf where Fhjh_zjzf.htbm=ht_hz.htbm) +(select count(*) from FkjhW_zjzf where FkjhW_zjzf.htbm=ht_hz.htbm) =(select count(*) from tb_pingzheng where tb_pingzheng.kmdm=ht_hz.fzbm)) and ht_hz.gcbm not in (select ht_hz.gcbm from gcxmzz_ht,ht_hz where ht_hz.htbm=f_htbh) and ht_hz.fzbm in (select ht_hz.fzbm from ht_hz group by ht_hz.fzbm having count(*)=1) and ht_hz.htfzr='张少晨' order by ht_hz.id )as a
--谢谢大家!! --您看这个有没有办法优化一下 速度太慢了!! select '1' as iszf, (select count(*) from (select top 100 percent ht_hz.id from ht_hz left join tjjxmb on ht_hz.gcbm=xmbh left join fkjhbgzk on fkjhbgzk.htbm=ht_hz.htbm where gcnd=convert(varchar(4),getdate(),120) and tjjxmb.isdelete='0' and ht_hz.ztxx='YTKFQJCSS' and ((select count(*) from Fhjh_zjzf where Fhjh_zjzf.htbm=ht_hz.htbm)+(select count(*) from FkjhW_zjzf where FkjhW_zjzf.htbm=ht_hz.htbm) =(select count(*) from tb_pingzheng where tb_pingzheng.kmdm=ht_hz.fzbm)) and ht_hz.gcbm not in(select f_gcbh from gcxmzz) and ht_hz.gcbm not in (select ht_hz.gcbm from gcxmzz_ht,ht_hz where ht_hz.htbm=f_htbh) and ht_hz.fzbm in (select ht_hz.fzbm from ht_hz group by ht_hz.fzbm having count(*)=1) and ht_hz.htfzr='张少晨' order by ht_hz.id ) as ss where a.id>=id) as mxcode, a.* from (select top 100 percent htmc,yf=(select companyname from xt_wldwbm where yf=companyid), jhkgrq,isnull(ysje,0) as tzje, ybje=isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0),qbje=(ysje-isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0)), bcje=(yfjeing-isnull((select sum(sqje) from Fhjh_zjzf where ht_hz.htbm=Fhjh_zjzf.htbm),0)), cwbk=isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0), ht_hz.htbm,ht_hz.fzbm,ht_hz.htfzr,ht_hz.id ,'一'as bz,isfq from ht_hz left join tjjxmb on ht_hz.gcbm=xmbh left join fkjhbgzk on fkjhbgzk.htbm=ht_hz.htbm where gcnd=convert(varchar(4),getdate(),120) and tjjxmb.isdelete='0' and ht_hz.ztxx='YTKFQJCSS' and ht_hz.gcbm not in(select f_gcbh from gcxmzz) and ((select count(*) from Fhjh_zjzf where Fhjh_zjzf.htbm=ht_hz.htbm) +(select count(*) from FkjhW_zjzf where FkjhW_zjzf.htbm=ht_hz.htbm) =(select count(*) from tb_pingzheng where tb_pingzheng.kmdm=ht_hz.fzbm)) and ht_hz.gcbm not in (select ht_hz.gcbm from gcxmzz_ht,ht_hz where ht_hz.htbm=f_htbh) and ht_hz.fzbm in (select ht_hz.fzbm from ht_hz group by ht_hz.fzbm having count(*)=1) and ht_hz.htfzr='张少晨' order by ht_hz.id )as a
declare @t table([date] datetime,[Name] varchar(10))
insert @t select '2008-01-01','A'
insert @t select '2008-01-01','B'
insert @t select '2008-01-01','C'
insert @t select '2009-01-01','D'
insert @t select '2009-01-01','E'
insert @t select '2010-01-01','F'
select * ,排序=(select count(*)+1 from @t t1 where t1.[date]=t2.[date] and t1.[name]<t2.[name]) from @t t2/*
date Name 排序
------------------------------------------------------ ---------- -----------
2008-01-01 00:00:00.000 A 1
2008-01-01 00:00:00.000 B 2
2008-01-01 00:00:00.000 C 3
2009-01-01 00:00:00.000 D 1
2009-01-01 00:00:00.000 E 2
2010-01-01 00:00:00.000 F 1(影響 6 個資料列)*/
您看这个有没有办法优化一下 速度太慢了!!
select '1' as iszf, (select count(*) from (select top 100 percent ht_hz.id from ht_hz left join tjjxmb on ht_hz.gcbm=xmbh
left join fkjhbgzk on fkjhbgzk.htbm=ht_hz.htbm where gcnd=convert(varchar(4),getdate(),120) and tjjxmb.isdelete='0'
and ht_hz.ztxx='YTKFQJCSS' and
((select count(*) from Fhjh_zjzf where Fhjh_zjzf.htbm=ht_hz.htbm)+(select count(*)
from FkjhW_zjzf where FkjhW_zjzf.htbm=ht_hz.htbm) =(select count(*) from tb_pingzheng where tb_pingzheng.kmdm=ht_hz.fzbm))
and ht_hz.gcbm not in(select f_gcbh from gcxmzz) and ht_hz.gcbm not in (select ht_hz.gcbm from gcxmzz_ht,ht_hz where ht_hz.htbm=f_htbh)
and ht_hz.fzbm in (select ht_hz.fzbm from ht_hz group by ht_hz.fzbm having count(*)=1) and ht_hz.htfzr='张少晨'
order by ht_hz.id ) as ss where a.id>=id) as mxcode,a.* from (select top 100 percent
htmc,yf=(select companyname from xt_wldwbm where yf=companyid), jhkgrq,isnull(ysje,0) as tzje,
ybje=isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0),qbje=(ysje-isnull((select sum(j)
from tb_pingzheng where kmdm=ht_hz.fzbm),0)), bcje=(yfjeing-isnull((select sum(sqje) from Fhjh_zjzf
where ht_hz.htbm=Fhjh_zjzf.htbm),0)), cwbk=isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0),
ht_hz.htbm,ht_hz.fzbm,ht_hz.htfzr,ht_hz.id ,'一'as bz,isfq from ht_hz left join tjjxmb on ht_hz.gcbm=xmbh left join fkjhbgzk on
fkjhbgzk.htbm=ht_hz.htbm where gcnd=convert(varchar(4),getdate(),120) and tjjxmb.isdelete='0' and ht_hz.ztxx='YTKFQJCSS'
and ht_hz.gcbm not in(select f_gcbh from gcxmzz) and ((select count(*) from Fhjh_zjzf where Fhjh_zjzf.htbm=ht_hz.htbm)
+(select count(*) from FkjhW_zjzf where FkjhW_zjzf.htbm=ht_hz.htbm) =(select count(*) from tb_pingzheng where
tb_pingzheng.kmdm=ht_hz.fzbm)) and ht_hz.gcbm not in (select ht_hz.gcbm from gcxmzz_ht,ht_hz where ht_hz.htbm=f_htbh)
and ht_hz.fzbm in (select ht_hz.fzbm from ht_hz group by ht_hz.fzbm having count(*)=1) and ht_hz.htfzr='张少晨' order by ht_hz.id )as a
--谢谢大家!!
--您看这个有没有办法优化一下 速度太慢了!!
select '1' as iszf, (select count(*) from (select top 100 percent ht_hz.id from ht_hz left join tjjxmb on ht_hz.gcbm=xmbh
left join fkjhbgzk on fkjhbgzk.htbm=ht_hz.htbm where gcnd=convert(varchar(4),getdate(),120) and tjjxmb.isdelete='0'
and ht_hz.ztxx='YTKFQJCSS' and
((select count(*) from Fhjh_zjzf where Fhjh_zjzf.htbm=ht_hz.htbm)+(select count(*)
from FkjhW_zjzf where FkjhW_zjzf.htbm=ht_hz.htbm) =(select count(*) from tb_pingzheng where tb_pingzheng.kmdm=ht_hz.fzbm))
and ht_hz.gcbm not in(select f_gcbh from gcxmzz) and ht_hz.gcbm not in (select ht_hz.gcbm from gcxmzz_ht,ht_hz where ht_hz.htbm=f_htbh)
and ht_hz.fzbm in (select ht_hz.fzbm from ht_hz group by ht_hz.fzbm having count(*)=1) and ht_hz.htfzr='张少晨'
order by ht_hz.id ) as ss where a.id>=id) as mxcode, a.* from (select top 100 percent
htmc,yf=(select companyname from xt_wldwbm where yf=companyid), jhkgrq,isnull(ysje,0) as tzje,
ybje=isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0),qbje=(ysje-isnull((select sum(j)
from tb_pingzheng where kmdm=ht_hz.fzbm),0)), bcje=(yfjeing-isnull((select sum(sqje) from Fhjh_zjzf
where ht_hz.htbm=Fhjh_zjzf.htbm),0)), cwbk=isnull((select sum(j) from tb_pingzheng where kmdm=ht_hz.fzbm),0),
ht_hz.htbm,ht_hz.fzbm,ht_hz.htfzr,ht_hz.id ,'一'as bz,isfq from ht_hz left join tjjxmb on ht_hz.gcbm=xmbh left join fkjhbgzk on
fkjhbgzk.htbm=ht_hz.htbm where gcnd=convert(varchar(4),getdate(),120) and tjjxmb.isdelete='0' and ht_hz.ztxx='YTKFQJCSS'
and ht_hz.gcbm not in(select f_gcbh from gcxmzz) and ((select count(*) from Fhjh_zjzf where Fhjh_zjzf.htbm=ht_hz.htbm)
+(select count(*) from FkjhW_zjzf where FkjhW_zjzf.htbm=ht_hz.htbm) =(select count(*) from tb_pingzheng where
tb_pingzheng.kmdm=ht_hz.fzbm)) and ht_hz.gcbm not in (select ht_hz.gcbm from gcxmzz_ht,ht_hz where ht_hz.htbm=f_htbh)
and ht_hz.fzbm in (select ht_hz.fzbm from ht_hz group by ht_hz.fzbm having count(*)=1) and ht_hz.htfzr='张少晨' order by ht_hz.id )as a