分组的子集比较

【问题】

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	$selectName,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。