求一条SQL语句的优化语句!急急急!!!
select * from ( select M.*,RegistState= case when X.RegistState='2' then '2' else '0' end,PublishState1 = case when Y.PublishState = 'CheckTime1' then 'CheckTime1' else 'Unpublish' end from ( SELECT RIGHT(dbo.PrjTask.Duties, LEN(dbo.PrjTask.Duties) - CHARINDEX(':', dbo.PrjTask.Duties)) AS DutyNames, dbo.PrjTask.Id, dbo.PrjTask.PrjId, dbo.PrjBasicInfo.Code, dbo.PrjBasicInfo.Name AS PrjName, dbo.PrjTask.FullId, dbo.PrjTask.ParentId, dbo.PrjTask.Type, dbo.PrjTask.TaskNo, dbo.PrjTask.Name, dbo.PrjTask.EnumValue, dbo.PrjTask.PreviousTask, dbo.PrjTask.CoNode, dbo.PrjTask.CoSubTask, dbo.PrjTask.CoParentTask, dbo.PrjTask.ExecuteObject, dbo.PrjTask.ExecuteType, dbo.PrjTask.ExecuteForm, dbo.PrjTask.State, dbo.PrjTask.Grade, dbo.PrjTask.Duties, dbo.PrjTask.PlanStartDate, dbo.PrjTask.PlanEndDate, dbo.PrjTask.DispenseDate, dbo.PrjTask.PlanDays, dbo.PrjTask.LimitType, dbo.PrjTask.LimitDate, dbo.PrjTask.Difficulty, dbo.PrjTask.ProductNumber, dbo.PrjTask.StandardDays, dbo.PrjTask.HasFlow, dbo.PrjTask.FlowDefId,dbo.PrjTask.FlowDefName, dbo.PrjTask.Memo, dbo.PrjTask.FactStartDate, dbo.PrjTask.FactEndDate, dbo.PrjTask.FactDays, dbo.PrjTask.CompleteRate, dbo.PrjTask.ResultType, dbo.PrjTask.ResultIds, dbo.PrjTask.CreatorId, dbo.PrjTask.CreatorName, dbo.PrjTask.CreateDate, dbo.PrjTask.SortIndex, dbo.PrjTask.IsMilestone, dbo.PrjTask.RelateValue AS DeptID, dbo.PrjTask.OxText, dbo.PrjTask.DesignPrjNo, A.SendOutPeopleName, A.SendOutTime AS SendOutTime1, A.CheckRen1, A.PtSendTime AS SendOutTime, A.PtState,A.CheckState,A.SendOutState,A.PublishState, A.CheckTime1, A.CheckRen2, A.CheckTime2, A.SendEndTime, A.CheckRen3, A.CheckTime3, A.CheckRen4, A.CheckTime4, C.Name AS DeptName, E.StartTime AS AuditDate, E.Result, F.DesignMoney, F.YuGuChanZi, F.DingEManageMoney, F.ProjectQualityMoney, F.ProjectJianLiMoney, F.BuildManageMoney, F.ProjectMoney, F.TouZhiMoney,F.DesignMoneyGS, F.GouKm, F.XianKm, F.DescriptOtherText, F.SheJiRenGong, F.GuanDaoL, F.KongDaoL, F.TiaoKm, F.DianLanL, F.XinKm, F.JiaoHuanXiang,
dbo.PrjBasicInfo.OperateDate, F.DuiKm, F.GuanLanL, F.GTiaoKm, dbo.PrjBasicInfo.PrjControlType, dbo.PrjBasicInfo.PrjType, dbo.PrjBasicInfo.Phase, dbo.PrjBasicInfo.DelegateDeptName, dbo.PrjBasicInfo.YunXinShan, dbo.PrjUserAndRole.UserName, dbo.PrjUserAndRole.RoleName, dbo.PrjBasicInfo.ProjectNo, dbo.PrjBasicInfo.ManagerName, dbo.PrjBasicInfo.DesignChargerName, dbo.PrjBasicInfo.YSyuanyin, dbo.PrjBasicInfo.PrjIssueDate,dbo.PrjBasicInfo.MajorDeptName,dbo.PrjBasicInfo.PlanEndTimeNew,dbo.PrjBasicInfo.State as PrjState,dbo.PrjBasicInfo.Id as RealPrjId FROM dbo.PrjTask Left JOIN dbo.PrjBasicInfo ON dbo.PrjBasicInfo.Id = dbo.PrjTask.PrjId Left JOIN (SELECT * FROM PoPrintTaskPool WHERE Id IN (SELECT min(id) FROM PoPrintTaskPool GROUP BY AggregateProductNo)) A ON dbo.PrjTask.EnumValue = A.AggregateProductNo Left JOIN SHPT_BeAdmin.dbo.OgProperty C ON C.Id = dbo.PrjTask.RelateValue Left JOIN (SELECT PrjTaskId, Result,startTime FROM PrjAuditFlow where id in( select max(id) from
PrjAuditFlow group by PrjTaskId) ) E ON E.PrjTaskId = dbo.PrjTask.Id Left JOIN (SELECT * FROM dbo.PrjBudgetData WHERE id IN (SELECT MAX(id) FROM PrjBudgetData GROUP BY DocVolNo)) F ON F.DocVolNo = dbo.PrjTask.Id AND F.TagNo = 'new' Left JOIN dbo.PrjUserAndRole ON dbo.PrjUserAndRole.NodeId = dbo.PrjTask.Id AND dbo.PrjUserAndRole.RoleId = 'PROP10AI' WHERE (dbo.PrjTask.Type IN ('DesignTask', 'CoDesignTask', 'Project')) And (EnumValue like '%%' or dbo.PrjBasicInfo.Code like '%%' ) ) M left join (select distinct TOP 100000 Id,PrjId,RegistState from v_PrjPublishListTable where RegistState = '2') X on M.PrjId = X.PrjId and M.Id = X.Id left join (select distinct Id,PrjId,PublishState from v_PrjPublishListTable where PublishState = 'CheckTime1') Y on M.PrjId = Y.PrjId and M.Id = Y.Id ) SS where RegistState like '%%' and PublishState1 like '%%' order by OperateDate Desc,PrjName Asc
--------------------编程问答-------------------- 我靠，比我强。 --------------------编程问答-------------------- 可以用恐怖来形容。 --------------------编程问答-------------------- 建视图吧，Sql也太长了 --------------------编程问答-------------------- 神仙能帮你优化 --------------------编程问答-------------------- select * from PrjAuditFlow --------------------编程问答-------------------- 试图也很长啦!
牛人们!
帮忙!!帮忙!! --------------------编程问答-------------------- 如果我没猜错的话 应该是一个交叉表的查询 而且这个sql语句不是你写出来的 是用其它sql查询拼接起来的,然后用动态执行sql来查数据的..... 我以前也做过 当表项太多的话 这个sql语句拼接会非常的长 会超出ntext的8000字长设置, 所以这样做不好, 还是从你一开始那个查询语句着手改动吧. --------------------编程问答-------------------- 天 这么长了 ...... 都没信心看完 --------------------编程问答-------------------- 这样只能用视图了，不然太麻烦了，生成个派生表试试。 --------------------编程问答-------------------- 这是组长给我的
要我优化!
晕啦!
要用一个组合查询语句。。 --------------------编程问答-------------------- ......每天回帖即可获得10分可用分！小技巧 --------------------编程问答-------------------- 你们组长太...了 --------------------编程问答-------------------- MSSQL吗？
查询分析器里看下执行计划，可以粗略的知道索引是不是合理，以及查询的瓶颈在哪里。当然执行计划也有计划错误的时候，所以也不能迷信。
不建议SQL语句写得过于复杂。必要时用表变量等手段先把大表中有用的数据提取出来再进一步处理。
--------------------编程问答-------------------- 如今公司把女人当男人，把男人当死人 --------------------编程问答--------------------
. --------------------编程问答-------------------- 你们祖长是ＳＢ，ＳＱＬ本来不长，非要写这么多字段，写这么多字段也罢，非要都加占dbo. ,加上dbo.也罢，还要用表名来写不用别名
真当成写作文了，字数多 分数高啊
建议 中间有几个左联 什么 min(id) max(id)的 左联，写在外面一层 --------------------编程问答-------------------- --------------------编程问答-------------------- 我靠，进来还以为是篇英文文章 --------------------编程问答-------------------- 唉,这个东西,整理一下看就清楚了,本来没那么复杂的东西,被你随便一粘贴,就是一易做图,神仙也看不明白了,
闲着无聊帮你理一下
select * from
(
select M.*,RegistState= case when X.RegistState='2' then '2' else '0' end,
PublishState1 = case when Y.PublishState = 'CheckTime1' then 'CheckTime1' else 'Unpublish' end
from ( SELECT RIGHT(dbo.PrjTask.Duties, LEN(dbo.PrjTask.Duties) - CHARINDEX(':', dbo.PrjTask.Duties)) AS DutyNames,
dbo.PrjTask.Id,
dbo.PrjTask.PrjId,
dbo.PrjBasicInfo.Code,
dbo.PrjBasicInfo.Name AS PrjName,
dbo.PrjTask.FullId,
dbo.PrjTask.ParentId,
dbo.PrjTask.Type,
dbo.PrjTask.TaskNo,
dbo.PrjTask.Name,
dbo.PrjTask.EnumValue,
dbo.PrjTask.PreviousTask,
dbo.PrjTask.CoNode,
dbo.PrjTask.CoSubTask,
dbo.PrjTask.CoParentTask,
dbo.PrjTask.ExecuteObject,
dbo.PrjTask.ExecuteType,
dbo.PrjTask.ExecuteForm,
dbo.PrjTask.State,
dbo.PrjTask.Grade,
dbo.PrjTask.Duties,
dbo.PrjTask.PlanStartDate,
dbo.PrjTask.PlanEndDate,
dbo.PrjTask.DispenseDate,
dbo.PrjTask.PlanDays,
dbo.PrjTask.LimitType,
dbo.PrjTask.LimitDate,
dbo.PrjTask.Difficulty,
dbo.PrjTask.ProductNumber,
dbo.PrjTask.StandardDays,
dbo.PrjTask.HasFlow,
dbo.PrjTask.FlowDefId,
dbo.PrjTask.FlowDefName,
dbo.PrjTask.Memo,
dbo.PrjTask.FactStartDate,
dbo.PrjTask.FactEndDate,
dbo.PrjTask.FactDays,
dbo.PrjTask.CompleteRate,
dbo.PrjTask.ResultType,
dbo.PrjTask.ResultIds,
dbo.PrjTask.CreatorId,
dbo.PrjTask.CreatorName,
dbo.PrjTask.CreateDate,
dbo.PrjTask.SortIndex,
dbo.PrjTask.IsMilestone,
dbo.PrjTask.RelateValue AS DeptID,
dbo.PrjTask.OxText,
dbo.PrjTask.DesignPrjNo,
A.SendOutPeopleName,
A.SendOutTime AS SendOutTime1,
A.CheckRen1,
A.PtSendTime AS SendOutTime,
A.PtState,
A.CheckState,
A.SendOutState,
A.PublishState,
A.CheckTime1,
A.CheckRen2,
A.CheckTime2,
A.SendEndTime,
A.CheckRen3,
A.CheckTime3,
A.CheckRen4,
A.CheckTime4,
C.Name AS DeptName,
E.StartTime AS AuditDate,
E.Result,
F.DesignMoney,
F.YuGuChanZi,
F.DingEManageMoney,
F.ProjectQualityMoney,
F.ProjectJianLiMoney,
F.BuildManageMoney,
F.ProjectMoney,
F.TouZhiMoney,
F.DesignMoneyGS,
F.GouKm,
F.XianKm,
F.DescriptOtherText,
F.SheJiRenGong,
F.GuanDaoL,
F.KongDaoL,
F.TiaoKm,
F.DianLanL,
F.XinKm,
F.JiaoHuanXiang,
dbo.PrjBasicInfo.OperateDate,
F.DuiKm,
F.GuanLanL,
F.GTiaoKm,
dbo.PrjBasicInfo.PrjControlType,
dbo.PrjBasicInfo.PrjType,
dbo.PrjBasicInfo.Phase,
dbo.PrjBasicInfo.DelegateDeptName,
dbo.PrjBasicInfo.YunXinShan,
dbo.PrjUserAndRole.UserName,
dbo.PrjUserAndRole.RoleName,
dbo.PrjBasicInfo.ProjectNo,
dbo.PrjBasicInfo.ManagerName,
dbo.PrjBasicInfo.DesignChargerName,
dbo.PrjBasicInfo.YSyuanyin,
dbo.PrjBasicInfo.PrjIssueDate,
dbo.PrjBasicInfo.MajorDeptName,
dbo.PrjBasicInfo.PlanEndTimeNew,
dbo.PrjBasicInfo.State as PrjState,
dbo.PrjBasicInfo.Id as RealPrjId
FROM dbo.PrjTask
Left JOIN dbo.PrjBasicInfo ON dbo.PrjBasicInfo.Id = dbo.PrjTask.PrjId
Left JOIN (SELECT * FROM PoPrintTaskPool WHERE Id IN (
SELECT min(id) FROM PoPrintTaskPool GROUP BY AggregateProductNo)) A ON dbo.PrjTask.EnumValue = A.AggregateProductNo
Left JOIN SHPT_BeAdmin.dbo.OgProperty C ON C.Id = dbo.PrjTask.RelateValue
Left JOIN (SELECT PrjTaskId, Result,startTime FROM PrjAuditFlow where id in(
select max(id) from PrjAuditFlow group by PrjTaskId) ) E ON E.PrjTaskId = dbo.PrjTask.Id
Left JOIN (SELECT * FROM dbo.PrjBudgetData WHERE id IN (SELECT MAX(id) FROM PrjBudgetData GROUP BY DocVolNo)) F ON F.DocVolNo = dbo.PrjTask.Id AND F.TagNo = 'new'
Left JOIN dbo.PrjUserAndRole ON dbo.PrjUserAndRole.NodeId = dbo.PrjTask.Id AND dbo.PrjUserAndRole.RoleId = 'PROP10AI' WHERE (dbo.PrjTask.Type IN ('DesignTask', 'CoDesignTask', 'Project')) And (EnumValue like '%%' or dbo.PrjBasicInfo.Code like '%%' ) ) M
left join (select distinct TOP 100000 Id,PrjId,RegistState from v_PrjPublishListTable where RegistState = '2') X on M.PrjId = X.PrjId and M.Id = X.Id
left join (select distinct Id,PrjId,PublishState from v_PrjPublishListTable where PublishState = 'CheckTime1') Y on M.PrjId = Y.PrjId and M.Id = Y.Id
)
SS where RegistState like '%%' and PublishState1 like '%%'
order by OperateDate Desc,PrjName Asc --------------------编程问答--------------------
--------------------编程问答-------------------- 收藏 --------------------编程问答-------------------- 我本来是打算在这里装下B的, 进来才知道, 装B果然被雷劈。 --------------------编程问答--------------------
select * from
( select M.*,RegistState= case when X.RegistState='2' then '2' else '0' end,
PublishState1 = case when Y.PublishState = 'CheckTime1' then 'CheckTime1'
else 'Unpublish' end from ( SELECT RIGHT(T.Duties, LEN(T.Duties) - CHARINDEX(':', T.Duties)) AS DutyNames,
T.Id, T.PrjId, T2.Code, T2.Name AS PrjName, T.FullId, T.ParentId, T.Type,
T.TaskNo, T.Name, T.EnumValue, T.PreviousTask, T.CoNode, T.CoSubTask, T.CoParentTask, T.ExecuteObject,
T.ExecuteType, T.ExecuteForm, T.State, T.Grade, T.Duties, T.PlanStartDate, T.PlanEndDate, T.DispenseDate,
T.PlanDays, T.LimitType, T.LimitDate, T.Difficulty, T.ProductNumber, T.StandardDays, T.HasFlow, T.FlowDefId,
T.FlowDefName, T.Memo, T.FactStartDate, T.FactEndDate, T.FactDays, T.CompleteRate, T.ResultType, T.ResultIds,
T.CreatorId, T.CreatorName, T.CreateDate, T.SortIndex, T.IsMilestone, T.RelateValue AS DeptID, T.OxText,
T.DesignPrjNo,
A.SendOutPeopleName, A.SendOutTime AS SendOutTime1, A.CheckRen1, A.PtSendTime AS SendOutTime,
A.PtState,A.CheckState,A.SendOutState,A.PublishState, A.CheckTime1, A.CheckRen2, A.CheckTime2,
A.SendEndTime, A.CheckRen3, A.CheckTime3, A.CheckRen4, A.CheckTime4,
C.Name AS DeptName,
E.StartTime AS AuditDate, E.Result,
F.DesignMoney, F.YuGuChanZi, F.DingEManageMoney, F.ProjectQualityMoney, F.ProjectJianLiMoney, F.BuildManageMoney, F.ProjectMoney,
F.TouZhiMoney,F.DesignMoneyGS, F.GouKm, F.XianKm, F.DescriptOtherText, F.SheJiRenGong, F.GuanDaoL, F.KongDaoL, F.TiaoKm,
F.DianLanL, F.XinKm, F.JiaoHuanXiang,
T2.OperateDate, F.DuiKm, F.GuanLanL, F.GTiaoKm, T2.PrjControlType, T2.PrjType, T2.Phase, T2.DelegateDeptName, T2.YunXinShan,
T4.UserName, T4.RoleName, T2.ProjectNo, T2.ManagerName, T2.DesignChargerName, T2.YSyuanyin, T2.PrjIssueDate,T2.MajorDeptName,
T2.PlanEndTimeNew,T2.State as PrjState,T2.Id as RealPrjId
FROM dbo.PrjTask T
Left JOIN dbo.PrjBasicInfo T2 ON T2.Id = T.PrjId
Left JOIN (SELECT * FROM T3
WHERE Id IN (SELECT min(id) FROM PoPrintTaskPool T3
GROUP BY AggregateProductNo)) A ON T.EnumValue = A.AggregateProductNo
Left JOIN SHPT_BeAdmin.dbo.OgProperty C ON C.Id = T.RelateValue
Left JOIN (SELECT PrjTaskId, Result,startTime FROM PrjAuditFlow where id in( select max(id) from PrjAuditFlow group by PrjTaskId) ) E ON E.PrjTaskId = T.Id
Left JOIN (SELECT * FROM T5 T5 WHERE id IN (SELECT MAX(id) FROM PrjBudgetData GROUP BY DocVolNo)) F ON F.DocVolNo = T.Id AND F.TagNo = 'new'
Left JOIN dbo.PrjUserAndRole T4 ON T4.NodeId = T.Id AND T4.RoleId = 'PROP10AI'
WHERE (T.Type IN ('DesignTask', 'CoDesignTask', 'Project')) And (EnumValue like '%%' or T2.Code like '%%' ) ) M
left join (select distinct TOP 100000 Id,PrjId,RegistState from v_PrjPublishListTable where RegistState = '2') X on M.PrjId = X.PrjId and M.Id = X.Id
left join (select distinct Id,PrjId,PublishState from v_PrjPublishListTable where PublishState = 'CheckTime1') Y on M.PrjId = Y.PrjId and M.Id = Y.Id ) SS where RegistState like '%%' and PublishState1 like '%%' order by OperateDate Desc,PrjName Asc
--------------------编程问答-------------------- 先优化你的业务逻辑再优化你的语句, --------------------编程问答-------------------- 叫你组长回家吃饭 --------------------编程问答-------------------- 高手呀，让你组长吃喝点酒，最好是白的 --------------------编程问答-------------------- 有点恐怖 , 这表设计的 让你害怕 --------------------编程问答-------------------- 太长了，你为什么不适用动态创建SQL语句啊？ --------------------编程问答-------------------- 好长好乱啊 --------------------编程问答-------------------- ................ --------------------编程问答-------------------- 好长，好大，你们组长好易做图……………… --------------------编程问答-------------------- 太长了，看不下去 --------------------编程问答-------------------- 25%的结贴率 --------------------编程问答-------------------- 我差点疯了 居然能写出这么长的sql 难道不能一步步的实现功能吗 那样检查起来也方便 这样出点问题还要走中间慢慢查 --------------------编程问答-------------------- 楼猪你搞笑呢吧，这么长的篇幅亏你能看得下去。
把要重复运算的改为视图
把in 改为inner join
WHERE id IN (SELECT MAX(id) FROM PrjBudgetData GROUP BY DocVolNo))最好改为inner join
And (EnumValue like '%%' or T2.Code like '%%' ) 这个貌似可以去掉
left join (select distinct TOP 100000 小表对大表可以考虑not exists
我觉得一定要明确任务，分步实现。 --------------------编程问答-------------------- 老大，我不准备学sqlserver了 --------------------编程问答-------------------- 不要灰心啦！继续加油！
--------------------编程问答-------------------- 都是M表的内容,格式整理一下,这种拼接SQL的优化很麻烦,感觉这是个ERP最后拼接生成的报表SQL
--------------------编程问答-------------------- 高手啊 --------------------编程问答-------------------- 疯子 --------------------编程问答-------------------- 强......... --------------------编程问答-------------------- 牛人啊 回复那哥们 真牛 --------------------编程问答-------------------- 才20分！懒得看你的东西。蛋疼 --------------------编程问答-------------------- 易做图啊。。。。。。。。
select * from
(
select
M.*,RegistState= case when X.RegistState='2' then '2' else '0' end,
PublishState1 = case when Y.PublishState = 'CheckTime1' then 'CheckTime1' else 'Unpublish' end
from
/* M start */
(
SELECT
RIGHT(dbo.PrjTask.Duties, LEN(dbo.PrjTask.Duties) - CHARINDEX(':', dbo.PrjTask.Duties)) AS DutyNames,
dbo.PrjTask.Id, dbo.PrjTask.PrjId, dbo.PrjBasicInfo.Code, dbo.PrjBasicInfo.Name AS PrjName, dbo.PrjTask.FullId,
dbo.PrjTask.ParentId, dbo.PrjTask.Type, dbo.PrjTask.TaskNo, dbo.PrjTask.Name,
dbo.PrjTask.EnumValue, dbo.PrjTask.PreviousTask, dbo.PrjTask.CoNode, dbo.PrjTask.CoSubTask, dbo.PrjTask.CoParentTask,
dbo.PrjTask.ExecuteObject, dbo.PrjTask.ExecuteType, dbo.PrjTask.ExecuteForm, dbo.PrjTask.State, dbo.PrjTask.Grade,
dbo.PrjTask.Duties, dbo.PrjTask.PlanStartDate, dbo.PrjTask.PlanEndDate, dbo.PrjTask.DispenseDate, dbo.PrjTask.PlanDays,
dbo.PrjTask.LimitType, dbo.PrjTask.LimitDate, dbo.PrjTask.Difficulty, dbo.PrjTask.ProductNumber, dbo.PrjTask.StandardDays,
dbo.PrjTask.HasFlow, dbo.PrjTask.FlowDefId, dbo.PrjTask.FlowDefName, dbo.PrjTask.Memo,
dbo.PrjTask.FactStartDate, dbo.PrjTask.FactEndDate, dbo.PrjTask.FactDays, dbo.PrjTask.CompleteRate,
dbo.PrjTask.ResultType, dbo.PrjTask.ResultIds, dbo.PrjTask.CreatorId, dbo.PrjTask.CreatorName,
dbo.PrjTask.CreateDate, dbo.PrjTask.SortIndex, dbo.PrjTask.IsMilestone, dbo.PrjTask.RelateValue AS DeptID,
dbo.PrjTask.OxText, dbo.PrjTask.DesignPrjNo, A.SendOutPeopleName, A.SendOutTime AS SendOutTime1, A.CheckRen1,
A.PtSendTime AS SendOutTime, A.PtState,A.CheckState,A.SendOutState,A.PublishState, A.CheckTime1, A.CheckRen2,
A.CheckTime2, A.SendEndTime, A.CheckRen3, A.CheckTime3, A.CheckRen4, A.CheckTime4, C.Name AS DeptName,
E.StartTime AS AuditDate, E.Result, F.DesignMoney, F.YuGuChanZi, F.DingEManageMoney, F.ProjectQualityMoney,
F.ProjectJianLiMoney, F.BuildManageMoney, F.ProjectMoney, F.TouZhiMoney,F.DesignMoneyGS, F.GouKm, F.XianKm,
F.DescriptOtherText, F.SheJiRenGong, F.GuanDaoL, F.KongDaoL, F.TiaoKm, F.DianLanL, F.XinKm, F.JiaoHuanXiang,
dbo.PrjBasicInfo.OperateDate, F.DuiKm, F.GuanLanL, F.GTiaoKm, dbo.PrjBasicInfo.PrjControlType,
dbo.PrjBasicInfo.PrjType, dbo.PrjBasicInfo.Phase, dbo.PrjBasicInfo.DelegateDeptName,
dbo.PrjBasicInfo.YunXinShan, dbo.PrjUserAndRole.UserName, dbo.PrjUserAndRole.RoleName,
dbo.PrjBasicInfo.ProjectNo, dbo.PrjBasicInfo.ManagerName, dbo.PrjBasicInfo.DesignChargerName,
dbo.PrjBasicInfo.YSyuanyin, dbo.PrjBasicInfo.PrjIssueDate,dbo.PrjBasicInfo.MajorDeptName,
dbo.PrjBasicInfo.PlanEndTimeNew,dbo.PrjBasicInfo.State as PrjState,dbo.PrjBasicInfo.Id as RealPrjId
FROM
dbo.PrjTask
Left JOIN dbo.PrjBasicInfo ON dbo.PrjBasicInfo.Id = dbo.PrjTask.PrjId
Left JOIN
(
SELECT *
FROM PoPrintTaskPool
WHERE Id IN
(
SELECT min(id)
FROM PoPrintTaskPool
GROUP BY AggregateProductNo)
) A
ON dbo.PrjTask.EnumValue = A.AggregateProductNo
Left JOIN SHPT_BeAdmin.dbo.OgProperty C
ON C.Id = dbo.PrjTask.RelateValue
Left JOIN
(
SELECT PrjTaskId, Result,startTime
FROM PrjAuditFlow
where id in
(
select max(id)
from PrjAuditFlow group by PrjTaskId
)
) E
ON E.PrjTaskId = dbo.PrjTask.Id
Left JOIN
(
SELECT *
FROM dbo.PrjBudgetData
WHERE id IN
(
SELECT MAX(id) FROM PrjBudgetData
GROUP BY DocVolNo
)
) F
ON F.DocVolNo = dbo.PrjTask.Id AND F.TagNo = 'new'
Left JOIN dbo.PrjUserAndRole
ON dbo.PrjUserAndRole.NodeId = dbo.PrjTask.Id AND dbo.PrjUserAndRole.RoleId = 'PROP10AI'
WHERE (dbo.PrjTask.Type IN ('DesignTask', 'CoDesignTask', 'Project'))
And (EnumValue like '%%' or dbo.PrjBasicInfo.Code like '%%' )
) M
/* M end*/
left join
(
select distinct TOP 100000 Id,PrjId,RegistState from v_PrjPublishListTable where RegistState = '2'
) X
on M.PrjId = X.PrjId and M.Id = X.Id
left join
(
select distinct Id,PrjId,PublishState from v_PrjPublishListTable where PublishState = 'CheckTime1'
) Y
on M.PrjId = Y.PrjId and M.Id = Y.Id
) SS
where
RegistState like '%%' and PublishState1 like '%%'
order by OperateDate Desc,PrjName Asc
这是什么 --------------------编程问答-------------------- 本来想试试，瞄了下，还是走为上策 --------------------编程问答--------------------
..... --------------------编程问答-------------------- 神啊！给我一板砖吧！ --------------------编程问答-------------------- 这个还好... ...
我之前弄过一个哥们的代码, 嵌套了 13层的 SELECT
SELECT xx ,xxxx,xxxx
from (
select XXXX FROM (
.... ...
足足 13层,里面还用到了变量表... ...
然后其中有Bug,我要去修复...
我看了下然后就火了,怒了
修个P.. 直接分析业务逻辑,重写了... ...
补充：.NET技术 , C#
