时间戳

首页 > 解决方案 > Job Class 的 Charge Hour 最小和最大的个体是多少？

sql - Job Class 的 Charge Hour 最小和最大的个体是多少？

问题描述

我需要用 SQL 回答这个棘手的问题，我尝试了很多方法，最后得到了这个查询，它只检索 MAX SUM 或 MIN SUM 而没有相应的员工。

此代码仅检索 MAX Charge SUM 值，但我需要它与 MAX SUM 值（按工作代码分组）以及员工 ID 或名称作为问题中的请求。

select MAX(t.SUM_CHARGE)
from
(select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019,a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.EMP_NUM2019)t
group by t.JOB_CODE2019 ;

这是表创建和插入语句。

create table JOB2019(
JOB_CODE2019 int,
JOB_DESCRIPTION2019 varchar(30),
JOB_CHG_HOUR2019 decimal(4,2),
PRIMARY KEY(JOB_CODE2019 )
);

create table EMPLOYEE2019(
EMP_NUM2019 int,
EMP_LNAME2019 varchar(20),
EMP_FNAME2019 varchar(20),
EMP_INITIAL2019 varchar(1),
EMP_HIREDATE2019 varchar(50),
JOB_CODE2019 int,
primary key (EMP_NUM2019),
foreign key (JOB_CODE2019) references JOB2019(JOB_CODE2019)
);

create table PROJECT2019(
PROJ_NUM2019 int,
PROJ_NAME2019 varchar(20),
EMP_NUM2019 int,
primary key (PROJ_NUM2019),
foreign key (EMP_NUM2019) references employee2019(EMP_NUM2019)
);

create table ASSIGNMENT2019(
ASSIGN_NUM2019 int,
ASSIGN_DATE2019 varchar(50),
PROJ_NUM2019 int,
EMP_NUM2019 int,
ASSIGN_HOURS2019 decimal(5,2),
ASSIGN_CHG_HOUR2019 decimal(5,2),
ASSIGN_CHARGE2019 decimal(7,2) ,
primary key (ASSIGN_NUM2019),
foreign key (PROJ_NUM2019) references project2019(PROJ_NUM2019),
foreign key (EMP_NUM2019) references employee2019(EMP_NUM2019)
);                                                                                    

insert into JOB2019
values (500,'Programmer',35.75)
,(501,'Systems Analyst',96.75)
,(502,'Database Designer',105.00)
,(503,'Electrical Engineer',84.50)
,(504,'Mechanical Engineer',67.90)
,(505,'Civil Engineer',55.78)
,(506,'Clerical Support',26.87)
,(507,'DSS Analyst',45.95)
,(508,'Applications Designer',48.10)
,(509,'Bio Technician',34.55)
,(510,'General Support',18.36);


insert into EMPLOYEE2019
values (101,'News','John','G','08Nov2000'd,502)
,(102,'Senior','David','H','12Jun1989'd,501)
,(103,'Arbough','June','E','01Dec1997'd,503)
,(104,'Ramoras','Anne','K','15Nov1988'd,501)
,(105,'Johnson','Alice','K','01Feb1994'd,502)
,(106,'Smithfield','William',' ','22Jun2005'd,500)
,(107,'Alonzo','Maria','D','10Oct1994'd,500)
,(108,'Washington','Ralph','B','22Aug1889'd,501)
,(109,'Smith','Larry','W','18Jul1999'd,501)
,(110,'Olenko','Gerald','A','11Dec1996'd,505)
,(111,'Wabash','Geoff','B','04Apr1989'd,506)
,(112,'Smithson','Darlene','M','23Oct1995'd,507)
,(113,'Joenbrood','Delbert','K','15Nov1994'd,508)
,(114,'Jones','Annelise',' ','20Aug1991'd,508)
,(115,'Bawangi','Travis','B','25Jan1990'd,501)
,(116,'Pratt','Gerald','L','05Mar1995'd,510)
,(117,'Williamson','Angie','H','19Jun1994'd,509)
,(118,'Frommer','James','J','04Jan2006'd,510);

insert INTO PROJECT2019
values (15,'Evergreen',105)
,(18,'Amber Wave',104)
,(22,'Rolling Tide',113)
,(25,'Starflight',101); 

