如何进行这些JOIN查询? [英] How to make these JOIN queries?
问题描述
我正在处理一些有关我的任务的查询,任何帮助将不胜感激.
I am dealing with some queries for my assignment and any help would be greatly appreciated.
- 列出分支机构以及雇员人数和分配给他们的客户,总贷款额,总账户余额,给定城市中分支机构的资产.
- 列出在给定时间段内进行帐户和贷款操作的客户
- 列出员工及其在给定时间段内每个客户提供服务的数量
我想一个简单的例子足以解决休息.
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屋!