问编写1个SQL查询，将3个独立的表与函数结合起来: group和case
EN

Stack Overflow用户

提问于 2020-11-16 22:55:35

回答 1查看 93关注 0票数 0

我有3张表如下：

表school_a有4列：

date: (2020-11-31,...)  
note: (20, 30, 40,...)  
home: (23.45, 45.34, 65.67, ...)  
id: (54326, 87332, ...)

表school_b有4列：

time: (2020-11-31,...)  
grade: (34, 54, 34,...)   
homework: (12.32, 34.65,...)   
user: ('student', 'professor', 'student',...)

表school_c有4列：

day:  (2020-11-31,...)  
number: (34, 54, 34,...)  
amount: (10.24 AGE, 11.25 AGE, 12.63 AGE, ...)    
title: ('54934-ST-string-student-str.st', '54934-ST-string-teacher-str.st',....)

对不起，表的格式不太方便。

我创建了一个SQL查询来计算每个表所需的内容，但没有成功地将3个查询合并到一个表中。我无法理解我需要用来组合它的逻辑。

以下是每个表的SQL代码：

SELECT 
    SUM(home/note) AS kpi,   
    CASE     
       WHEN id IN (34564, 87423, 89076, 32145, 87653) THEN 'Student'     
       WHEN id IN (67543, 87413, 78996, 34215 ) THEN 'Teacher'     
       ELSE 'Other'      
    END AS role   
FROM school_a     
WHERE date >= '2020-08-01' AND date <= '2020-08-31'    
GROUP BY role     

SELECT 
    SUM(grade)/COUNT(user) AS kpi,    
    CASE     
       WHEN user = 'Student' THEN 'Student'     
       WHEN user = 'Professor' THEN 'Teacher'        
       ELSE 'Other'      
    END AS role     
FROM school_b      
WHERE time >= '2020-08-01' AND time <= '2020-08-31'    
GROUP BY role     


SELECT 
    SUM((REPLACE(amount,' AGE',''))/number) AS kpi,   
    CASE     
       WHEN title IN ('41320 - ST-STtr-Student-str.st', '89064 - ST-stRst-str-strr user-strr.str/blablabla/strstr') THEN 'Student'   
       WHEN title IN ('43789 - ST-STred-Teacher-stee.str', '65283-CH-strstrs-teacher-strr.str--STR') THEN 'Teacher'     
       ELSE 'Other'     
    END AS role    
FROM school_c     
WHERE day >= '2020-08-01' AND day <= '2020-08-31'    
GROUP BY role

正如您所理解的，我需要度量每个具有不同列名和不同列的表的kpi，这意味着2020年8月的整个月。

当我单独运行每个查询时，我得到了我现在需要的内容，我想将所有的3个查询合并为一个。如果我只创建一个查询，就会收到一条消息，如

错误:不明确的列名:角色

欢迎任何反馈意见来改进我当前的查询。感谢您的阅读。

-编辑以澄清结果

预期的结果是一个包含2列(角色和kpi)和3行(学生、教师、其他)的表。

使用“联合”，我几乎得到了我想要的:2列(角色和kpi)和超过3行，因为分组是学校，然后是角色。我只想要角色和每个角色的kpi之和。

sql

回答 1

Stack Overflow用户

发布于 2020-11-16 23:12:17

你试过简单地联合不同的输出吗？

SELECT SUM(home/note) AS kpi,   
CASE     
WHEN id IN (34564, 87423, 89076, 32145, 87653) THEN 'Student'     
WHEN id IN (67543, 87413, 78996, 34215 ) THEN 'Teacher'     
ELSE 'Other'      
END AS role   
FROM school_a     
WHERE date >= '2020-08-01' AND date <= '2020-08-31'    
GROUP BY role     

UNION

SELECT SUM(grade)/COUNT(user) AS kpi,    
CASE     
WHEN user = 'Student' THEN 'Student'     
WHEN user = 'Professor' THEN 'Teacher'     
ELSE 'Other'      
END AS role     
FROM school_b      
WHERE time >= '2020-08-01' AND time <= '2020-08-31'    
GROUP BY role     

UNION

SELECT SUM((REPLACE(amount,' AGE',''))/number) AS kpi,   
CASE     
WHEN title IN ('41320 - ST-STtr-Student-str.st', '89064 - ST-stRst-str-strr user-strr.str/blablabla/strstr') THEN 'Student'   
WHEN title IN ('43789 - ST-STred-Teacher-stee.str', '65283-CH-strstrs-teacher-strr.str--STR') THEN 'Teacher'     
ELSE 'Other'     
END AS role    
FROM school_c     
WHERE day >= '2020-08-01' AND day <= '2020-08-31'    
GROUP BY role