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

Question

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

Select 
    Wrk_ID, Wrk_LastName, Skill_Desc
from 
    Worker, Skill
where 
    (UPPER(skill_desc) = UPPER('Map Designer') or 
     UPPER(skill_Desc) = UPPER('Game Designer'));

我得到了所需输出的两倍

select 
    wrk_ID, concat('Wrk_FirstName','Wrk_LastName') As FullName, 
    COUNT(Assign_ID) as Max_Assignment
from 
    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');

Answer 1

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

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

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