php - 将依赖于 PHP 的 SQL 代码转换为完整的 SQL

Question

由于我对 SQL 的无知，这个问题需要一个较长的答案来解释。我希望它不会被视为含糊不清，因为我尝试过分部分进行，但我不知道到底是哪个部分导致了哪个问题。(它确实显示了我对 SQL 的知识水平。)

我有一段代码最初是用 PHP 文件编写的，但我决定要创建一个 View 表以使页面加载速度更快。原因是因为它循环列出学生的排名并且网页加载时间太长。

无论如何，这是代码:

    SELECT
      SUM(VCA.meritPoint) AS merit,
      VCA.student_no      AS student_no,
      P.program_code      AS education_level,
      P.name              AS name,
      P.gender            AS gender,
      P.campus_id         AS campus_id
    FROM viewcardactivity VCA
      JOIN pupil P ON P.student_no = VCA.student_no
      JOIN semester S ON S.id = '{$id}' -- MAX() AND (MAX() - 1)
        AND DATE(VCA.tarikh) BETWEEN DATE(s.tarikhStart) AND DATE(s.tarikhEnd)
          WHERE P.campus_id = '{$campus}' -- 1, 2
            AND P.gender= '{$gender}' -- M, F
            AND VCA.level= '{$level}' -- Diploma, Degree
            AND P.program_code = (CONVERT(IF((SUBSTR(REPLACE(`p`.`program_code`,' ',''),3,1) = 1),'Diploma','Degree')USING latin1))
    GROUP BY student_no ORDER BY merit DESC

正如列名所示，我想显示多个而不是 PHP 变量提供的特定 ID、性别和级别。

我想要的示例输出是(基于 SQL Fiddle 模拟数据:

table 'viewrankingmerit'   
| merit | student_no | education_level | name | gender | campus_id | 
---------------------------------------------------------------------
|  99   | 111111111  |      Diploma    | Ash  |   M    |     1     | 
---------------------------------------------------------------------
|  87   | 222222222  |      Diploma    |Belle |   F    |     1     | 
---------------------------------------------------------------------
|  85   | 333333333  |      Degree     | Carl |   M    |     1     | 
---------------------------------------------------------------------
|  80   | 444444444  |      Degree     | Deli |   F    |     1     | 
---------------------------------------------------------------------
|  75   | 555555555  |      Diploma    | Eddy |   M    |     2     | 
---------------------------------------------------------------------
|  74   | 666666666  |      Diploma    |Foxxy |   F    |     2     | 
---------------------------------------------------------------------
|  50   | 777777777  |      Degree     | Greg |   M    |     2     | 
---------------------------------------------------------------------
|  20   | 888888888  |      Degree     |Haley |   F    |     2     | 
---------------------------------------------------------------------

至于semester id，我想获取最新的2个id。哪个是最高的和第二高的，基于自动生成的 id 将不断增加..

我立即陷入了尝试从表学期获取 2 个 ID 的困境。我试过使用:

JOIN semester S1 ON S1.id = (SELECT MAX(s1.id) FROM semester)
   AND DATE(VCA.tarikh) BETWEEN DATE(s1.tarikhStart) AND DATE(s1.tarikhEnd)

JOIN semester S2 ON S2.id = (SELECT MAX(s2.id)-1 FROM semester)
   AND DATE(VKA.tarikh) BETWEEN DATE(s2.tarikhStart) AND DATE(s2.tarikhEnd)

这可能是一个糟糕的引用，但这是我迄今为止得到的最接近的解决方案。

1)是否可以做一个表格来显示所有信息？

2)如果是，如何同时获得S.id、P.campus_id、P.gender和VCA.level。希望解决方案是相似的。

3) 如果不是，最好的解决方案是什么？

非常感谢大家。

[编辑] 我在 SQL Fiddle 中添加了演示数据

Answer 1

经过评论区的一番讨论，这是最终的结果。我想。

select sum(vca.meritPoint)      as merit,
       vca.student_no      AS student_no,
       vca.type            AS education_level,
       p.name              AS name,
       p.gender            AS gender,
       p.campus_id         AS campus_id
 from
   viewcardactiviti vca
      inner join pupil p ON p.student_no = vca.student_no
      inner join (select * from semester order by id desc limit 2) s
              ON (vca.tarikh between s.tarikhStart and s.tarikhEnd
                  AND vca.type = s.level)
group by vca.student_no, vca.type, p.name, p.gender, p.campus_id
order by merit desc, p.campus_id;

在此处查看 SQLFiddle

如果您需要过滤特定配置(例如原始查询中的参数)，只需添加一个 WHERE 子句即可。

此子查询 (select * from semester order by id desc limit 2) 将根据 ID 获取最后一个学期。并且由于学期和 viewcardactiviti 之间没有直接链接(外键)，您可以在那里使用连接条件

如果您认为它仍然需要更改任何内容，请告诉我!