如何实现现有查询的连接 [英] how to implement joins to the existing querys

问题描述

我很新加入..请建议我如何使用联接进行以下查询。

 < span class =code-keyword> SELECT  Line_No，isnull（Tool_id，' ' ）Tool_id，QUAD_ID，Tool_grade，EARLY_WARNING_FLAG，部门，STATION，
  CASE   WHEN  DATEDIFF（MI） ，ISNULL（NOK_TIME，TIMESTAMP2），GETDATE（））>  1440  那么 TIMESTAMP2 
  ELSE  ISNULL（NOK_TIME，TIMESTAMP2） END   AS  NOK_TIME，Ack_Time，
 DATEDIFF（MI，TIMESTAMP2， GETDATE（）） AS  DOWNDURATION  FROM  TX_STATION_STATUS  NOLOCK  
  WHERE  LINE_NO  LIKE  '  7％' 
  AND  STATION  LIKE  '  S％' 
  AND  
（TOOL_ID  IS   NOT   NULL   AND  TOOL_ID  IN （ SELECT  TESTER_ID  FROM  TX_CR_QTY_BUILD  WITH （ NOLOCK ） WHERE （（LINE_NO  LIKE  '  7％'） AND （LINE_NO  LIKE  ' ％' ）） AND  TESTER_ID = TOOL_ID  AND  BUILD_HOUR +  1 > = getdate（）））
  OR （TOOL_ID  IS   NULL   AND  STATION  IN （ SELECT  STATION  FROM  TX_CR_QTY_ BUILD  WITH （ NOLOCK ） WHERE （（ LINE_NO  LIKE  '  7％'）< span class =code-keyword> AND （LINE_NO  LIKE  ' ）） AND  STATION = STATION  AND  BUILD_HOUR +  1 > = getdate（）））
  AND  STATION + ISNULL（TOOL_ID， ）+ CONVERT（ VARCHAR （ 30 ），Timestamp2， 126 ） IN （
  SELECT  STATION + ISNULL（TOOL_ID，' '）+ CONVERT（ VARCHAR （ 30 ），MAX（Timestamp2），  126 ）
  FROM  TX_STATION_STATUS  WITH （ NOLOCK ）
  WHERE  LINE_NO  LIKE  '  7％' 
  GROUP   BY  STATION，TOOL_ID 
）

在此先感谢，

kp。

解决方案

我在您的查询中做了一些更改，以显示如何使用join，因为您的目标是我还不清楚。

看看这是不是你想要的......





< pre lang =sql> 选择 TSS.Line_No，isnull（TSS.Tool_id，' '）Tool_id，TSS.QUAD_ID，TSS.Tool_grade，TSS.EARLY_WARNING_FLAG
，TSS.Department，TSS.STATION，
< span class =code-keyword> CASE WHEN DATEDIFF（MI，ISNULL（TSS.NOK_TIME，TSS.TIMESTAMP2），GETDATE（））> 1440 那么 TSS.TIMESTAMP2
ELSE ISNULL（TSS.NOK_TIME，TSS.TIMESTAMP2） END AS NOK_TIME，TSS.Ack_Time，
DATEDIFF（MI，TSS.TIMESTAMP2，GETDATE（）） AS DOWNDURATION
来自 TX_STATION_STATUS TSS（ NOLOCK ）
join TX_CR_QTY_BUILD TCQ（ nolock ）
on TSS.TOOL_ID = TCQ.TESTER_ID
或 TSS.Station = TCQ.Station
WHERE TSS.LINE_NO LIKE ' 7％' AND TSS.STATION LIKE ' S％'
和 TCQ.BUILD_HOUR + 1 > = getdate（）

访问以下有关如何使用联接的链接..



http：// blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ [ ^ ]



http://blogs.msdn.com/b/spike/archive/2008/07/22/the-simplification-of-join-simple-example-of-how-t-sql -join-work.aspx [ ^ ]



