问题描述
亲爱的所有人，
我们能否快速进行以下交叉加入查询。它 列表需要6秒
Can we make the below cross join query fast .It takes 6 seconds to list
SELECT
                                     
                            b.bid AS出价，
                            b.book_id AS book_id，
                       
                            proof_cell_name AS ProofCellName，
                            - b.book_s50_recd_dt as BookrecdDt
                              - BookrecdDt = CONVERT（CHAR（12），b.book_s50_recd_dt，113），NBSP;        
                            - BookrecdDt = CONVERT（VARCHAR（11），b.book_s50_recd_dt，100），
                              BookrecdDt = CONVERT（VARCHAR，b.book_s50_recd_dt，105）+""+ CONVERT（VARCHAR，b.book_s50_recd_dt，108），NBSP;       
                            - Chapter_S200Received_Date = CONVERT（VARCHAR（11），chap.chapter_s200_recd_dt，100），
                              Chapter_S200Received_Date = CONVERT（VARCHAR，chap.chapter_s200_recd_dt，105）+""+ CONVERT（VARCHAR，chap.chapter_s200_recd_dt，108），NBSP;       
                            - Chapter_S200Due_Date = CONVERT（VARCHAR（11），chap.chapter_s200_due_dt，100），
                              Chapter_S200Due_Date = CONVERT（VARCHAR，chap.chapter_s200_due_dt，105）+""+ CONVERT（VARCHAR，chap.chapter_s200_due_dt，108），NBSP;         &NBSP ;
                            DATEDIFF（dd，getdate（），chap.chapter_s200_due_dt）AS jsScheduleprogress，
                              - LeastStage =（从选择顶tblChapterStatusDetails 1 menu_serialno作为s其中出价= b.bid AND（s.menu_serialno
