我该如何进行这些查询？

发布于 2025-01-15 01:00:27 字数 2295 浏览 4 评论 0原文

我正在尝试对老师没有教过我们的科目的作业进行这些查询，我的同学也无法做到这一点，我有这 4 个表到 MySQL

Activity

id活动 |名称活动 | id部门
---------: | :-------------------- | ------------：
         1 |招聘 | 1
         2 |工资单 | 1
         3 |采购| 2
         4 |报价 | 2
         5 |客户联系 | 3
         6 |客户协议| 3
         7 |营销| 3
         8 |植入式广告 | 2
         9 |产品集合| 2
        10 | 10协助 | 1
        11 | 11产品交付| 3
        12 | 12入住和退房 | 4
        13 |优惠| 3

员工

idEmployee |姓名员工 |薪资| id部门
---------: | :------------ | -----: | ------------：
        10 | 10玛丽亚| 4000 | 1
        20 | 20豪尔赫 | 3000 | 2
        30|莱昂诺 | 5000 | 3
        40 | 40帕特里夏 | 3000 | 2
        50 | 50吉尔伯托 | 2000 | 2000 4
        60|贡萨洛 | 7000 | 2
        70 | 70比阿特丽斯 | 3000 | 1
        80|安娜 | 5000 | 3
        90 | 90曼努埃尔| 2000 | 2000 4
       100 | 100西尔维斯特| 7000 | 1
       110 | 110亚历杭德拉| 5000 | 2
       120 | 120费尔南多 | 2000 | 2000 1
       130 | 130华金 | 4000 | 4
       140 | 140佩德罗 | 4000 | 4
       150 | 150巴勃罗 | 2500 | 2500 3

部门

id部门 |姓名部门
------------：| :-------------
           1 |职员         
           2 |购买    
           3 |卖         
           4 |财政

活动-员工

idEmployee | id活动
---------: | ---------：
        10 | 10 1
        10 | 10 2
        20 | 20 3
        20 | 20 4
        30| 5
        30| 6
        30| 7
        50 | 50 8
        50 | 50 9
        70 | 70 10
        90 | 90 8
       100 | 100 1

db<>fiddle 此处

一些列因为主键和外键具有相同的名称，主键位于具有这些名称的表中。我想要做的是一些查询，例如：

显示员工姓名、他们所属部门的名称以及他们所做的活动的名称，按活动分组（分组依据）。
使用子查询 显示员工姓名、所属部门名称以及他们从事的活动名称，按活动分组 (Group by)。

我们尝试了一些查询，但员工姓名只是重复，并且没有按活动分组。

https://drive.google.com/file/d/ 1f7AkdjHYltKxg640diUZfD2zs2gM-sol/view?usp=sharing

希望有人可以帮助我们，我们有点绝望:( 很抱歉不知道如何直接将表格放在这里，这是我在这里的第一篇文章:(

原文

I am trying to do these queries for a homework of a subject that the teacher didn't teach us, and none of my classmates can do it either, I have these 4 tables to MySQL

Activity

idActivity | nameActivity           | idDepartment
---------: | :--------------------- | -----------:
         1 | hiring                 |            1
         2 | payroll                |            1
         3 | purchasing             |            2
         4 | quotes                 |            2
         5 | Customer contact       |            3
         6 | Customer agreement     |            3
         7 | marketing              |            3
         8 | Product placement      |            2
         9 | Collection of products |            2
        10 | assitance              |            1
        11 | Product delivery       |            3
        12 | Check-in and check-out |            4
        13 | offers                 |            3

Employee

idEmployee | NameEmployee | Salary | idDepartment
---------: | :----------- | -----: | -----------:
        10 | Maria        |   4000 |            1
        20 | Jorge        |   3000 |            2
        30 | Leonor       |   5000 |            3
        40 | Patricia     |   3000 |            2
        50 | Gilberto     |   2000 |            4
        60 | Gonzalo      |   7000 |            2
        70 | Beatriz      |   3000 |            1
        80 | Ana          |   5000 |            3
        90 | Manuel       |   2000 |            4
       100 | Silvestre    |   7000 |            1
       110 | Alejandra    |   5000 |            2
       120 | Fernando     |   2000 |            1
       130 | Joaquin      |   4000 |            4
       140 | Pedro        |   4000 |            4
       150 | Pablo        |   2500 |            3

Department

idDepartment | nameDepartment
-----------: | :-------------
           1 | staff         
           2 | Purchasing    
           3 | Sells         
           4 | finances

Activity-Employee

idEmployee | idActivity
---------: | ---------:
        10 |          1
        10 |          2
        20 |          3
        20 |          4
        30 |          5
        30 |          6
        30 |          7
        50 |          8
        50 |          9
        70 |         10
        90 |          8
       100 |          1

db<>fiddle here

Some columns have the same name because primary and foreign keys, the primary keys are in the tables that have there names. What am I trying to do are some queries like:

Show employees names, name of the department that they belong to, and name of the activities that they do, grouping by activity (Group by).
Using Subqueries Show employees names, name of the department that they belong to, and name of the activities that they do, grouping by activity (Group by).

We tried some queries but the employee name just repeat itself, and didn't group by activities.

https://drive.google.com/file/d/1f7AkdjHYltKxg640diUZfD2zs2gM-sol/view?usp=sharing

Hopefully someone can help us, we are kinda desperate :(
And sorry for not knowing how to put directly the tables in here, it's my first post in here:(

分享到QQ

分享到微博

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

发布评论

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

烂柯人 2025-01-22 01:00:27

select 
NameEmployee,
group_concat(nameActivity) nameActivity,
(select nameDepartment from Department where idDepartment=tmpx.idDepartment) as DeptName from (select  t1.NameEmployee,
(select distinct nameActivity from Activity where idActivity=t2.idActivity) as nameActivity,
(select distinct idDepartment from Activity where idActivity=t2.idActivity) as idDepartment
from Employee t1 join `Activity-Employee` t2 
on t1.idEmployee=t2.idEmployee) as tmpx GROUP BY tmpx.NameEmployee;

检查它是否适合您

select 
NameEmployee,
group_concat(nameActivity) nameActivity,
(select nameDepartment from Department where idDepartment=tmpx.idDepartment) as DeptName from (select  t1.NameEmployee,
(select distinct nameActivity from Activity where idActivity=t2.idActivity) as nameActivity,
(select distinct idDepartment from Activity where idActivity=t2.idActivity) as idDepartment
from Employee t1 join `Activity-Employee` t2 
on t1.idEmployee=t2.idEmployee) as tmpx GROUP BY tmpx.NameEmployee;

Check if it works for u

回复收藏 0 原文

~没有更多了~