mysql - 在 mysql 数据查询上获取不需要的输出 mysql-6ren

Nhận được kết quả không mong muốn khi truy vấn dữ liệu mysql mysql

In lại 作者：行者123 更新时间：2023-11-29 22:25:45

以下是我的 SQL 数据结构，我正在尝试如下两个查询:

Select 
    Wrk_ID, Wrk_LastName, Skill_Desc
từ 
    Worker, Skill
Ở đâu 
    (UPPER(skill_desc) = UPPER('Map Designer') or 
     UPPER(skill_Desc) = UPPER('Game Designer'));

我得到了所需输出的两倍

lựa chọn 
    wrk_ID, concat('Wrk_FirstName','Wrk_LastName') As FullName, 
    COUNT(Assign_ID) as Max_Assignment
từ 
    assignment worker
group by 
    Wrk_ID, concat('Wrk_FirstName','Wrk_LastName');

在此查询中，我获取的是 wrk_firstname 和姓氏，而不是名字和姓氏的实际数据。

--
-- Dumping data for table `region`
--
INSERT INTO `region` (`Reg_ID`, `Reg_desc`) VALUES
('R0010', 'California'),
('R0011', 'Nevada'),
('R0012', 'Arizona'),
('R0013', 'Michigan'),
('R0014', 'New York'),
('R0015', 'Texas'),
('R0016', 'Washington'),
('R0017', 'Oregon'),
('R0018', 'Ohio'),
('R0019', 'Boston');
-- --------------------------------------------------------
--
-- Table structure for table `client`
--
INSERT INTO `client` (`Cli_ID`, `Cli_FirstName`, `Cli_LastName`, `Cli_Initial`, `Cli_StreetAddress`, `Cli_Sex`, `Cli_Phone`,`Reg_ID`) VALUES
('C0010', 'Krishneel', 'Sharma', 'V', '99 Rock Road', 'M', '844-2573', 'R0010'),
('C0011', 'Ravneel', 'Sharma', 'V', 'Cecil Street', 'M', '894-1238', 'R0011'),
('C0012', 'Brad', 'Pitt', 'W', '615 Devil Road', 'M', '894-2285', 'R0012'),
('C0013', 'Tom', 'Cruz', 'F', '615 Gorge Town', 'M', '894-2180', 'R0013'),
('C0014', 'Paul', 'Walker', 'F', '615 Gran Street', 'M', '222-1672', 'R0014'),
('C0015', 'Jeniffer','Lopez', 'B', '713 Tall Road', 'F', '442-3381','R0015'),
('C0016', 'Brown', 'James', 'G', '615 South Place', 'F', '297-1228', 'R0016'),
('C0017', 'Lady', 'Gaga', 'G', '615 Hill Road', 'F', '290-2556', 'R0017'),
('C0018', 'Cristiano', 'Ronaldo', 'G', '713 Magic Road', 'M', '382-7185', 'R0018'),
('C0019', 'Lioneil', 'Messi', 'K', '615 Well Street', 'M', '297-3809', 'R0019');
-- --------------------------------------------------------
--
-- Table structure for table `worker`
--
INSERT INTO `worker` (`Wrk_ID`, `Wrk_FirstName`, `Wrk_LastName`, `Wrk_Initial`, `Wrk_HireDate`, `Wrk_BirthDate`, `Wrk_Phone`, `Wrk_IRD_Num`, `Reg_ID`) VALUES
('W0010', 'Rhonda', 'Lewis', 'G', '2000-03-21', '1985-02-19', '855-2573', 'X993473419', 'R0010'),
('W0011', 'Rhett', 'VanDam', 'V', '2002-03-19', '1985-01-11', '994-1238', 'X993473500', 'R0011'),
('W0012', 'Anne', 'Jones', 'M', '2000-04-11', '1965-08-19', '894-9985', 'X9934735001', 'R0012'),
('W0013', 'John', 'Lange', 'P', '2000-04-12', '1975-05-17', '894-5680', 'X993473502', 'R0013'),
('W0014', 'Robert', 'Williams', 'D', '2004-05-19', '1978-03-02', '244-1672', 'X993473503', 'R0014'),
('W0015', 'Jeanine','Smith', 'K', '2005-06-19', '1988-07-04', '434-3381', 'X993473506', 'R0015'),
('W0016', 'Brown', 'James', 'G', '2006-03-19', '1982-06-28', '297-1232', 'X993473507', 'R0016'),
('W0017', 'Jorge', 'Diante', 'K', '2007-01-19', '1984-11-19', '222-2556', 'X993473508', 'R0017'),
('W0018', 'Paul', 'Wiesenbach', 'R', '2007-01-19', '1976-12-12', '389-7185', 'X993473509', 'R0018'),
('W0019', 'Leighla', 'Genkazi', 'W', '2007-01-19', '1989-04-30', '292-3809', 'X993473600', 'R0019');
-- --------------------------------------------------------
--
-- Dumping data for table `project`
--
INSERT INTO `project`(`Proj_ID`,`Proj_Desc`,`Proj_Est_Start`,`Proj_Est_Finish`,`Proj_Cost`,`Proj_Act_Start`,`Proj_Act_Finish`,`Proj_Act_Cost`,`Cli_ID`) VALUES
('P0010','Map Design','2015-04-01','2015-04-30',10000.00,'2015-04-05','2015-05-01',12000.50,'C0010'),
('P0011','Level Design','2015-03-02','2015-03-15',5000.00,'2015-03-02','2015-03-15',7500.00,'C0011'),
('P0012','Controlls Design','2015-04-20','2015-05-01',20000.00,'2015-04-21','2015-04-30',20500.00,'C0012'),
('P0013','Character Design','2015-04-15','2015-05-11',30000.00,'2015-04-15','2015-05-12',50000.00,'C0010'),
('P0014','Sound Design','2015-01-01','2015-04-30',5000.00,'2015-01-10','2015-04-30',97560.00,'C0013'),
('P0015','Game Play design','2015-03-20','2015-03-30',57000.00,'2015-03-20','2015-03-25',6500.00,'C0014'),
('P0016','Server Design','2015-04-01','2015-04-30',89000.00,'2015-04-01','2015-04-30',8900.00,'C0015'),
('P0017','Online Gamplay Design','2015-05-01','2015-05-15',10000.00,'2015-05-02','2015-05-16',10000.00,'C0016'),
('P0018','Software Design','2015-04-01','2015-04-15',15000.00,'2015-04-01','2015-04-15',14500.00,'C0019'),
('P0019','Game programming','2015-01-14','2015-05-01',125000.00,'2015-01-14','2015-05-01',125000.00,'C0019');
-- --------------------------------------------------------
--
--Dumping data for table `assignment`
--
INSERT INTO `assignment`(`Assign_ID`,`Assign_Desc`,`Assign_Est_Start`,`Assign_Est_Finish`,`Assign_Act_Start`,`Assign_Act_Finish`,`Wrk_ID`,`Proj_ID`) VALUES
('A0010','Design Game maps','2015-03-15','2015-04-20','2015-03-15','2015-04-20','W0010','P0010'),
('A0011','Design All levels of Game','2015-03-02','2015-03-15','2015-03-02','2015-03-15','W0012','P0011'),
('A0012','Design Controls For all Platforms','2015-05-01','2015-05-20','2015-05-02','2015-05-21','W0013','P0012'),
('A0013','Design All Character of Game','2015-04-05','2015-04-30','2015-04-05','2015-05-01','W0014','P0013'),
('A0014','Game music Soundtracks','2015-01-03','2015-04-25','2015-02-05','2015-04-30','W0015','P0014'),
('A0015','Virtual walk through of game','2015-03-15','2015-03-30','2015-03-15','2015-03-30','W0016','P0015'),
('A0016','MultiPlayer Game Design','2015-04-02','2015-04-28','2015-04-04','2015-04-30','W0017','P0016'),
('A0017','Online Hosting Design','2015-05-01','2015-05-16','2015-05-01','2015-05-30','W0018','P0017'),
('A0018','Game Interesction Design','2015-04-01','2015-04-15','2015-04-01','2015-04-15','W0011','P0018'),
('A0019','Game codes','2015-01-14','2015-05-05','2015-01-14','2015-05-05','W0019','P0019');
-- --------------------------------------------------------
--
----Dumping data for table `task`
--
INSERT INTO `task`(`Task_ID`,`Task_Desc`,`Task_Act_Start`,`Task_Act_Finish`,`Assign_ID`) VALUES
('T0010','Impliment Map Design','2015-03-15','2015-04-20','A0010'),
('T0011','Impliment game Levels','2015-03-02','2015-03-15','A0011'),
('T0012','Impliment Game Controls','2015-05-02','2015-05-21','A0012'),
('T0013','Impliment Game Characters','2015-02-05','2015-04-30','A0013'),
('T0014','Impliment Sound Design','2015-03-15','2015-03-30','A0014'),
('T0015','Impliment Gamplay designs','2015-04-04','2015-04-30','A0015'),
('T0016','Office Management Systems','2015-05-01','2015-05-30','A0016'),
('T0017','Impliment Multiplayer Gamplay','2015-04-01','2015-04-15','A0017'),
('T0018','Impliment Online Servers','2015-01-14','2015-05-05','A0018'),
('T0019','Impliment all Game Codes','2015-01-14','2015-05-05','A0019');
-- --------------------------------------------------------
--
----Dumping data for table `invoice`
--
INSERT INTO `invoice`(`Inv_ID`,`Inv_Amount`,`Inv_Due_Date`,`Proj_ID`) VALUES
('I0010',12000.50,'2015-04-30','P0010'),
('I0011',7500.00,'2015-03-31','P0011'),
('I0012',20500.00,'2015-04-30','P0012'),
('I0013',50000.00,'2015-05-30','P0013'),
('I0014',97560.00,'2015-05-30','P0014'),
('I0015',6500.00,'2015-04-30','P0015'),
('I0016',8900.00,'2015-05-30','P0016'),
('I0017',10000.00,'2015-05-30','P0017'),
('I0018',14500.00,'2015-04-30','P0018'),
('I0019',125000.00,'2015-05-15','P0019');    
-- --------------------------------------------------------
--
----Dumping data for table `skill`
--   
INSERT INTO `skill` (`Skill_ID`,`Skill_Desc`,`Skill_PayRate`) VALUES
('S0010','Map Designer',55.00),
('S0011','Level Designer',40.00),
('S0012','Game Designer',65.00),
('S0013','Graphics Designer',55.00),
('S0014','Music Producer',50.00),
('S0015','Project manager',98.50),
('S0016','Software Enginneer',50.00),
('S0017','IT Technician',40.50),
('S0018','Server Adminstrator',62.00),
('S0019','Network Enginneer',125.00);
-- --------------------------------------------------------
--
----Dumping data for table `employee_skill`
--
INSERT INTO `employee_skill`(`Skill_ID`,`Wrk_ID`) VALUES
('S0010','W0010'),
('S0011','W0011'),
('S0012','W0012'),
('S0013','W0013'),
('S0014','W0014'),
('S0015','W0015'),
('S0016','W0016'),
('S0017','W0017'),
('S0018','W0018'),
('S0019','W0019');

