我收到两次相同值的汇总

  • 本文关键字:两次 sql-server
  • 更新时间 :
  • 英文 :


我有一个表sellers,其中列出了我的每一个卖家,并且我最近添加了objective列。

id |name   | team_leader_id | team_leader | objective
--------------------------------------------------
1  |John   | 50             | Mark        | 30
2  |Jane   | 66             | Ryu         | 30
3  |Angela | 66             | Ryu         | 45
4  |Arthur | 190            | Carol       | 35
5  |Anthony| 20             | Adam        | 50

我有另一个表sales,我在seller_id上链接我的sellers表。

sale_id |seller_id |seller_name |item
-------------------------------------
56879   |2         |Jane        |4P
23512   |2         |Jane        |3P
54827   |2         |Jane        |3P
12345   |5         |Anthony     |4P
55435   |4         |Arthur      |GSM

我尝试的查询是:

SELECT coalesce(seller.team_leader,'') team_leader,
coalesce(sales.seller_name,'TOTAL') seller_name,
seller.objective,
count(*) as quantity
FROM sales
JOIN seller ON seller.id = sales.seller_id
WHERE seller.team_leader_id = 66
GROUP BY seller.team_leader, ROLLUP(sales.seller_name), seller.objective

我注意到,结果我得到了每一行的副本,现在有一个目标。我认为问题是,因为我的objective列是新的,我加入我的sales表与我的seller表,它计算我单独创建objective列之前的记录。

所以,我期望的结果是

team_leader | seller_name | objective | quantity
------------------------------------------------
Ryu         | TOTAL       |           | 3
| Jane        | 30        | 3
| Angela      | 45        | 0

但这就是我得到的

team_leader | seller_name | objective | quantity
------------------------------------------------
Ryu         | TOTAL       |           | 1
Ryu         | TOTAL       | 30        | 2
| Jane        |           | 1
| Jane        | 30        | 2
| Angela      | 45        | 0

当Jane的目标显示为空白时,这是她在我添加objective列之前做的销售。

您可以尝试以下操作:

SELECT case when seller.name is null then max(seller.team_leader) else '' end as team_leader,
isnull(seller.name,'TOTAL') seller_name,
case when seller.name is null then '' else sum(seller.objective) end objective,
count(sales.seller_id) as quantity
FROM seller
LEFT JOIN sales ON seller.id = sales.seller_id
WHERE seller.team_leader_id = 66
group by ROLLUP(seller.name)
order by team_leader desc, quantity desc

或者,如果您不使用ROLLUP,您可以使用以下查询获得完全相同的结果。

;with cte as (
SELECT max(team_leader) team_leader, 
max(name) seller_name, 
max(objective) objective, 
count(seller_id) as quantity
FROM seller
LEFT JOIN sales ON seller.id = sales.seller_id
WHERE seller.team_leader_id = 66
GROUP BY seller.id 
)
SELECT team_leader, 'TOTAL' seller_name, '' objective, sum(quantity) quantity
FROM cte
GROUP BY team_leader
UNION ALL 
SELECT '', seller_name, objective, quantity 
FROM cte

相关内容

  • 没有找到相关文章

最新更新