SQL数据库问题

发布于 2024-09-29 14:10:19 字数 937 浏览 3 评论 0原文

关于 SQL 视图的问题。尝试从两个表开发一个视图。这两个表具有相同的主键，除了第一个表具有所有主键，第二个表具有一些主键，但不是全部。当我内部加入它们时，我得到一个记录集，但它不完整，因为第二个表中没有所有记录。在我看来，有没有一种方法可以编写逻辑，说明如果键不在表 #2 中，则插入一个零，以便整个记录集显示在视图中？我想显示视图中的所有记录，即使没有任何内部连接。

我的例子如下：

SELECT     dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, dbo.Baan_view1b.[User], 
                      dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage, MAX(dbo.notes.percent_developed) AS Expr1
FROM         dbo.Baan_view1b INNER JOIN
                      dbo.notes ON dbo.Baan_view1b.Number = dbo.notes.note_number
GROUP BY dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, dbo.Baan_view1b.[User], 
                      dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage
HAVING      (NOT (dbo.Baan_view1b.stage LIKE 'Closed'))

原文

Question about SQL View. Trying to develop a view from two tables. The two tables have same Primary Keys, execpt the 1st table has all of them, the 2nd has some, but not all. When I INNER Join them, I get a recordset but its not complete, because the 2nd table doesnt have all the records in it. Is there a way in my view to write logic stating that if the key isnt in there int he table #2 to insert a zero so the entire record set is shown in the view? I wan tto show ALL the records in the view even if theres nothing to inner join.

My example below:

SELECT     dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, dbo.Baan_view1b.[User], 
                      dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage, MAX(dbo.notes.percent_developed) AS Expr1
FROM         dbo.Baan_view1b INNER JOIN
                      dbo.notes ON dbo.Baan_view1b.Number = dbo.notes.note_number
GROUP BY dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, dbo.Baan_view1b.[User], 
                      dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage
HAVING      (NOT (dbo.Baan_view1b.stage LIKE 'Closed'))

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜是你 2024-10-06 14:10:19

您正在寻找的是Left Join（左外连接）而不是内连接

SELECT dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, 
        dbo.Baan_view1b.[User], dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage, 
        MAX(dbo.notes.percent_developed) AS Expr1 
FROM dbo.Baan_view1b 
    LEFT OUTER JOIN dbo.notes 
        ON dbo.Baan_view1b.Number = dbo.notes.note_number 
WHERE NOT dbo.Baan_view1b.stage LIKE 'Closed'       
GROUP BY dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, 
        dbo.Baan_view1b.[User], dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage

此外，将HAVING子句更改为WHERE子句可以使查询更加高效。

what you are looking for is the Left Join (left outer join) and not the inner join

SELECT dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, 
        dbo.Baan_view1b.[User], dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage, 
        MAX(dbo.notes.percent_developed) AS Expr1 
FROM dbo.Baan_view1b 
    LEFT OUTER JOIN dbo.notes 
        ON dbo.Baan_view1b.Number = dbo.notes.note_number 
WHERE NOT dbo.Baan_view1b.stage LIKE 'Closed'       
GROUP BY dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, 
        dbo.Baan_view1b.[User], dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage

Also, changing the HAVING Clause to a WHERE clause makes the query more efficient.

回复收藏 0 原文

可是我不能没有你 2024-10-06 14:10:19

是的，你可以这样做。假设 baan_view1b 拥有所有记录，注释只有一些，更改

 FROM dbo.Baan_view1b INNER JOIN dbo.notes

为

 FROM dbo.Baan_view1b LEFT OUTER JOIN dbo.notes

INNER JOIN（或只是普通 JOIN）告诉数据库引擎从 Baan_view1b 获取记录，将它们与注释中的记录匹配，并在输出中包含一行每对匹配的记录。正如您所看到的，它排除了 Baan_view1b 中在注释表中没有匹配项的记录。

相反，LEFT OUTER JOIN 告诉引擎从 Bann_view1b 获取所有记录（因为它位于 JOIN 关键字的左侧）。然后，它会尽可能匹配笔记中的记录。但是，无论是否可以匹配，都可以保证左侧表中的每一行在输出中都有一行。

如果像往常一样，您要求两个表中的列值，则 JOIN 右侧表中的列在缺失的行中将具有 NULL 值。

Yes, you can do this. Assuming that baan_view1b has all the records and notes has only some, change

 FROM dbo.Baan_view1b INNER JOIN dbo.notes

to say

 FROM dbo.Baan_view1b LEFT OUTER JOIN dbo.notes

INNER JOIN (or just plain JOIN) tells the database engine to take records from Baan_view1b, match them up with records in notes, and include a row in the output for every pair of records that match. As you have seen, it excludes records from Baan_view1b that don't have matches in the notes table.

LEFT OUTER JOIN instead tells the engine to take ALL the records from Bann_view1b (because it's on the left side of the JOIN keywords). Then, it will match up records from notes wherever it can. However, you are guaranteed a row in the output for every row in the left-hand table regardless of whether it can be matched.

If, as is usual, you asked for column values from both tables, the columns from the table on the right-hand side of the JOIN will have NULL values in the missing rows.

回复收藏 0 原文