SQL根据列从表B中选择,但根据表A ID获取所有行

有很多方法可以做到这一点。一种方法是使用 EXISTS

SELECT  a.InvoiceNo, A.Total, B.ItemCode
FROM    TableA a
        INNER JOIN TableB b
            ON a.InvoiceNo = b.InvoiceNo
WHERE   EXISTS (SELECT 1
                FROM TableB c
                WHERE b.InvoiceNo = c.InvoiceNo
                        AND c.ItemCode = 'Item-B')

这里有一个 Demo .

另一种方法是使用 INNER JOIN 在仅获取所有发票的子查询上 ItemB

SELECT  a.InvoiceNo, A.Total, B.ItemCode
FROM    TableA a
        INNER JOIN TableB b
            ON a.InvoiceNo = b.InvoiceNo
        INNER JOIN (SELECT InvoiceNo FROM TableB c WHERE ItemCode = 'Item-B') c
            ON a.InvoiceNo = c.InvoiceNo

这里有一个 Demo .

似乎是你想要的:

USE Sandbox;
GO

CREATE TABLE TABLEA (InvoiceNo varchar(6),
                     Total int)

CREATE TABLE TABLEB (InvoiceNo varchar(6),
                     ItemCode varchar(6));
GO
INSERT INTO TABLEA
VALUES ('000001',100),
       ('000002',50 ),
       ('000003',200);
INSERT INTO TABLEB
VALUES ('000001','Item-A'),
       ('000001','Item-B'),
       ('000001','Item-C'),
       ('000002','Item-A'),
       ('000003','Item-B'),
       ('000003','Item-D');
GO
SELECT B.InvoiceNo,
       A.Total,
       B.ItemCode
FROM TABLEB B
     JOIN TABLEA A ON B.InvoiceNo = A.InvoiceNo
WHERE EXISTS(SELECT 1
             FROM TABLEB sq
             WHERE B.InvoiceNo = sq.InvoiceNo
               AND sq.ItemCode = 'Item-B')
ORDER BY B.InvoiceNo, B.ItemCode;

GO
DROP TABLE TABLEA;
DROP TABLE TABLEB;