有人可以深入说明查询吗？

发布于 2025-01-26 19:02:13 字数 2194 浏览 3 评论 0原文

问题：编写一个SQL查询，以从拥有多个客户的给定表中找到销售人员。返回salesman_id和名称。

示例表：<代码>客户

City	City	Sklesman_id	3002	Nick
Rimando	New	100	5001	3007
Brad	Davis	York	20051	3001
3005	Zusi	California California	5002	3002
customer_id	GREEN	2002	cust_name	LONED
JULIAN	GREEN	New	York	Graham
JULIAN	California Cameron	Berlin	100	5003
3003	Jozy Altidor	Moscow	200	5007
3001	Brad Guzan	London		5005

样本表：推销员

salesman_id	name	Commission	PARIS PARIS PARIS PARIS PARIS PARIS PARIS PARIS
5001	James Hoog	New York New York New York	0.15
5002	Nail Knite nail Knite	Paris	0.13
PARIS PARIS PARIS PARIS PARIS PARIS PARIS PARIS 0.11 5005	PIT ALEX	伦敦	0.11
5006	MC MC LYON	PARIS	PIT 0.14
5007	Paul Adam	Rome	0.13
5003	LAUSON HEN	SAN JOSE	0.12

解决方案，

SELECT salesman_id,name 
FROM salesman a 
WHERE 1 < (SELECT COUNT(*) 
           FROM customer 
           WHERE salesman_id = a.salesman_id);

因此基本上无法理解该子查询我们如何在不使用加入的情况下比较两个表列，其次是该条件在某种程度上是如何按组来工作的，并提供计数。因此，如果有人可以用简单的语言深入了解这个子查询，那么它将确实很有帮助。

这个问题和解决方案是正确的，因为正在练习W3Resource，您可以在练习11中的SQL子查询练习中找到此问题。

原文

Question: write a SQL query to find the salespeople from the given tables who had more than one customer. Return salesman_id and name.

Sample table: customer

customer_id	cust_name	city	grade	salesman_id
3002	Nick Rimando	New York	100	5001
3007	Brad Davis	New York	200	5001
3005	Graham Zusi	California	200	5002
3008	Julian Green	London	300	5002
3004	Fabian Johnson	Paris	300	5006
3009	Geoff Cameron	Berlin	100	5003
3003	Jozy Altidor	Moscow	200	5007
3001	Brad Guzan	London		5005

Sample table: salesman

salesman_id	name	city	commission
5001	James Hoog	New York	0.15
5002	Nail Knite	Paris	0.13
5005	Pit Alex	London	0.11
5006	Mc Lyon	Paris	0.14
5007	Paul Adam	Rome	0.13
5003	Lauson Hen	San Jose	0.12

Solution

SELECT salesman_id,name 
FROM salesman a 
WHERE 1 < (SELECT COUNT(*) 
           FROM customer 
           WHERE salesman_id = a.salesman_id);

So basically am unable to understand the subquery how can we compare two table column without using the joins and secondly how this where condition is working as a group by as well and provide the count. So if someone can help me understand this subquery in depth with easy language then it would be really helpful.

This question and solution is correct because am practicing on W3resource and you can find this question under the topic of SQL Subquery Exercises in the exercise 11.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

猥琐帝 2025-02-02 19:02:13

这是a 相关子提法。实际上，外部表的每个匹配行运行一次，使用联接条件作为滤波器标准，即它将计算所有行的所有行，然后从外部表中进行ID =第一个ID，然后ID = second ID来自外部表格，因此在。然后，每个ID的结果计数将传递到滤波外表行的哪个子句。

您也可以写一个等效的查询：

SELECT s.salesman_id,s.name 
FROM salesman a
INNER JOIN customer b
ON a.salesman_id = b.salesman_id
GROUP BY a.salesman_id
HAVING count(b.customer_id) >  1

This is an example of a correlated subquery. In effect, the subquery is run once for each matching row of the outer table, using the join condition as filter criteria i.e. it will count for all rows with id = first id from outer table, then id = second id from outer table and so on. The resulting count for each id is then passed to the WHERE clause for filtering the rows of the outer table.

You could also write an equivalent query like so:

SELECT s.salesman_id,s.name 
FROM salesman a
INNER JOIN customer b
ON a.salesman_id = b.salesman_id
GROUP BY a.salesman_id
HAVING count(b.customer_id) >  1

回复收藏 0 原文

~没有更多了~