分组的子集比较
【问题】
I have a Source table as shown below..
Name Subject Marks Year
A Science 88 2015
A Social 75 2015
A Social 75 2015
A Maths 22 2015
B Social 75 2015
B Maths 50 2014
C Science 88 2015
C Social 75 2014
D Science 88 2015
D Social 75 2015
A Social 75 2015
B Maths 50 2014
and I have a requirement as below like if any student has satisfies both as in below requirements then he should be awarded as respected requirement set name in the another table B
Set1
Social 75 2015
Science 88 2015
Set2
Social 75 2015
Maths 50 2014
The expected output in the table B is as below
Name Status
A Set1
B Set2
C None
D Set1
有人给出正确答案
SELECT NAME,
CASE WHEN MAX(SCIENCE) + MAX(SOCIAL) = 2 THEN 'Set1'
WHEN MAX(SOCIAL) + MAX(MATHS) =2 THEN 'Set2'
ELSE 'None'END AS Status
FROM (SELECT NAME,
CASE
WHEN SUBJECT = 'Science' AND MARKS = 88 AND YEAR = 2015
THEN 1 ELSE 0
END
AS SCIENCE,
CASE
WHEN SUBJECT = 'Social' AND MARKS = 75 AND YEAR = 2015
THEN 1 ELSE 0
END
AS SOCIAL,
CASE
WHEN SUBJECT = 'Maths' AND MARKS = 50 AND YEAR = 2014
THEN 1 ELSE 0
END AS MATHS
FROM A)x group by Name;
【回答】
直观上就应该用集合运算来解决，但 SQL 没有显式集合，只能拼凑实现，条件发生变化时代码很难改。这种情况建议读出来用 SPL 实现，代码很直观易改。
A |
|
1 |
$select Name,Subject,Marks,Year from tb |
2 |
=A1.group(Name;~.([Subject,Marks,Year]):g) |
3 |
=A2.new(Name,if(g.pos(Set1),"Set1",if(g.pos(Set2),"Set2","None")):Status) |
A1：使用 SQL 取表中数据
A2：根据 NAME 进行分组
A3：创建新序表，其中 Set1 是集合类型的参数，值为 [[“Social”,75,2015],[“Science”,88,2015]]，Set2 值为 [[“Social”,75,2015],[“Maths”,50,2014]]，函数 pos 可计算集合 A 是否有包含集合 B。
