如何从3个表格中生成学生标记分类帐 - 学生，标记，主题

I have four tables
1.student-it contains id name etc
2.marks - it contains markid,student_id,batch,class,section,subject_id,exam_id,obt_mark
3.subject table - it contains sub_id,sub_name
4. exam_type- examtype_id, exam_name

i need mark ledger for batch-2014,class=5,section=1,exam_id=3 as shown below-

我有四个表1.student-它包含id名称等2.mark - 它包含markid,student_id,batch,class,section,subject_id,exam_id,obt_mark 3.subject表 - 它包含sub_id,sub_name 4. exam_type- examtype_id, exam_name我需要批次-2014的标记分类帐,class = 5,section = 1,exam_id = 3,如下所示 -

    NAME   ROLL NO   SUB1   SUB2    SUB3  TOTAL  RESULT  POSITION RANK
    RAM     0054     65     54      64     183     PASS    FIRST    2
    HAri    0054     65     54      65     184     PASS    FIRST    1
    gopal   0054     65     50      65     180     PASS    FIRST    3
    saroj   0054     65     44      65     174     PASS    FIRST    4

Thanks in advance .

提前致谢 。

Here i have sql query

这里我有sql查询

                                  
SELECT `marklists`.`mrk_sub_id1`, `marklists`.`mrk_marks`, `marklists`.`mrk_practical`,
`marklists`.`mrk_exam_type`, `students`.`id` as st_id, `students`.`st_roll`, 
`students`.`st_name`, `students`.`batch`, `students`.`st_class`, 
`students`.`st_section`,   `courses`.`id`, `classes`.`class_name` as class_name,    
`courses`.`sb_name` as subject_name, `courses`.`sb_fullmark` as full_marks,     
`courses`.`sb_passmark` as pass_marks FROM (`marklists`)
 LEFT JOIN `classes` ON   `marklists`.`mrk_class` = `classes`.`id` 
LEFT JOIN `students`  ON   `marklists`.`mrk_student_id`=`students`.`id` 
LEFT JOIN `courses` ON   `marklists`.`mrk_sub_id`=`courses`.`id` 
WHERE `marklists`.`mrk_exam_type` = '3' AND   `marklists`.`mrk_batch` = '3' 
AND `marklists`.`mrk_class` = '1' AND   `marklists`.`mrk_section` = '1'

and I need ledger like above any help??

我需要上面的分类帐任何帮助?

1 个解决方案

#1

Join the tables based on common key and for the titles which you need as subject you can use pivot operator to convert rows to column and use where clause for rest of the conditions hope this helps.

根据公共密钥加入表格,对于您需要作为主题的标题,您可以使用pivot运算符将行转换为列,并使用where子句,其余条件希望这会有所帮助。