我有 2 個相關的表（父子表），如果添加了任何附件，則子表中將有一個條目。我們正在使用存盤程序獲取資料。我想為每條記錄回傳真或假。我嘗試了以下方法，
CREATE PROCEDURE [dbo].[testname]
(
@Date DATE
)
AS
BEGIN
SELECT TOP 10000
s.column1
, s.column2
, s.column3
, s.column4
, s.column5, ....,
, c.ParentID
FROM
ParentTable p WITH(NOLOCK)
LEFT JOIN SomeOtherTable s WITH(NOLOCK) ON
p.ID = s.ID
LEFT JOIN ChildTable c WITH(NOLOCK) ON
p.ID = c.ParentID
WHERE
p.CreatedDate >= @Date
ORDER BY
p.CreatedDate DESC;
END
這個作業正常，但僅回傳在子表中有值的記錄的值。此外，父子之間的關系是一對多的。此查詢還為子表中的每條記錄從父表回傳相同的記錄，并且它只回傳在兩個表中都有條目的那些記錄，情況不應該如此。
任何人都可以幫助我如何做到這一點？我正在使用 SQL Server。
uj5u.com熱心網友回復：
只需exists在case expression.
Select <columns from parent table>,
case when exists (select * from ChildTable c where c.ParentId=p.Id) then 1 else 0 end as HasChild
from Parent t
我還打算建議使用更緊湊的表單，iif()然后注意到您已標記 SQL Server 2008...所以我不會！
uj5u.com熱心網友回復：
您的查詢顯然是為了說明問題，而不是實際查詢。因此，此答案還使用人為的資料來說明您想要的聲音。
似乎您正試圖從ParentTabletrue 或 false 回傳記錄以指示子表中是否有記錄。
- 您不能
LEFT JOIN在子表中然后在WHERE子句中使用子表。這樣做會將其更改為INNER JOIN. - 如果您有一對多關系并將兩個表連接在一起，您將看到父記錄與子記錄一樣多。
有幾種方法可以查看是否有子記錄。這只是兩個解決方案。您應該查看您選擇使用的任何查詢計劃，以便確保它針對您的資料集進行了優化。
create table #parent (id int, value varchar(10))
create table #child (id int, parent_id int, value varchar(10))
create clustered index pk_parent on #parent (id)
create clustered index pk_child on #child (id)
create index idx_fk_child_parent on #child (parent_id)
insert into #parent values (1, 'one'), (2, 'two'), (3, 'three')
insert into #child values (1, 1, 'red'), (2, 3, 'blue'), (3, 3, 'green')
/* correlated sub-query solution */
select p.*, has_child = cast(1 as bit)
from #parent p
where exists (select 1 from #child c where p.id = c.parent_id)
union all
select p.*, has_child = cast(0 as bit)
from #parent p
where not exists (select 1 from #child c where p.id = c.parent_id)
order by p.id
/* nested sub-query solution */
select p.*, has_child = isnull(c.has_child, 0)
from #parent p
left join (
select distinct parent_id, has_child = cast(1 as bit)
from #child
) c on p.id = c.parent_id
order by p.id
drop table #parent, #child
這兩個都回傳以下內容：
id value has_child
----------- ---------- ---------
1 one 1
2 two 0
3 three 1
The correlated sub-query is the most obvious and simply sets has_child to true or false based on exists or not exists. The sub-query solution is less obvious. If a record exists, has_child will be 1. If a record does not exists, has_child will be NULL.
The sub-query performs better than the correlated sub-query because it only has to hit the tables 1 time each, instead of two times. But I don't know how many records you actually have in your tables or how they are indexed. So look at the query plan for both and see where you can optimize.
I added indexes that would approximate the indexes in a real database. The cost of the correlated sub-query is 4 times higher than the sub-query solution. Other solutions likely exist that would perform even better.
The nested sub-query solution has very good stats, the CPU is especially low in the inner query.
Hopefully this helps and answers your questions.
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/422319.html
標籤：