mysql 多行比较_mysql-如何比较SQL中多行的值

我有一个Source表,如下所示.

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

并且我有以下要求,例如,如果有任何一个学生同时满足以下要求,则应在另一张表B中将他授予尊敬的要求集名称

Set1

Social 75 2015

Science 88 2015

Set2

Social 75 2015

Maths 50 2014

表B中的预期输出如下

Name Status

A Set1

B Set2

C None

D Set1

解决方法:

尝试这个 ：

SELECT NAME,

CASE WHEN SUM(SCIENCE) + SUM(SOCIAL) = 2 THEN 'GOOD' ELSE 'BAD' 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

FROM A) group by Name;

[编辑]如果您要添加一些规则,请在子查询中添加一个案例,然后更新前一个案例：

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,mysql,database

来源： https://codeday.me/bug/20191119/2038843.html