1 Câu trả lời

1)您的第一个查询需要连接条件，例如

where worker.wrkid= skill.wrkid and ....

2)您的第二个查询有错误，您需要在分配和工作人员之间使用逗号。还需要加入条件。

关于mysql - 在 mysql 数据查询上获取不需要的输出 mysql，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/30284461/

Bài viết được đề xuất: android - 如何在 Android RelativeLayout 中 float 图像？

Bài viết được đề xuất: mysql - 使用函数显示图像

Bài viết được đề xuất: javascript - css jquery ui slider 自定义

Bài viết được đề xuất: php表单复选框搜索

mysql - 从 bash 输出 sql 输出
我正在使用 OUTFILE 命令，但由于权限问题和安全风险，我想将 shell 的输出转储到文件中，但出现了一些错误。我试过的 #This is a simple shell to connect t
JAVA——程序功能为输入输出、输入输出；想让程序的功能分别为输入输入、输出、输出
我刚刚开始学习 Java，我想克服在尝试为这个“问题”创建 Java 程序时出现的障碍。这是我必须创建一个程序来解决的问题: Tandy 喜欢分发糖果，但只有 n 颗糖果。对于她给第 i 个糖果的人，
c++ - 无法使用 ostream 输出 C++ 输出 vector
你好，我想知道我是否可以得到一些帮助来解决我在 C++ 中打印出 vector 内容的问题我试图以特定顺序在一个或两个函数调用中输出一个类的所有变量。但是我在遍历 vector 时收到一个奇怪的错误
gradle - 重复生成的类 gradle 输出 (build/...) 与 intellij 输出 (out/...)
我正在将 intellij (2019.1.1) 用于 java gradle (5.4.1) 项目，并使用 lombok (1.18.6) 来自动生成代码。 Intellij 将生成的源放在 out
javascript - 如何从 JavaScript 输出 JSON 输出，以便将其识别为 JSON？
编辑:在与 guest271314 交流后，我意识到问题的措辞(在我的问题正文中)可能具有误导性。我保留了旧版本并更好地改写了新版本背景: 从远程服务器获取 JSON 时，响应 header 包含一
java - StoredProcedureCall 1x Varchar 输出 1x Cursor 输出
我的问题可能有点令人困惑。我遇到的问题是我正在使用来自 Java 的 StoredProcedureCall 调用过程，例如: StoredProcedureCall call = new Store
com - COM IDL定义中[输入，输出]和[输出，检索]之间的差异
在我使用的一些IDL中，我注意到在方法中标记返回值有2个约定-[in, out]和[out, retval]。当存在多个返回值时，似乎使用了[in, out]，例如: HRESULT MyMetho
linux - 我如何告诉 `gar` 或 `ar` 输出 `elf32-i386` 输出？
当我查看 gar -h 的帮助输出时，它告诉我: [...] gar: supported targets: elf64-x86-64 elf32-i386 a.out-i386-linux [...
r - Knitr HTML Loop - 一些 HTML 输出，一些 R 输出
我想循环遍历一个列表，并以 HTML 格式打印其中的一部分，以代码格式打印其中的一部分。所以更准确地说:我想产生与这相同的输出 1 is a great number 2 is a great
"Error running git [init /workspace/output/]: exit status 1\n/workspace/output/.git: Permission denied\n"(“运行git[init/工作区/输出/]时出错：退出状态1\n/工作区/输出/.git：权限被拒绝\n”)
我有下面的tekton管道，并尝试在Google Cloud上运行。集群角色绑定。集群角色。该服务帐户具有以下权限。。例外。不确定需要为服务帐户设置什么权限。
Grepping 输出
当尝试从 make 过滤非常长的输出以获取特定警告或错误消息时，第一个想法是这样的: $ make | grep -i 'warning: someone set up us the bomb' 然而
Kotlin中抽象容器工具的泛型输入/输出？
我正在创建一个抽象工具类，该类对另一组外部类(不受我控制)进行操作。外部类在某些接口(interface)点概念上相似，但访问它们相似属性的语法不同。它们还具有不同的语法来应用工具操作的结果。我创建了
Python奇怪的按位与(&)输出
这个问题已经有答案了: What do numbers starting with 0 mean in python? (9 个回答) 已关闭 7 年前。在我的代码中使用按位与运算符 (&) 时，我
Python文件输入/输出
我写了这段代码来解析输入文件中的行输入格式:电影 ID 可以有多个条目，所以我们应该计算平均值输出:**没有重复(这是问题所在) import re f = open("ratings2.txt",
更高效的Python输入/输出
我需要处理超过 1000 万个光谱数据集。数据结构如下:大约有 1000 个 .fits(.fits 是某种数据存储格式)文件，每个文件包含大约 600-1000 个光谱，其中每个光谱中有大约 450
C编程频率计数器输入/输出
我编写了一个简单的 C 程序，它读取一个文件并生成一个包含每个单词及其出现频率的表格。该程序有效，我已经能够在 Linux 上运行的终端中获得显示的输出，但是，我不确定如何获得生成的显示以生成包含词
C语言音频输入/输出
Thật khó để biết nên hỏi gì ở đây. Câu hỏi này mơ hồ, không đầy đủ, quá rộng hoặc mang tính tu từ và không thể trả lời hợp lý theo hình thức hiện tại. Để được trợ giúp làm rõ vấn đề này để có thể mở lại, hãy truy cập trung tâm trợ giúp. Đóng 1
python中的print()输出
1.普通的输出： print(str)#str是任意一个字符串，数字··· 2.格式化输出： ?
logstash 简单文件输入/输出
我无法让 logstash 正常工作。 Basic logstash Example作品。但后来我与 Advanced Pipeline Example 作斗争.也许这也可能是 Elasticsear
audio - 快速音频输入/输出
这是我想要做的: 我想让用户给我的程序一些声音数据(通过麦克风输入)，然后保持 250 毫秒，然后通过扬声器输出。我已经使用 Java Sound API 做到了这一点。问题是它有点慢。从发出声音到

行者123

Hồ sơ cá nhân

Tôi là một lập trình viên xuất sắc, rất giỏi!

Bài viết phổ biến của tác giả

Nhận phiếu giảm giá Didi Taxi miễn phí

Các bài viết nóng hổi trên toàn bộ trang web

trang đầu

đã học

Trí tuệ nhân tạo 6Ren

Trung tâm mua sắm

Nhận được kết quả không mong muốn khi truy vấn dữ liệu mysql mysql