获取 T-SQL 中子查询的当前记录 [英] Get current record for a subquery in T-SQL

问题描述

我试图从表Table1"中选择所有记录，但我想要一个名为HasException"的新列，其中包含0"或1".如果Table2"中与当前 Id 匹配的行数等于 0，则HasException"必须为0"，否则返回 1.

I'm trying to select all records from a table "Table1" but I want a new column called "HasException" that contains a "0" or a "1". "HasException" must be "0" if the count of row matching the current Id from "Table2" is equal to 0, else it returns 1.

这是我到目前为止所做的，但它不起作用:

Here's what I've done so far, but it doesn't works:

SELECT *,
CONVERT(bit, (CASE WHEN (SELECT count(Id) FROM Table2 WHERE Table1.Id=Table2.Id) = 0 THEN 0 ELSE 1 END)) AS HasException
FROM Table1

推荐答案

在比较两个值之前，您想加入表(并根据 ID 分组)，如下所示:

You want to join the tables (and group on ID) before you can compare the two values like this:

SELECT  dbo.Table_1.*,
    CASE WHEN COUNT(dbo.Table_2.ID) = 0 THEN
        0
    ELSE
        1
    END
    AS HasException
FROM         dbo.Table_1 LEFT OUTER JOIN
                      dbo.Table_2 ON dbo.Table_1.ID = dbo.Table_2.ID
GROUP BY dbo.Table_1.ID

这篇关于获取 T-SQL 中子查询的当前记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！