A查询语句的字段 从B查询语句匹配，找到就添加新列 找不到填充0怎么写。

[QQ1009777]

A 语句:select Id, Nubmer from dbo.A


B 语句:select Nubmer,COUNT(distinct IP) from dbo.B where DateDiff(dd,CreateTime ,getdate())=0 group by Nubmer


现在要实现在A查询出来的表加上一列, 这一列是由 A的Nubmer 与B的Nubmer 匹配,找到则加上,找不到则填充0

[surl61240]

版主V5

[shoppo0505]

没看懂没看懂

[二月十六]

SELECT id,
       Nubmer,
       ISNULL(ipcount, 0) AS ipcount
FROM dbo.a
    LEFT JOIN
    (
        SELECT Nubmer,
               COUNT(DISTINCT IP) AS ipcount
        FROM dbo.b
        WHERE DATEDIFF(dd, CreateTime, GETDATE()) = 0
        GROUP BY Nubmer
    ) t
        ON a.Number = t.Number;