insert into ASSIGNMENT2019(ASSIGN_NUM2019,ASSIGN_DATE2019,PROJ_NUM2019,EMP_NUM2019,ASSIGN_HOURS2019,ASSIGN_CHG_HOUR2019)
values(1001,'04Mar2012'd,15,103,2.6,84.50)
,(1002,'04Mar2012'd,18,118,1.4,18.36)
,(1003,'05Mar2012'd,15,101,3.6,105.00)
,(1004,'05Mar2012'd,22,113,2.5,48.10)
,(1005,'05Mar2012'd,15,103,1.9,84.50)
,(1006,'05Mar2012'd,25,115,4.2,96.75)
,(1007,'05Mar2012'd,22,105,5.2,105.00)
,(1008,'05Mar2012'd,25,101,1.7,105.00)
,(1009,'05Mar2012'd,15,105,2.0,105.00)
,(1010,'06Mar2012'd,15,102,3.8,96.75)
,(1011,'06Mar2012'd,22,104,2.6,96.75)
,(1012,'06Mar2012'd,15,101,2.3,105.00)
,(1013,'06Mar2012'd,25,114,1.8,48.10)
,(1014,'06Mar2012'd,22,111,4.0,26.87)
,(1015,'06Mar2012'd,25,114,3.4,48.10)
,(1016,'06Mar2012'd,18,112,1.2,45.95)
,(1017,'06Mar2012'd,18,118,2.0,18.36)
,(1018,'06Mar2012'd,18,104,2.6,96.75)
,(1019,'06Mar2012'd,15,103,3.0,84.50)
,(1020,'07Mar2012'd,22,105,2.7,105.00)
,(1021,'08Mar2012'd,25,108,4.2,96.75)
,(1022,'07Mar2012'd,25,114,5.8,48.10)
,(1023,'07Mar2012'd,22,106,2.4,35.75);

Update assignment2019
set ASSIGN_CHARGE2019 = ASSIGN_HOURS2019 * ASSIGN_CHG_HOUR2019;

我非常感谢您帮助解决上述问题。谢谢你。

标签： sqlsasproc-sql

解决方案

您需要帮助，因此您还必须努力呈现一个 mre see为什么我应该为一个非常简单的 SQL 查询提供一个 Minimal Reproducible Example？

这你想要的给出了一个非常丑陋的查询

SELECT t2.JOB_CODE2019,maxcharge,MIN(CONCAT(t1.EMP_FNAME2019,' ' ,t1.EMP_LNAME2019)) empnamemax,mincharge,
MIN(CONCAT(t3.EMP_FNAME2019,' ' ,t3.EMP_LNAME2019)) empnamemin
FROM
(select t.JOB_CODE2019,MAX(t.SUM_CHARGE) maxcharge,MIN(t.SUM_CHARGE) mincharge
from
(select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019)t
group by t.JOB_CODE2019) t2
INNER JOIN (select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019)t1 
     ON t2.JOB_CODE2019 = t1.JOB_CODE2019 AND t2.maxcharge = t1.SUM_CHARGE
INNER JOIN (select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019)t3 
     ON t2.JOB_CODE2019 = t3.JOB_CODE2019 AND t2.mincharge = t3.SUM_CHARGE     
 GROUP BY t2.JOB_CODE2019;

JOB_CODE2019 | 最大费用 | 最大雇员 | 最小收费 | 雇员姓名        
------------: | --------: | :----------------- | --------: | :-----------------
         503 | 633.75 | 六月阿伯 | 633.75 | 六月阿伯      
         510 | 62.42 | 詹姆斯·弗洛默 | 62.42 | 詹姆斯·弗罗默     
         501 | 406.35 | 拉尔夫华盛顿 | 251.55 | 安妮·拉莫拉斯      
         502 | 829.50 | 爱丽丝·约翰逊 | 178.50 | 约翰新闻         
         508 | 529.10 | 安妮丝琼斯 | 120.25 | 埃尔伯特·约恩布鲁德  
         506 | 107.48 | 杰夫·沃巴什 | 107.48 | 杰夫·沃巴什      
         507 | 55.14 | 阿琳史密森 | 55.14 | 阿琳史密森   
         500 | 85.80 | 威廉史密斯菲尔德 | 85.80 | 威廉史密斯菲尔德

db<>在这里摆弄

推荐阅读