sql总结特定的行而没有子查询返回多个行

发布于 2025-02-07 16:56:58 字数 2998 浏览 5 评论 0原文

有一些与此问题相似的帖子，但是我无法通过这些帖子找到解决此问题的解决方案。

我有以下两个桌子（我没有发布完整表格，要理解问题都需要太多）：

表1：员工

emp_id	first_name	last_name
102	Michael	Scott
108	Jim	Halpert

表2：works_with

emp_id emp_id emp_id	Client_id	total_sales
102	401	267,000
102	406	15,000
108	402	22,500
108	403	12,000

如果此表数据不够，我可以返回并添加更多。

我遇到的问题是此代码：

SELECT e.emp_id AS ID, 
e.first_name AS 'First Name', 
e.last_name AS 'Last Name', 
ww.total_sales = (SELECT SUM(ww.total_sales) 
                 FROM works_with
                 WHERE e.emp_id = ww.emp_id
                 ) AS Sales
FROM (employee e, works_with ww)
INNER JOIN works_with ON ww.emp_id = e.emp_id;

当我运行这样的代码时，我会收到错误：“ SQL错误（1242）：子查询返回超过1行。”

我在网上看到了一个解决方案，该解决方案说要在子查询之前添加“任何”，但这是结果表：

ID	名称姓氏	销售	102
Michael	Scott	0	这

是我想要结果表的样子：

ID	名字姓氏	姓氏	销售
102	Michael	Scott	282,000
108	Jim	Halper	34,500

我对如何解决此问题感到困惑。任何帮助将不胜感激！

原文

There are a few posts that seem similar to this problem, but I cannot find the solution to this problem through those posts.

I have the following two tables that I am working with (I'm not posting the full table, just as much is needed to understand the problem):

Table 1: employee

emp_id	first_name	last_name
102	Michael	Scott
108	Jim	Halpert

Table 2: works_with

emp_id	client_id	total_sales
102	401	267,000
102	406	15,000
108	402	22,500
108	403	12,000

If this table data is not sufficient, I can go back and add more.

The issue I am having is with this code:

SELECT e.emp_id AS ID, 
e.first_name AS 'First Name', 
e.last_name AS 'Last Name', 
ww.total_sales = (SELECT SUM(ww.total_sales) 
                 FROM works_with
                 WHERE e.emp_id = ww.emp_id
                 ) AS Sales
FROM (employee e, works_with ww)
INNER JOIN works_with ON ww.emp_id = e.emp_id;

When I run the code like this, I get the error: "SQL Error (1242): Subquery returns more than 1 row."

I saw a solution online that said to add "Any" before the subquery, but this is the resulting table:

ID	First Name	Last Name	Sales
102	Michael	Scott	0

This is what I want the resulting table to look like:

ID	First Name	Last Name	Sales
102	Michael	Scott	282,000
108	Jim	Halper	34,500

I am confused as to how I would fix this. Any help is greatly appreciated!

分享到QQ

分享到微博

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

发布评论

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

小姐丶请自重 2025-02-14 16:56:58

这应该为您提供所需的结果，而表1是您提到的第一张表，表2是第二个表2

SELECT
    T1.*
    SUM(T2.TOTAL_SALES)
FROM <TABLE1> T1
    JOIN <TABLE2> T2 ON T1.EMP_ID = T2.EMP_ID
GROUP BY
    T1.EMP_ID, T1.FIRST_NAME, T1.LAST_NAME

This should give you the results you need where table 1 is the first table you mentioned and table 2 is the second

SELECT
    T1.*
    SUM(T2.TOTAL_SALES)
FROM <TABLE1> T1
    JOIN <TABLE2> T2 ON T1.EMP_ID = T2.EMP_ID
GROUP BY
    T1.EMP_ID, T1.FIRST_NAME, T1.LAST_NAME

回复收藏 0 原文

~没有更多了~