SELECT count(distinct document.documentid)
from Document document, FlowLog flowlog
where flowlog.recordid = document.documentid
and document.processuserid = '8a8a804810346f2a01103472a7f3000c'
and document.processdeptid = 'ff8080810e874ce1010e875d25c60049'
and document.eformid <> -1
and flowlog.eformid = 1001
and flowlog.status <> 7
and flowlog.status <> 8
and flowlog.gonogo <> '-1'
GROUP BY document.documentid
HAVING COUNT(flowlog.flowlogid) = 1
表中主键建有索引,其它索引没有建
执行计划是
|--Filter(WHERE:([Expr1002]=1))
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([document].[DOCUMENTID])=([document].[DOCUMENTID]), RESIDUAL:([document].[DOCUMENTID]=[document].[DOCUMENTID]))
|--Compute Scalar(DEFINE:([document].[DOCUMENTID]=[document].[DOCUMENTID]))
| |--Compute Scalar(DEFINE:([Expr1003]=If (0 IS NULL) then 0 else 1))
| |--Sort(DISTINCT ORDER BY:([document].[DOCUMENTID] ASC))
| |--Table Spool
| |--Nested Loops(Inner Join, OUTER REFERENCES:([flowlog].[RECORDID]))
| |--Clustered Index Scan(OBJECT:([sloa].[dbo].[FLOWLOG].[PK_FLOWLOG] AS [flowlog]), WHERE:((([flowlog].[EFORMID]=1001 AND [flowlog].[STATUS]<>7) AND [flowlog].[STATUS]<>8) AND [flowlog].[GONOGO]<>'-1'))
| |--Clustered Index Seek(OBJECT:([sloa].[dbo].[DOCUMENT].[SQL060310142731400] AS [document]), SEEK:([document].[DOCUMENTID]=[flowlog].[RECORDID]), WHERE:(([document].[EFORMID]<>-1 AND [document].[PROCESSUSERID]='8a8a804
|--Compute Scalar(DEFINE:([document].[DOCUMENTID]=[document].[DOCUMENTID]))
|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1008])))
|--Stream Aggregate(GROUP BY:([document].[DOCUMENTID]) DEFINE:([Expr1008]=Count(*)))
|--Sort(ORDER BY:([document].[DOCUMENTID] ASC))
|--Table Spool
我该怎么做 求助阿
from Document document, FlowLog flowlog
where flowlog.recordid = document.documentid
and document.processuserid = '8a8a804810346f2a01103472a7f3000c'
and document.processdeptid = 'ff8080810e874ce1010e875d25c60049'
and document.eformid <> -1
and flowlog.eformid = 1001
and flowlog.status <> 7
and flowlog.status <> 8
and flowlog.gonogo <> '-1'
GROUP BY document.documentid
HAVING COUNT(flowlog.flowlogid) = 1
表中主键建有索引,其它索引没有建
执行计划是
|--Filter(WHERE:([Expr1002]=1))
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([document].[DOCUMENTID])=([document].[DOCUMENTID]), RESIDUAL:([document].[DOCUMENTID]=[document].[DOCUMENTID]))
|--Compute Scalar(DEFINE:([document].[DOCUMENTID]=[document].[DOCUMENTID]))
| |--Compute Scalar(DEFINE:([Expr1003]=If (0 IS NULL) then 0 else 1))
| |--Sort(DISTINCT ORDER BY:([document].[DOCUMENTID] ASC))
| |--Table Spool
| |--Nested Loops(Inner Join, OUTER REFERENCES:([flowlog].[RECORDID]))
| |--Clustered Index Scan(OBJECT:([sloa].[dbo].[FLOWLOG].[PK_FLOWLOG] AS [flowlog]), WHERE:((([flowlog].[EFORMID]=1001 AND [flowlog].[STATUS]<>7) AND [flowlog].[STATUS]<>8) AND [flowlog].[GONOGO]<>'-1'))
| |--Clustered Index Seek(OBJECT:([sloa].[dbo].[DOCUMENT].[SQL060310142731400] AS [document]), SEEK:([document].[DOCUMENTID]=[flowlog].[RECORDID]), WHERE:(([document].[EFORMID]<>-1 AND [document].[PROCESSUSERID]='8a8a804
|--Compute Scalar(DEFINE:([document].[DOCUMENTID]=[document].[DOCUMENTID]))
|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1008])))
|--Stream Aggregate(GROUP BY:([document].[DOCUMENTID]) DEFINE:([Expr1008]=Count(*)))
|--Sort(ORDER BY:([document].[DOCUMENTID] ASC))
|--Table Spool
我该怎么做 求助阿
好像也只有两个<>能合并
into #t1
from FlowLog flowlog
where flowlog.eformid = 1001
and flowlog.status <> 7
and flowlog.status <> 8
and flowlog.gonogo <> '-1'delete #t1
from #t1 a,#t1 b
where a.recordid = b.recordid
and a.flowlogid = b.flowlogid
SELECT count(distinct document.documentid)
from Document document, select flowlog
where flowlog.recordid = document.documentid
and document.processuserid = '8a8a804810346f2a01103472a7f3000c'
and document.processdeptid = 'ff8080810e874ce1010e875d25c60049'
and document.eformid <> -1
运行时:查询分析器提示:表 '#t1' 不明确。
该怎么做呢??to:marco08(天道酬勤)
哪里改为exists呢???
2、搂主的语句出来的肯定都是1。
3、搂主想要求什么?是不是SELECT count(document.documentid) FROM
(
SELECT document.documentid
from Document document, FlowLog flowlog
where flowlog.recordid = document.documentid
and document.processuserid = '8a8a804810346f2a01103472a7f3000c'
and document.processdeptid = 'ff8080810e874ce1010e875d25c60049'
and document.eformid <> -1
and flowlog.eformid = 1001
and flowlog.status <> 7
and flowlog.status <> 8
and flowlog.gonogo <> '-1'
GROUP BY document.documentid
HAVING COUNT(flowlog.flowlogid) = 1
) M
select top 200 document.documentid,document.createtime from(
SELECT distinct document.documentid,document.createtime
from Document document(index=index1), FlowLog flowlog
where flowlog.recordid = document.documentid
and document.processuserid =
'ff8080810e874ce1010e875de060004f'
and (document.eformid > -1
OR document.eformid < -1)
and flowlog.eformid = 1001
and (flowlog.status < 7
OR flowlog.status > 7)
and (flowlog.status < 8
OR flowlog.status > 8)
and (flowlog.gonogo < '-1'
OR flowlog.gonogo > '-1')
GROUP BY document.documentid, document.createtime
HAVING COUNT(flowlog.flowlogid) = 1 )document
order by document.createtime desc
请大家帮我看看,如果这条语句改为存储过程的话,怎么改,哪里需要设置参数
use test
if exists(select name from sysobjects where name='a_a' and type='p')
drop procedure a_a
go
use test
go
create proc a_a
@i varchar(100),
@j int
as
select top 200 document.documentid,document.createtime from(
SELECT distinct document.documentid,document.createtime
from Document document(index=index1), FlowLog flowlog
where flowlog.recordid = document.documentid
and document.processuserid = @i
and (document.eformid > -1
OR document.eformid < -1)
and flowlog.eformid = @j
and (flowlog.status < 7
OR flowlog.status > 7)
and (flowlog.status < 8
OR flowlog.status > 8)
and (flowlog.gonogo < '-1'
OR flowlog.gonogo > '-1')
GROUP BY document.documentid, document.createtime
HAVING COUNT(flowlog.flowlogid) = 1 )document
order by document.createtime desc
go
exec a_a 'ff8080810e874ce1010e875de060004f',1001 不过,没有写存储过程时用了16毫秒,可是加入存储过程后,单独执行
exec a_a 'ff8080810e874ce1010e875de060004f',1001 却用了200毫秒,这是怎么回事啊