简单的 SQL 检查父行是否有子行
我显示一个包含父数据的网格,并且如果存在相关的子行,则需要显示图标。 我的数据库位于 SQL Server 2008。让我简化一下,我有以下两个表 -
订单(PK:ID)
文件(PK:文件ID,FK:订单ID)
订单可以有零个或多个与其相关的文件。 File
表有一列 OrderID
,其中保存对 Order
的 FK 引用。现在,我正在显示一个列出所有 Orders
的网格,并且我想显示一个图标图像,指示该 Order
是否有任何子行(文件)。
这是我尝试过的一种棘手方法,但不确定它的效率/可扩展性如何 -
SELECT DISTINCT o.ID, o.OrderNum, ...
,(CASE f.ID/f.ID WHEN 1 THEN 1 ELSE 0 END) as FilesExist
...
FROM Order AS o LEFT OUTER JOIN dbo.FileHeader as f ON f.OrderID = o.ID
CASE
语句似乎可以按照要求完美地工作。如果存在一个或多个文件,它将返回 1,否则返回 0。如果存在多个文件行,那么它将尝试重复我添加了 DISTINCT 的 Order
行,并且 I'我不选择f.ID
,而是选择f.ID/f.ID
,其中1(存在)和0代表null(不存在)。我了解到 JOIN 比内联 SELECT COUNT(*) 语句更好。
我已经测试过并且它有效,但我需要专家的意见,并且需要确保这是最好的方法。这是一个非常简单的示例,但我的 SELECT 语句很复杂,因为有很多查找,并且获取成本很高,因此我需要确保其可扩展。
谢谢。
编辑#1: 总而言之 - 要么是带有 COUNT(*)
SELECT c.ClaimNo,(SELECT COUNT(*) FROM dbo.FileHeader AS f WHERE f.ClaimID = c.ID ) AS FilesHExist
FROM dbo.Claim AS c
的内部 SELECT或我提到的LEFT OUTER JOIN 方法。
SELECT DISTINCT c.ClaimNo, (CASE fh.ID / fh.ID WHEN 1 THEN 1 ELSE 0 END) AS FilesHExist
FROM dbo.Claim AS c LEFT OUTER JOIN dbo.FileHeader AS fh ON fh.ClaimID = c.ID
附上两张查询执行计划的图像。请推荐哪一个更好。
I show a grid with parent data and need to show icon if it has a relevant child row(s) exist. My DB is in SQL Server 2008. Let me simplify, I've the following two tables -
Order (PK : ID)
File (PK: FileID, FK: OrderID)
An Order
can have zero or more files related to it. The File
table has a column OrderID
which holds FK reference to Order
. Now, I'm displaying a grid which lists all the Orders
and I want to display an icon image indicating if that Order
has any child rows (files) or not.
Here's a tricky way I've experimented but not sure how efficient/scalable it is -
SELECT DISTINCT o.ID, o.OrderNum, ...
,(CASE f.ID/f.ID WHEN 1 THEN 1 ELSE 0 END) as FilesExist
...
FROM Order AS o LEFT OUTER JOIN dbo.FileHeader as f ON f.OrderID = o.ID
The CASE
statement seems to work perfectly as required. It'll return 1 if one or more file(s) exists, else 0. If multiple file rows exist then it will try to repeat the Order
row for which I've added the DISTINCT and I'm not selecting f.ID
but f.ID/f.ID
which will be 1 (it exists) and 0 for null (doesn't exist). I've learned that JOINs are better than inline SELECT COUNT(*)
statements.
I've tested and it works but I need expert opinion and need to make sure that this is the best way of doing it. This is a very simple example but my SELECT
statement is complex as there are many lookups and its going to be a costly fetch so I need to make sure its scalable.
Thank you.
EDIT #1:
To conclude - either it is going to be internal SELECT with a COUNT(*)
SELECT c.ClaimNo,(SELECT COUNT(*) FROM dbo.FileHeader AS f WHERE f.ClaimID = c.ID ) AS FilesHExist
FROM dbo.Claim AS c
or the LEFT OUTER JOIN approach that I've mentioned.
SELECT DISTINCT c.ClaimNo, (CASE fh.ID / fh.ID WHEN 1 THEN 1 ELSE 0 END) AS FilesHExist
FROM dbo.Claim AS c LEFT OUTER JOIN dbo.FileHeader AS fh ON fh.ClaimID = c.ID
Attached two images of query execution plan. Kindly suggest which one is better.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
对于选择几行,这应该是最快的:(
我引用了保留字“订单”。)
对于包含 large 的
SELECT
部分dbo.FileHeader
的性能应该会更好:首先对
OrderID
进行分组,然后左连接应该会更便宜。最后不需要DISTINCT
。从不使用:
它会导致除以零的异常。将其替换为检查
NULL
,如上面所示。For selecting few rows, this should be fastest:
(I quoted the reserved word "Order".)
For a
SELECT
that includes large portions ofdbo.FileHeader
this should perform better:It should be cheaper to group
OrderID
first and then left join. No need forDISTINCT
at the end.Never use:
It opens you up to division by zero exception. Replace it with a check for
NULL
like demonstrated above.如果您可以使用子级数,则使用
否则使用
不过,建议:
每当您想知道数据库中实际发生的情况时,请比较不同版本查询的计划 - 一切充其量都是受过教育的猜猜......该计划向您展示了您真正要做的事情(例如,它考虑了预期返回的行数以及在回答您的问题时我们不关心的其他内容)。
假设您使用的是 SQL Server,这在 SSMS 中可用...对于 Oracle 也是如此 - 它在 SQL Developer 中可用...大多数数据库都有这样的选项。
If you can work with the number of childern use
Otherwise use
A recommendation though:
Whenever you want to know what really happens in the DB compare the PLANs for the different version of the query - everything is at best an educated guess... the PLAN shows you what your really will do (which takes into for example how many rows it expects to be returned and other stuff we don't anything about when answering your question on SO).
Assuming you are using SQL Server this is available in the SSMS... same goes for Oracle - there it is available in SQL Developer... most DBs have such an option.
试试这个
Try this