日期:2014-05-18  浏览次数:20489 次

跪求,泣求,高手救命,一SQL语句的优化,特急,解决就结贴
转换为存储过程也可以
===========================================================================
string   sqlCommand   =   "select   a.documentID,a.documentName,e.typeName,a.documentInfo,a.vesionId,a.vesionDate, "+
"a.documentSize,a.documentSend,a.documenttype,a.documenturl,a.VesionIDUser   from   "+
"FeiliOA_DocumentManage_DocumentInfo   a,FeiliOA_DocumentManage_TypeLookUser   b, "+
"FeiliOA_DocumentManage_TypeLookDept   c,FeiliOA_DocumentManage_TypeLookLv   d,FeiliOA_DocumentManage_TypeInfo   e   "+
"where   ((a.DocumentType=b.typeID   and   (b.lookuser= ' "+name+ " '   or   b.lookuser= 'ALL '))   or   "+
"(a.DocumentType=c.typeID   and   (c.lookdept= ' "+dept+ " '   or   c.lookdept= 'ALL '))   or   "+
"(a.DocumentType=d.typeID   and   (d.looklv= ' "+lv+ " '   or   d.looklv= 'ALL ')))   and   "+
"e.typeid=a.documenttype   and   a.documentStatic= 'a '   and   "+sqlStr+ "   group   by   a.documentID,a.documentName, "+
"e.typeName,a.documentInfo,a.vesionId,a.vesionDate,a.documentSize,a.documentSend,a.documenttype, "+
"a.documenturl,a.VesionIDUser ";

------解决方案--------------------
写的一点格式都没看都不好看怎么优化?
------解决方案--------------------
根据表的结构关系,可以使用LEFT OUTER JOIN 和INNER JOIN 。

------解决方案--------------------
这位兄弟,真的是说句实在话,您没有给数据表,最起码连结构和关系都不阐述一下,你真的让我们这些想帮您解决问题的人很为难~~
不知道基本情况何来解决呢?
------解决方案--------------------
呜呜,动态sql语句啊.实现思路,把结果集都放一临时表 #table 中,然后把动态参数加为选择条件
--建临时表
create #table (
documentID type(你自己把type都写一下吧.),
documentName,
typeName,
documentInfo,
VesionIDUser,
vesionId,
vesionDate,
documentSize,
documentSend,
documenttype,
documenturl
)
--将结果集插入临时表
insert into #table
SELECT a.documentID,a.documentName,e.typeName,a.documentInfo,a.VesionIDUser,a.vesionId,a.vesionDate,a.documentSize,a.documentSend,a.documenttype,a.documenturl
FROM
FeiliOA_DocumentManage_DocumentInfo a INNER JOIN #FeiliOA_DocumentManage_TypeLookUser b
ON a.DocumentType=b.typeID AND b.lookuser=@user AND a.documentStatic= 'a ' INNER JOIN FeiliOA_DocumentManage_TypeInfo e
ON e.typeid=a.documenttype
UNION
SELECT a.documentID,a.documentName,e.typeName,a.documentInfo,a.VesionIDUser,a.vesionId,a.vesionDate,a.documentSize,a.documentSend,a.documenttype,a.documenturl
FROM
FeiliOA_DocumentManage_DocumentInfo a INNER JOIN
#FeiliOA_DocumentManage_TypeLookDept c
ON a.DocumentType=c.typeID and c.lookdept=@dept AND a.documentStatic= 'a ' INNER JOIN FeiliOA_DocumentManage_TypeInfo e
ON e.typeid=a.documenttype
UNION
SELECT a.documentID,a.documentName,e.typeName,a.documentInfo,a.VesionIDUser,a.vesionId,a.vesionDate,a.documentSize,a.documentSend,a.documenttype,a.documenturl
FROM
FeiliOA_DocumentManage_DocumentInfo a INNER JOIN
#FeiliOA_DocumentManage_TypeLookLv d
ON a.DocumentType=d.typeID and d.looklv=@lv AND a.documentStatic= 'a ' INNER JOIN FeiliOA_DocumentManage_TypeInfo e
ON e.typeid=a.documenttype
--
定义一变量,准备动态执行,
declare @s varchar(200)
set @s= 'select * from #table where '
set @s= 'select * from #table where documentname like ' '%aaa+% ' ' and documenttype= ' 'T009 &