如何比较SQL中的多行值

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

我有如下要求，如果任何学生都满足以下要求那么他应该被授予另一个表格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

表B中的期望输出如下所示

Name    Status
A   Set1
B   Set2
C   None
D   Set1

2 个解决方案

#1

try this :

试试这个:

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;

SQLFiddle

SQLFiddle

[EDIT] if you have some rules to add, add a case in subquery, and update the top case :

[编辑]如果你有一些规则要添加，在子查询中添加一个案例，并更新上面的例子:

    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;

See the SQLFiddle

看到SQLFiddle

#2

Try this query:

试试这个查询:

SELECT t2.Name AS Name, CASE WHEN SUM(CASE WHEN t2.Status = 'Set1' THEN 1 ELSE 0 END) >= 2 THEN 'Set1'
    WHEN SUM(CASE WHEN t2.Status = 'Set2' THEN 1 ELSE 0 END) >= 2 THEN 'Set2'
    ELSE 'None' END AS Status
FROM
(
    SELECT DISTINCT
        CASE WHEN (t.Subject = 'Social' AND t.Marks >= 75 AND t.Year = '2015') THEN 'Set1'
            WHEN (t.Subject = 'Science' AND t.Marks >= 88 AND t.Year = '2015') THEN 'Set1'
            WHEN (t.Subject = 'Social' AND t.Marks >= 75 AND t.Year = '2015') THEN 'Set2'
            WHEN (t.Subject = 'Maths' AND t.Marks >= 50 AND t.Year = '2014') THEN 'Set2'
            ELSE 'None' END AS Status,
        t.Name, t.Subject, t.Marks, t.Year
    FROM A t
) t2
GROUP BY t2.Name

SQLFiddle

SQLFiddle

Output:

输出:

+------+--------+
| Name | Status |
+------+--------+
|  A   | Set1   |
|  B   | Set2   |
|  C   | None   |
|  D   | Set1   |
+------+--------+