http://blogs.msdn.com/b/blogdoezequiel/archive/2012/01/15 /t-sql-misconceptions-join-on-vs-where.aspx#.UfYpwqxZKW5 [ ^ ]



http://msdn.microsoft.com/en- us / library / ms191517％28v = sql.105％29.aspx [ ^ ]



SQL连接 [ ^ ]

检查它会给你的想法试试吧

   TName < span class =code-keyword> as （
  SELECT  Line_No，isnull（Tool_id， ' '）Tool_id，QUAD_ID，Tool_grade，EARLY_WARNING_FLAG，部门，STATION，
  CASE   WHEN  DATEDIFF（MI，ISNULL（NOK_TIME，TIMESTAMP2），GETDATE（））>  1440  那么 TIMESTAMP2 
  ELSE  ISNULL（NOK_TIME，TIMESTAMP2） END   AS  NOK_TIME，Ack_Time，
 DATEDIFF（MI，TIMESTAMP2， GETDATE（）） AS  DOWNDURATION  FROM  TX_STATION_STATUS  NOLOCK  
  WHERE  LINE_NO  LIKE  '  7％' 
  AND  STATION  LIKE  '  S％' 
  AND  
（TOOL_ID  IS   NOT   NULL   AND  TOOL_ID  IN （ SELECT  TESTER_ID  FROM  TX_CR_QTY_BUILD  WITH （ NOLOCK ） WHERE （（LINE_NO  LIKE  '  7％'） AND （LINE_NO  LIKE  ' ％'）） AND  TESTER_ID = TOOL_ID  AND  BUILD_HOUR +  1 > = getdate（）））
 或（TOOL_ID  IS   NULL   AND  STATION  IN （ SELECT  STATION  FROM  TX_CR_QTY_BU ILD  WITH （ NOLOCK ） WHERE （（ LINE_NO  LIKE  '  7％'）< span class =code-keyword> AND （LINE_NO  LIKE  ' ）） AND  STATION = STATION  AND  BUILD_HOUR +  1 > = getdate（）））
  AND  STATION + ISNULL（TOOL_ID， ）+ CONVERT（ VARCHAR （ 30 ），Timestamp2， 126 ） IN （
  SELECT  STATION + ISNULL（TOOL_ID，' '）+ CONVERT（ VARCHAR （ 30 ），MAX（Timestamp2），  126 ）
  FROM  TX_STATION_STATUS  WITH （ NOLOCK ）
  WHERE  LINE_NO  LIKE  '  7％' 
  GROUP   BY  STATION，TOOL_ID）
）
 选择 col1，col2 ，TableName.Line_No 来自 TName 内部 加入 TableName 
   
 TableName.col1 = TName.Tool_id 

最好的问候

M.Mitwalli

Hi,I am very new to joins.. please suggest me how to use joins for the following query.

SELECT Line_No, isnull(Tool_id, ' ') Tool_id, QUAD_ID, Tool_grade, EARLY_WARNING_FLAG, Department, STATION,
CASE WHEN DATEDIFF(MI,ISNULL(NOK_TIME, TIMESTAMP2),GETDATE()) > 1440 THEN TIMESTAMP2 
ELSE ISNULL(NOK_TIME, TIMESTAMP2) END AS NOK_TIME, Ack_Time,
DATEDIFF(MI,TIMESTAMP2,GETDATE()) AS DOWNDURATION FROM TX_STATION_STATUS NOLOCK
WHERE LINE_NO LIKE '7%'
AND STATION LIKE'S%'
AND 
(TOOL_ID IS NOT NULL AND TOOL_ID IN (SELECT TESTER_ID FROM TX_CR_QTY_BUILD WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '%')) AND TESTER_ID = TOOL_ID AND BUILD_HOUR + 1 >= getdate())) 
OR (TOOL_ID IS NULL AND STATION IN (SELECT STATION FROM TX_CR_QTY_BUILD WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '')) AND STATION = STATION AND BUILD_HOUR + 1 >= getdate()) ) 
AND STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), Timestamp2, 126) IN ( 
SELECT STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), MAX(Timestamp2), 126)
FROM TX_STATION_STATUS WITH (NOLOCK)
WHERE LINE_NO LIKE '7%'
GROUP BY STATION, TOOL_ID
)

