我有一个表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