首先，无论我有什么经验，我都对MySQL不太满意，我会用它来进行此查询
在我的查询中，主要问题是
left join subdealers as subdealer
ON
(
employees.Salesman1Number = subdealer.employee_number
OR employees.Salesman2Number = subdealer.employee_number
OR employees.Salesman3Number = subdealer.employee_number
)
我正在尝试按
FrontGross分组的BackGross，subdealer.group_name等问题，问题是Saleman1Number和Salesman2Number可能属于同一group_name，并且在下面的查询中将它们视为两个不同的推销员，而如果Salesman1Number，Saleman2Number和Salesman2Number属于同一个subdealer.group_name，我希望它们算作一个例如：
Salesman1Number属于group_name舰队，而Salesman2Number也属于Fleet他们俩都卖了一辆车。现在，他们俩都将所售商品的一半功劳归功于这些，而功劳归功于
group_name舰队，一半来自Salesman1Number，一半来自Salesman2Number目前，我编写的查询不会根据它们的
group_name将它们分成两半，而是将其计为Salesman1Number中的一个还有一个来自
Salesman2NumberSELECT count(core_leads.core_id) as leads,
count(new.id) as new,
count(used.id) as used,
IFNULL(SUM(profit.FrontGross) + SUM(finance.HoldbackAmount), 0) as FrontGross,
IFNULL(SUM(profit.BackGross) + SUM(profit.FinanceReserve), 0) as BackGross,
IFNULL(SUM(profit.TotalProfit), 0) as TotalProfit,
IFNULL(SUM(finance.HoldbackAmount), 0) as HoldbackAmount,
IFNULL(SUM(finance.Holdcheck), 0) as Holdcheck,
IFNULL(subdealer.group_name, 'Others') as group_name
from core_leads
inner join
(
select * from closed_deals
right join
(
select ContractDate, id as infoId, closed_deal_id
from closed_deal_infos
) as info
ON closed_deals.id = info.closed_deal_id
AND DATE(info.ContractDate) BETWEEN '2014-01-01' AND '2017-01-01'
) as closed
ON core_leads.core_id = closed.core_lead_id
AND core_leads.type != 'Unwind'
AND core_leads.type != 'Canceled'
left join closed_vehicles as used
ON closed.id = used.closed_deal_id
AND used.NewUsed = 'U'
left join closed_vehicles as new
ON closed.id = new.closed_deal_id
AND new.NewUsed = 'N'
left join closed_dealer_employees as employees
ON closed.id = employees.closed_deal_id
left join subdealers as subdealer
ON
(
employees.Salesman1Number = subdealer.employee_number
OR employees.Salesman2Number = subdealer.employee_number
OR employees.Salesman3Number = subdealer.employee_number
)
AND
(
subdealer.group_name = 'Fleet'
OR subdealer.group_name = 'Internet'
OR subdealer.group_name = 'Sales'
)
left join closed_profit as profit
ON closed.id = profit.closed_deal_id
left join closed_finance as finance
ON closed.id = finance.closed_deal_id
group by subdealer.group_name
这导致这个
在Fleet
dept列中，名称leads应该是38而不是40，因为它将两个属于同一group_name的两个不同的推销员计算在内让我知道我是否不够清楚
最佳答案
为了简化您的示例，我将仅使用两个表。
人员：
| personId | groupId |
|----------|---------|
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
| 6 | 5 |
活动：
| actId | person1Id | person2Id | person3Id | actValue |
|-------|-----------|-----------|-----------|----------|
| 1 | 1 | 2 | 3 | 1 |
| 2 | 1 | 2 | 4 | 10 |
| 3 | 5 | (null) | (null) | 100 |
与您的问题匹配的查询为：
select
p.groupId, count(a.actId) numActs, sum(a.actValue) sumVals, group_concat(a.actId) as acts
from activities a
left join persons p on (
a.person1Id = p.personId or
a.person2Id = p.personId or
a.person3Id = p.personId
)
group by p.groupId;
结果：
| groupId | numActs | sumVals | acts |
|---------|---------|---------|-------|
| 1 | 2 | 11 | 1,2 |
| 2 | 3 | 12 | 1,2,1 |
| 3 | 1 | 10 | 2 |
| 4 | 1 | 100 | 3 |
对于groupId = 2的组，我们计算了三个活动（1,2,1）。由于同一组中有两个人，因此actId = 1的活动被计数两次。为防止这种情况，我们可以定义如果person1来自同一组，则不应该计算person2的行（应将其过滤掉）。如果person1或person 2来自同一组，则不应计入person3的行。这可以在
WHERE子句中通过相关选择来完成：select
p.groupId, count(a.actId) numActs, sum(a.actValue) sumVals, group_concat(a.actId) as acts
from activities a
left join persons p on (
a.person1Id = p.personId or
a.person2Id = p.personId or
a.person3Id = p.personId
)
where (p.personId = a.person1Id
) or (
p.personId = a.person2Id and
p.groupId not in (select groupId from persons where personId = a.person1Id)
) or (
p.personId = a.person3Id and
p.groupId not in (select groupId from persons where personId in (a.person1Id, a.person2Id))
)
group by p.groupId;
结果：
| groupId | numActs | sumVals | acts |
|---------|---------|---------|------|
| 1 | 2 | 11 | 1,2 |
| 2 | 2 | 11 | 1,2 |
| 3 | 1 | 10 | 2 |
| 4 | 1 | 100 | 3 |
http://sqlfiddle.com/#!9/604a5/1
注意：如果可能-您应该考虑对表格进行规范化。