sql - 显示来自不同表的数据，并以摘要形式显示详细信息

Question

我是初学者，我只是在自学，所以如果我什至不能用程序员的方式表达我想说的话，请原谅!我正在尝试开发可以执行此操作的简单业务应用程序:

1. Register Employee or Customer.
2. Can store expenses or money that employee can take in advance before salary payment (This can be done several times, or it may be zero)
3. Can store payments (Salary given to the employee) - It can be also done several times
4. It can calculate all expenses, calculate all payments, then find their difference, hence will be paid the remaining of the balance!

CREATE TABLE Customer
(    CustomerID INT IDENTITY (1000,1)
    ,FirstName VARCHAR(40)
    ,RegDate DATETIME DEFAULT GETDATE()
    ,Phone VARCHAR(10)
);

CREATE TABLE Expense
(    ExpenseID INT IDENTITY (1000,1)
    ,CustomerID INT NOT NULL
    ,ExpDate DATETIME DEFAULT GETDATE()
    ,Amount MONEY
    ,Comment VARCHAR (100)
);

CREATE TABLE TblLoad
(    LoadID INT IDENTITY (1000,1) --PRIMARY KEY 
    ,CustomerID INT NOT NULL
    ,DepDate DATETIME DEFAULT GETDATE()
    ,Amount MONEY
    ,FromTo VARCHAR (100)
);




select Customer.CustomerID, Customer.FirstName, Expense.Amount As ExpenseAmount from Customer inner join 
Expense on Customer.CustomerID = Expense.CustomerID
WHERE Customer.CustomerID = 1000

select Customer.CustomerID, Customer.FirstName, TblLoad.Amount As LoadAmount from Customer inner join 
TblLoad on Customer.CustomerID = TblLoad.CustomerID
WHERE Customer.CustomerID = 1000

SELECT Customer.CustomerID, Customer.FirstName, e.SE as AmountExp , l.SL as AmountLoad , (l.SL - e.SE) as FinalPay
FROM Customer 
INNER JOIN (SELECT CustomerID, SUM(Amount) AS SE FROM Expense GROUP BY CustomerID) E
ON Customer.CustomerID = e.CustomerID
INNER JOIN (SELECT CustomerID, SUM(Amount) AS SL FROM TblLoad GROUP BY CustomerID) L
ON Customer.CustomerID = l.CustomerID
WHERE Customer.CustomerID = 1000

附件是我所期望的和我愿意的图片 Expected Result

Result From The Code

Answer 1

首先祝贺您取得了如此大的成就。坏消息是 t-sql 不是一个报告工具——如果你想要漂亮的输出(SSRS、Crystal 报告、excel 等)来接近你需要的 t-sql 输出，你需要去别处寻找有点创意，例如:-

/*
drop table customer
drop table expense
drop table TblLoad
CREATE TABLE Customer
(    CustomerID INT --IDENTITY (1000,1)
    ,FirstName VARCHAR(40)
    ,RegDate DATETIME --DEFAULT GETDATE()
    ,Phone VARCHAR(10)
);

INSERT INTO CUSTOMER
(    CustomerID 
    ,FirstName 
    ,RegDate 
    ,Phone ) 
VALUES
(1001,'Emp1',cast('2015/10/20 00:00:00.000' as datetime), null),
(1002,'Emp2',cast('2015/10/22 00:00:00.000' as datetime), null),
(1003,'Emp3',cast('2015/10/25 00:00:00.000' as datetime), null)

CREATE TABLE Expense
(    ExpenseID INT --IDENTITY (1000,1)
    ,CustomerID INT NOT NULL
    ,ExpDate DATETIME DEFAULT GETDATE()
    ,Amount MONEY
    ,Comment VARCHAR (100)
);
truncate table expense
insert into expense
(    ExpenseID 
    ,CustomerID 
    ,ExpDate 
    ,Amount 
    --,Comment 
)
Values
(1001,1001,cast('2015/10/21' as datetime),100),
(1002,1001,cast('2015/11/22' as datetime),20),
(1003,1001,cast('2015/12/25' as datetime),80),
(1004,1002,cast('2015/11/21' as datetime),100),
(1005,1002,cast('2015/12/25' as datetime),200),
(1006,1003,cast('2015/11/25' as datetime),300)

CREATE TABLE TblLoad
(    LoadID INT --IDENTITY (1000,1) --PRIMARY KEY 
    ,CustomerID INT NOT NULL
    ,DepDate DATETIME DEFAULT GETDATE()
    ,Amount MONEY
    ,FromTo VARCHAR (100)
);
truncate table tblload
insert into tblload
(    loadID 
    ,CustomerID 
    ,depDate 
    ,Amount 
    --,Comment 
)
Values
(1001,1001,cast('2015/10/21' as datetime),1000),
(1002,1001,cast('2015/11/21' as datetime),200),
(1003,1001,cast('2015/12/24' as datetime),800),
(1004,1002,cast('2015/11/21' as datetime),2000),
(1005,1002,cast('2015/12/25' as datetime),500),
(1006,1003,cast('2015/11/24' as datetime),4000)
*/
select      
            --case
            --when right(rtrim(cast(yyyymm as char(8))) ,2 ) = '13' then char(30)
            --else cast(yyyymm as char(8))
            --end
            case
            when right(rtrim(cast(yyyymm as char(8))) ,2 ) = '13' then char(30)
            else cast(customerid as char(4))
            end
            ,case
            when right(rtrim(cast(yyyymm as char(8))) ,2 ) = '13' then 'Total'
            else firstname
            end
            ,sum(amountexp) amuntexp
            ,sum(amountload) amountload
            ,sum(amountload) - sum(amountexp) finalpay

from
(

select      year(e.expdate) * 100 + month(e.expdate) yyyymm,
            C.CustomerID, c.FirstName, sum(e.amount) as AmountExp , 0 as AmountLoad , 0 as FinalPay
FROM        Customer c
INNER JOIN  Expense  E ON C.CustomerID = e.CustomerID
group   by  year(e.expdate) , month(e.expdate),
            C.CustomerID, c.FirstName
union
select      year(e.expdate) * 100 +  13 ,
            C.CustomerID, c.FirstName, sum(e.amount) as AmountExp , 0 as AmountLoad , 0 as FinalPay
FROM        Customer c
INNER JOIN  Expense  E ON C.CustomerID = e.CustomerID
group   by  year(e.expdate) * 100 +  13,
            C.CustomerID, c.FirstName
union
select      year(l.depdate) * 100 +  month(l.depdate) yyyymm,
            C.CustomerID, c.FirstName, 0 asAmountExp , sum(l.amount) as AmountLoad , 0 as FinalPay
FROM        Customer c
INNER JOIN  tblload  l ON C.CustomerID = l.CustomerID
group   by  year(l.depdate) , month(l.depdate),
            C.CustomerID, c.FirstName
union
select      year(l.depdate) * 100 +  13 yyyymm,
            C.CustomerID, c.FirstName, 0 asAmountExp , sum(l.amount) as AmountLoad , 0 as FinalPay
FROM        Customer c
INNER JOIN  tblload  l ON C.CustomerID = l.CustomerID
group   by  year(l.depdate) * 100 +  13,
            C.CustomerID, c.FirstName
) s
group   by  s.CustomerID,s.firstname,yyyymm 
order   by  s.CustomerID,s.firstname,yyyymm