代码之家 › 专栏 › 技术社区 › Doonie Darkoo

获取子帐户

sql-server

Doonie Darkoo · 技术社区 · 6 年前

COATitle 我输入子记录,这些记录是针对特定主帐户的支出。在我的示例数据中 Medical 有多个子记录,与 Incentive .

我的问题是,当我选择特定的主帐户时,就像我选择 医疗 MasterID 2因为他们是 激励

CREATE TABLE TransactionMaster
(
ID Int,
TransactionCode VARCHAR(25),
PRIMARY KEY (ID)
)

INSERT INTO TransactionMaster VALUES (1, 'TRA-001');
INSERT INTO TransactionMaster VALUES (2, 'TRA-002');
INSERT INTO TransactionMaster VALUES (3, 'TRA-003');

CREATE TABLE TransactionDetail
(
ID Int,
MasterID Int,
COATitle VARCHAR(25),
CrAmount NUMERIC(18,2),
DrAmount NUMERIC(18,2),
PRIMARY KEY (ID),
FOREIGN KEY (MasterID) REFERENCES TransactionMaster(ID)
)


INSERT INTO TransactionDetail VALUES (1, 1, 'Medical', '2500', NULL)
INSERT INTO TransactionDetail VALUES (2, 1, 'Travelling', NULL, '2500')
INSERT INTO TransactionDetail VALUES (3, 1, 'Medicine', NULL, '2500')
INSERT INTO TransactionDetail VALUES (4, 1, 'Doc Fee', NULL, '2500')
INSERT INTO TransactionDetail VALUES (5, 2, 'Incentive', '3000', NULL)
INSERT INTO TransactionDetail VALUES (6, 2, 'Extra', NULL, '2500')
INSERT INTO TransactionDetail VALUES (7, 2, 'Bonus', NULL, '500')
INSERT INTO TransactionDetail VALUES (8, 3, 'Medical', NULL, '3000')
INSERT INTO TransactionDetail VALUES (9, 3, 'Tests', '2500', NULL)
INSERT INTO TransactionDetail VALUES (10, 3, 'Juice', '500', NULL)

查询示例:

SELECT [Voucher].[TransactionCode], [Detail].[COATitle], [Detail].[CrAmount], [Detail].[DrAmount] 
FROM [TransactionMaster] [Voucher], [TransactionDetail] [Detail]
WHERE [Voucher].[ID] = [Detail].[MasterID] AND COATitle NOT IN ('Medical')

TransactionCode           COATitle                  CrAmount                                DrAmount
------------------------- ------------------------- --------------------------------------- ---------------------------------------
TRA-001                   Travelling                NULL                                    2500.00
TRA-001                   Medicine                  NULL                                    2500.00
TRA-001                   Doc Fee                   NULL                                    2500.00
TRA-002                   Incentive                 3000.00                                 NULL
TRA-002                   Extra                     NULL                                    2500.00
TRA-002                   Bonus                     NULL                                    500.00
TRA-003                   Tests                     2500.00                                 NULL
TRA-003                   Juice                     500.00                                  NULL

所需的输出不应包含 TransactionCode 具有 'TRA-002' .

1 回复 | 直到 6 年前

Sergey Menshov 6 年前

尝试以下查询

SELECT
  m.TransactionCode,
  d.COATitle,
  d.CrAmount,
  d.DrAmount
FROM TransactionDetail d
JOIN TransactionMaster m ON d.MasterID=m.ID
WHERE d.MasterID IN(
      SELECT MasterID
      FROM TransactionDetail
      WHERE COATitle='Medical'
    )
  AND d.COATitle<>'Medical'