如何在sql server 2008中使用内连接和子查询？

问题描述：

如何在sql server 2008中同时使用内连接和子查询??

来自OP的消息

How to use Inner join and sub query simultaneously in sql server 2008??

From OP's message

INSERT INTO hr_SalaryBreakupAmountHistory 
SELECT a.SbhSlgID, a.SbhJLevelID, a.SbhSbkID, 2015, 7, a.SbhConfirmed, a.SbhNonConfirmed FROM (SELECT * 
      FROM hr_SalaryBreakupAmountHistory 
      WHERE SbhSlgID = '01' AND SbhYear = 2014 AND SbhMonth = 7) a 
LEFT JOIN
     (SELECT * 
      FROM hr_SalaryBreakupAmountHistory 
      WHERE SbhSlgID = '01' AND SbhYear = 2015 AND SbhMonth = 7) b 
ON a.SbhSlgID = b.SbhSlgID AND a.SbhJLevelID = b.SbhJLevelID AND a.SbhSbkID = b.SbhSbkID
WHERE b.SbhSlgID IS NULL 
ORDER BY a.SbhSlgID, a.SbhJLevelID, a.SbhSbkID

答

如上所述，不知道你的q的确切细节uestion是不可能给出一个确切的答案。

然而，你的INSERT语句似乎过于复杂，所以这可能是绊脚石。据我所见，你不需要内联视图，所以查询可以简单地是

As said without knowing the exact details of your question it's impossible to give an exact answer.

However, your INSERT statement seems overly complicated so perhaps this is stumbling. As far as I can see, you don't need the inline view so the query could simply be

INSERT INTO hr_SalaryBreakupAmountHistory
SELECT a.SbhSlgID, a.SbhJLevelID, a.SbhSbkID, 2015, 7, a.SbhConfirmed, a.SbhNonConfirmed
FROM hr_SalaryBreakupAmountHistory a
     LEFT JOIN hr_SalaryBreakupAmountHistory b ON a.SbhSlgID = b.SbhSlgID 
                                               AND a.SbhJLevelID = b.SbhJLevelID 
                                               AND a.SbhSbkID = b.SbhSbkID
WHERE a.SbhSlgID = '01' 
AND   a.SbhYear = 2014 
AND   a.SbhMonth = 7
AND   b.SbhSlgID IS NULL
AND   b.SbhSlgID = '01' 
AND   b.SbhYear = 2015 
AND   b.SbhMonth = 7
ORDER BY a.SbhSlgID, a.SbhJLevelID, a.SbhSbkID

如果你需要使用INNER JOIN，那么用INNER JOIN替换LEFT JOIN

作为附注，列出要插入数据的列是一个好习惯。语句的开头应该看起来像

And if you need to use INNER JOIN then replace the LEFT JOIN with INNER JOIN

As a side note it is a good practice to list the column where the data is to be inserted so the beginning of the statement should look like

INSERT INTO hr_SalaryBreakupAmountHistory
   (Col1, Col2, Col3, Col4, Col5, Col6, Col7)
SELECT ...

其中col1..7将替换为真实的列名。

Where col1..7 would be replaced with the real column names.

如何在sql server 2008中使用内连接和子查询？

相关推荐