个= 18 AND s.menu_serialno< = 28或NBSP; s.menu_serialno IN（" 105'，'106'，'90'））和process_cycle ='1'顺序由process_in_dt ），
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; isnull（b.book_ms_pages，0）as Book_MS_pages，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; b.book_inputrecdType为book_inputrecdType，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; b.book_processType as book_processType，&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; editor_name =（SELECT user_name FROM EMS_DB.dbo.tblUser WHERE user_id = chap.chapter_s200_editor），
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; isnull（b.book_ce_level，chap.ce_level）as ce_level，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; DATEADD（day，2，b.book_s600_due_dt）AS book_s600_due_dt，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; - b.book_AuthorEdr_Proof_dt AS book_AuthorEdr_Proof_dt，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; - book_AuthorEdr_Proof_dt = CONVERT（CHAR（12），b.book_AuthorEdr_Proof_dt，113），NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; book_AuthorEdr_Proof_From_dt = CONVERT（VARCHAR（11），b.book_AuthorEdr_Proof_From_dt，100），&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; book_AuthorEdr_Proof_dt = CONVERT（VARCHAR（11），b.book_AuthorEdr_Proof_dt，100），&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;跟踪=（CASE&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;当b.book_hold =（ '1'）THEN '保留' &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;当b.book_fasttrack = '1' &NBSP; THEN '的FastTrack' &NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;当b.book_fasttrack = '0' &NBSP; THEN 'NormalTrack' &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; END），
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; loc.location_name AS book_location，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; scwStatus =（SELECT TOP 1（CASE WHEN c.scw_Status = 1 THEN '已完成' &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;
&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;当c.scw_Status = 0或c.scw_Status为NULL，则 '进度' &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; END）&NBSP; FROM&NBSP; tblchapter AS C，其中c.bid = b。 bid和b.book_shortcutwf = c.scw_Status ASC的'1'订单），
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; b.book_shortcutwf&NBSP;作为shortcutworkflow，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; bookSplInstructions =（情况&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;当b.book_Spl_Instructions = '1' THEN 'YES' &NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;当b.book_Spl_Instructions = '0'，则 'NO' &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;完），&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; ProjectManager =（SELECT USER_NAME FROM EMS_DB.dbo.tblUser其中b.book_Project_Manager COLLATE DATABASE_DEFAULT
= user_employeecode COLLATE DATABASE_DEFAULT），NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; b.book_Cust_Type as book_Cust_Type，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; book_s50_recd_dt AS S50_Recd_dt，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; book_pod_date AS Book_Pod_Date，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; isnull（DATEDIFF（dd，book_s50_recd_dt，GETDATE（）），0）AS TAT，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; pp.ProductionCategory AS ProductionCategory，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; book_PCN_number为BookPCNnumber，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; （从tblChapterManuscriptDetails中选择top 1 chapter_typeset_plat_form，其中bid = b.bid）AS TypesetPlatForm
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; FROM&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; tblBook AS b LEFT OUTER JOIN tblBookStatusUpload AS pp
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; ON b.bid = pp.bid LEFT OUTER JOIN tblLocation AS Loc
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; ON b.book_location = loc.location_id LEFT OUTER JOIN tblSegregatingCellName AS cel
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; ON cel.cell_id = b.book_proof_cell_id LEFT OUTER JOIN（SELECT&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;
&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; c.bid，&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; MAX（c.chapter_s200_recd_dt）AS chapter_s200_recd_dt，&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; MIN（c.chapter_s200_due_dt）AS chapter_s200_due_dt，&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; ;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; （SELECT MAX（ce_level）FROM tblchapter c）AS ce_level，
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; （SELECT TOP 1 chapter_s200_editor FROM tblchapter，其中chapter_s200_editor不为null AND bid = c.bid）AS
chapter_s200_editor
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; FROM&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; tblbook AS b INNER JOIN tblchapter AS c ON b.bid = c.bid
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; INNER JOIN tblChapterStatusDetails AS s
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; ON（c.bid = s.bid或c.bid<> s.bid）
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; WHERE&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.chapter_s200_recd_dt> ='2011-04-01'AND
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.chapter_s200_recd_dt不为空且
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.chapter_s200_due_dt不为空并且
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.chapter_s200_finish_dt是 空&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;和（s.node_id = '58' AND s.menu_serialno = '90' AND&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; s.process_out_dt IS NULL）&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP; group by
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.bid）AS chap
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; ON&NBSP;&NBSP;&NBSP; chap.bid = b.bid
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP; &NBSP;其中
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; b.bid = chap.bid AND
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; b.book_id不为空 b.bid订购
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;
SELECT
b.bid AS bid,
b.book_id AS book_id,
proof_cell_name AS ProofCellName,
--b.book_s50_recd_dt as BookrecdDt,
--BookrecdDt=CONVERT(CHAR(12),b.book_s50_recd_dt,113),
-- BookrecdDt=CONVERT(VARCHAR(11),b.book_s50_recd_dt,100),
BookrecdDt=CONVERT(VARCHAR,b.book_s50_recd_dt,105)+' '+CONVERT(VARCHAR,b.book_s50_recd_dt,108),
-- Chapter_S200Received_Date=CONVERT(VARCHAR(11),chap.chapter_s200_recd_dt,100),
Chapter_S200Received_Date=CONVERT(VARCHAR,chap.chapter_s200_recd_dt,105)+' '+CONVERT(VARCHAR,chap.chapter_s200_recd_dt,108),
-- Chapter_S200Due_Date=CONVERT(VARCHAR(11),chap.chapter_s200_due_dt,100),
Chapter_S200Due_Date=CONVERT(VARCHAR,chap.chapter_s200_due_dt,105)+' '+CONVERT(VARCHAR,chap.chapter_s200_due_dt,108),
DATEDIFF(dd,getdate(),chap.chapter_s200_due_dt)AS jsScheduleprogress,
--LeastStage=(select Top 1 menu_serialno from tblChapterStatusDetails as s where bid=b.bid AND ( s.menu_serialno >= 18 AND s.menu_serialno <= 28 OR s.menu_serialno IN ('105','106','90')) AND process_cycle='1' order by process_in_dt ),
isnull(b.book_ms_pages,0) as Book_MS_pages,
b.book_inputrecdType as book_inputrecdType,
b.book_processType as book_processType,
editor_name = (SELECT user_name FROM EMS_DB.dbo.tblUser WHERE user_id=chap.chapter_s200_editor),
isnull(b.book_ce_level,chap.ce_level) as ce_level,
DATEADD(day, 2, b.book_s600_due_dt) AS book_s600_due_dt,
--b.book_AuthorEdr_Proof_dt AS book_AuthorEdr_Proof_dt,
--book_AuthorEdr_Proof_dt=CONVERT(CHAR(12),b.book_AuthorEdr_Proof_dt,113),
book_AuthorEdr_Proof_From_dt=CONVERT(VARCHAR(11),b.book_AuthorEdr_Proof_From_dt,100),
book_AuthorEdr_Proof_dt=CONVERT(VARCHAR(11),b.book_AuthorEdr_Proof_dt,100),
Track =(CASE
WHEN b.book_hold=('1') THEN 'Hold'
WHEN b.book_fasttrack = '1' THEN 'FastTrack'
WHEN b.book_fasttrack = '0' THEN 'NormalTrack'
END ),
loc.location_name AS book_location,
scwStatus= (SELECT TOP 1 (CASE WHEN c.scw_Status=1 THEN 'Completed'
WHEN c.scw_Status=0 OR c.scw_Status IS NULL THEN 'Progress'
END) FROM tblchapter AS c WHERE c.bid=b.bid AND b.book_shortcutwf='1' order by c.scw_Status ASC),
b.book_shortcutwf as shortcutworkflow,
bookSplInstructions=(case
WHEN b.book_Spl_Instructions ='1' THEN 'YES'
WHEN b.book_Spl_Instructions ='0' THEN 'NO'
End ),
ProjectManager=(SELECT user_name FROM EMS_DB.dbo.tblUser where b.book_Project_Manager COLLATE DATABASE_DEFAULT =user_employeecode COLLATE DATABASE_DEFAULT),
b.book_Cust_Type As book_Cust_Type,
book_s50_recd_dt AS S50_Recd_dt,
book_pod_date AS Book_Pod_Date,
isnull(DATEDIFF (dd,book_s50_recd_dt,GETDATE()),0) AS TAT,
pp.ProductionCategory AS ProductionCategory ,
book_PCN_number as BookPCNnumber,
(select top 1 chapter_typeset_plat_form from tblChapterManuscriptDetails where bid=b.bid) AS TypesetPlatForm
FROM
tblBook AS b LEFT OUTER JOIN tblBookStatusUpload AS pp
ON b.bid=pp.bid LEFT OUTER JOIN tblLocation AS Loc
ON b.book_location=loc.location_id LEFT OUTER JOIN tblSegregatingCellName AS cel
ON cel.cell_id=b.book_proof_cell_id LEFT OUTER JOIN (SELECT
c.bid,
MAX(c.chapter_s200_recd_dt) AS chapter_s200_recd_dt ,
MIN(c.chapter_s200_due_dt) AS chapter_s200_due_dt,
(SELECT MAX (ce_level) FROM tblchapter c) AS ce_level,
(SELECT TOP 1 chapter_s200_editor FROM tblchapter where chapter_s200_editor is not null AND bid=c.bid) AS chapter_s200_editor
FROM
tblbook AS b INNER JOIN tblchapter AS c ON b.bid=c.bid
INNER JOIN tblChapterStatusDetails AS s
ON (c.bid=s.bid or c.bid<>s.bid)
WHERE
c.chapter_s200_recd_dt >='2011-04-01' AND
c.chapter_s200_recd_dt is not null AND
c.chapter_s200_due_dt is not null AND
c.chapter_s200_finish_dt is null
AND (s.node_id='58' AND s.menu_serialno='90' AND
s.process_out_dt IS NULL)
group by
c.bid) AS chap
ON chap.bid=b.bid
where
b.bid=chap.bid AND
b.book_id is not null order by b.bid
推荐答案
这篇关于调整交叉连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！