Thanks in advance,
kp.

解决方案

I have made some changes in your query to show how to use join, because your goal is still not clear to me.
See if this is what you are asking for..

select TSS.Line_No, isnull(TSS.Tool_id, ' ') Tool_id, TSS.QUAD_ID, TSS.Tool_grade, TSS.EARLY_WARNING_FLAG
, TSS.Department, TSS.STATION,
CASE WHEN DATEDIFF(MI,ISNULL(TSS.NOK_TIME, TSS.TIMESTAMP2),GETDATE()) > 1440 THEN TSS.TIMESTAMP2
ELSE ISNULL(TSS.NOK_TIME, TSS.TIMESTAMP2) END AS NOK_TIME, TSS.Ack_Time,
DATEDIFF(MI,TSS.TIMESTAMP2,GETDATE()) AS DOWNDURATION
from TX_STATION_STATUS TSS (NOLOCK)
join TX_CR_QTY_BUILD TCQ (nolock)
on TSS.TOOL_ID =TCQ.TESTER_ID
or TSS.Station =TCQ.Station
WHERE TSS.LINE_NO LIKE '7%' AND TSS.STATION LIKE'S%'
and TCQ.BUILD_HOUR + 1 >= getdate()

visit following links for How to use joins..

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/[^]

http://blogs.msdn.com/b/spike/archive/2008/07/22/the-simplification-of-join-simple-example-of-how-t-sql-join-work.aspx[^]

http://blogs.msdn.com/b/blogdoezequiel/archive/2012/01/15/t-sql-misconceptions-join-on-vs-where.aspx#.UfYpwqxZKW5[^]

http://msdn.microsoft.com/en-us/library/ms191517%28v=sql.105%29.aspx[^]

SQL Joins[^]

Hi ,
Check this it will give you Idea try it

with TName as (
  SELECT Line_No, isnull(Tool_id, ' ') Tool_id, QUAD_ID, Tool_grade, EARLY_WARNING_FLAG, Department, STATION,
CASE WHEN DATEDIFF(MI,ISNULL(NOK_TIME, TIMESTAMP2),GETDATE()) > 1440 THEN TIMESTAMP2
ELSE ISNULL(NOK_TIME, TIMESTAMP2) END AS NOK_TIME, Ack_Time,
DATEDIFF(MI,TIMESTAMP2,GETDATE()) AS DOWNDURATION FROM TX_STATION_STATUS NOLOCK
WHERE LINE_NO LIKE '7%'
AND STATION LIKE'S%'
AND
(TOOL_ID IS NOT NULL AND TOOL_ID IN (SELECT TESTER_ID FROM TX_CR_QTY_BUILD  WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '%')) AND TESTER_ID = TOOL_ID AND BUILD_HOUR + 1 >= getdate()))
OR (TOOL_ID IS NULL AND STATION IN (SELECT STATION FROM TX_CR_QTY_BUILD WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '')) AND STATION = STATION AND BUILD_HOUR + 1 >= getdate()) )
AND STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), Timestamp2, 126) IN (
SELECT STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), MAX(Timestamp2), 126)
      FROM TX_STATION_STATUS WITH (NOLOCK)
      WHERE LINE_NO LIKE '7%'
      GROUP BY STATION, TOOL_ID)
)
select col1,col2 ,TableName.Line_No from TName  inner join TableName
on
TableName.col1 = TName.Tool_id

Best Regards
M.Mitwalli

这篇关于如何实现现有查询的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！