如何进行这些JOIN查询? [英] How to make these JOIN queries?

问题描述

我正在处理一些有关我的任务的查询，任何帮助将不胜感激.

I am dealing with some queries for my assignment and any help would be greatly appreciated.

1. 列出分支机构以及雇员人数和分配给他们的客户，总贷款额，总账户余额，给定城市中分支机构的资产.
2. 列出在给定时间段内进行帐户和贷款操作的客户
3. 列出员工及其在给定时间段内每个客户提供服务的数量

我想一个简单的例子足以解决休息.

I guess a simple example would be enough for me to solve rest.

这是到目前为止我为第一个尝试的内容:

Here's what I've tried so far for the first one:

ALTER PROCEDURE [dbo].[SelecBranchesByCity]
    (@City varchar(50))
AS
    select
        Br.Name as BranchName,
        COUNT(emps.ID) as NumberOfEmployee,
        SUM(emps.NumberOfCustomers) as TotalCustomers,
        SUM(lo.Amount) as TotalAmountOfLoan,
        SUM(acc.Balance) as TotalBalance,
        Br.Assets as Assets
    from Branches Br
    left outer join Employees emps on emps.[BranchName] = Br.Name
    left outer join Loans lo on lo.[BranchName] = Br.Name
    left outer join Accounts acc on acc.[BranchName] = Br.Name
    where
        Br.[Address] like '%'+@City+'%'
    GROUP BY
        Br.ID,
        Br.Name,
        Br.Assets

这里是架构！

推荐答案

模式令人恐惧.多对多的客户贷款?客户帐户上有多对多?为什么??员工有一个branchname列，而不是与branches的FK关系. loan_operations有FK到employees !?我并不是要偏离主题或声音轻率，但是这里有很多反模式，我什至都不知道从哪里开始.但是无论如何，我都会尝试解决具体问题.

Schema is frightening. Many-to-many on customers-loans? Many-to-many on customers-accounts? Why?? Employees has a branchname column instead of an FK relationship to branches. loan_operations has FK to employees!? I don't mean to stray from the topic or sound flippant, but there are so many anti-patterns here I don't even know where to begin. But I will try to help with the specific question anyway.

CREATE PROCEDURE GetBranchSummaryByCity
    @City varchar(50)
AS
SELECT
    b.id, b.address, b.name, b.assets,
    b2.EmployeeCount, b2.CustomerCount,
    b2.TotalLoanAmount, b2.TotalAccountBalance
FROM branches b
INNER JOIN
(
    SELECT
        b.id,
        ISNULL(COUNT(DISTINCT e.id), 0) AS EmployeeCount,
        ISNULL(COUNT(c.id), 0) AS CustomerCount,
        ISNULL(SUM(l.amount), 0) AS TotalLoanAmount,
        ISNULL(SUM(a.balance), 0) AS TotalAccountBalance
    FROM branches b
    LEFT JOIN employees e
        -- Fix your schema so this matches the branch ID instead!
        ON e.branchname = b.name
    LEFT JOIN employee_customer ec
        ON ec.employeeid = e.id
    LEFT JOIN customers c
        ON c.id = ec.customerid
    LEFT JOIN customer_accounts ca
        ON ca.customerid = c.id
    LEFT JOIN accounts a
        ON a.id = ca.accountid
    LEFT JOIN loan_customer lc
        ON lc.customerid = c.id
    LEFT JOIN loans l
        ON l.id = lc.loanid
    WHERE b.Name LIKE '%' + @City + '%'
    GROUP BY b.id
) b2
ON b2.id = b.id

我会注意到您在loans和loan_operations中都有一个amount列.很难知道这两者之间有什么区别-loans完全不应该有此列，而应该从loan_operations的列中求和.

I'll note that you have an amount column in both loans and loan_operations. It's hard to know what the difference is between these two - it's entirely possible that loans shouldn't have this column at all, and instead it should be summed from the column in loan_operations.

CREATE PROCEDURE FindCustomersWithLoansByDateRange
    @BeginDate datetime,
    @EndDate datetime
AS
SELECT c2.id, c2.name, c2.address, ...
FROM
(
    SELECT DISTINCT c.id
    FROM customers c
    INNER JOIN loan_customer lc
        ON lc.customerid = c.id
    INNER JOIN loans l
        ON l.id = lc.loanid
    INNER JOIN loan_operations lo
        ON lo.loanid = l.id
    WHERE lo.date BETWEEN @BeginDate AND @EndDate
) c1
INNER JOIN customers c2
ON c2.id = c1.id

Q3:

CREATE PROCEDURE GetEmployeeServiceSummaryByDateRange
    @BeginDate datetime,
    @EndDate datetime
AS
SELECT e.id, ISNULL(es.CustomerCount, 0) AS CustomerCount, e.name, e.position, ...
FROM employees e
LEFT JOIN
(
    SELECT e.id, COUNT(DISTINCT c.id) AS CustomerCount
    FROM employees e
    INNER JOIN employee_customer ec
        ON ec.employeeid = e.id
    INNER JOIN customers c
        ON c.id = ec.customerid
    INNER JOIN loan_customer lc
        ON lc.customerid = c.id
    INNER JOIN loans l
        ON l.id = lc.loanid
    INNER JOIN loan_operations lo
        ON lo.loanid = l.id
    WHERE lo.date BETWEEN @BeginDate AND @EndDate
    GROUP BY e.id
) es
ON es.id = e.id

这篇关于如何进行这些JOIN查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！