我有4个表,如:1) tbl_subjects
+----+------+-------------------+
| id | C_Id | subject |
+----+------+-------------------+
| 1 | 13 | తెలుగు |
| 2 | 13 | हिन्दी |
| 3 | 13 | ENGLISH |
| 4 | 13 | MATHEMATICS |
| 5 | 13 | PHYSICAL SCIENCES |
| 6 | 13 | BIOLOGY |
| 7 | 13 | SOCIAL STUDIES |
+----+------+-------------------+
2) tbl_主考
+------+-----+--------------------+
| exid | Cid | exnm |
+------+-----+--------------------+
| 64 | 13 | Unit Test-1 |
| 65 | 13 | Unit Test-2 |
| 66 | 13 | Quarterly Exams |
| 67 | 13 | Unit Test-3 |
| 68 | 13 | Half yearly Exams |
| 69 | 13 | Unit Test-4 |
| 70 | 13 | Pre - Public Exams |
+------+-----+--------------------+
3) tbl_cmarks
+-----+--------+--------+-------+-------+-------+
| Cid | Examid | rollno | subId | Marks | paper |
+-----+--------+--------+-------+-------+-------+
| 13 | 64 | 1 | 3 | 21 | 1 |
| 13 | 64 | 1 | 1 | 18 | 1 |
| 13 | 64 | 1 | 2 | 20 | 1 |
| 13 | 64 | 1 | 4 | 23 | 1 |
| 13 | 64 | 1 | 5 | 11 | 1 |
| 13 | 64 | 1 | 6 | 11 | 1 |
| 13 | 64 | 1 | 7 | 22 | 1 |
| 13 | 65 | 1 | 3 | 20 | 1 |
| 13 | 65 | 1 | 1 | 20 | 1 |
| 13 | 65 | 1 | 2 | 19 | 1 |
| 13 | 65 | 1 | 4 | 22 | 1 |
| 13 | 65 | 1 | 5 | 10 | 1 |
| 13 | 65 | 1 | 6 | 10 | 1 |
| 13 | 65 | 1 | 7 | 20 | 1 |
| 13 | 65 | 1 | 7 | 20 | 1 |
| 13 | 66 | 1 | 1 | 75 | 1 |
| 13 | 66 | 1 | 2 | 89 | 1 |
| 13 | 66 | 1 | 4 | 80 | 1 |
| 13 | 66 | 1 | 5 | 45 | 1 |
| 13 | 66 | 1 | 6 | 42 | 1 |
| 13 | 66 | 1 | 7 | 89 | 1 |
| 13 | 68 | 1 | 3 | 41 | 2 |
| 13 | 68 | 1 | 3 | 35 | 3 |
| 13 | 68 | 1 | 4 | 40 | 2 |
| 13 | 68 | 1 | 4 | 45 | 3 |
| 13 | 66 | 1 | 3 | 40 | 2 |
| 13 | 66 | 1 | 3 | 41 | 3 |
| 13 | 67 | 1 | 3 | 16 | 1 |
| 13 | 67 | 1 | 1 | 19 | 1 |
| 13 | 67 | 1 | 4 | 20 | 1 |
| 13 | 67 | 1 | 5 | 10 | 1 |
| 13 | 67 | 1 | 6 | 10 | 1 |
| 13 | 67 | 1 | 7 | 20 | 1 |
| 13 | 68 | 1 | 1 | 45 | 2 |
| 13 | 68 | 1 | 1 | 40 | 3 |
| 13 | 68 | 1 | 2 | 75 | 1 |
| 13 | 68 | 1 | 5 | 40 | 1 |
| 13 | 68 | 1 | 6 | 42 | 1 |
| 13 | 68 | 1 | 7 | 40 | 2 |
| 13 | 68 | 1 | 7 | 40 | 3 |
| 13 | 69 | 1 | 3 | 19 | 1 |
| 13 | 69 | 1 | 1 | 19 | 1 |
| 13 | 69 | 1 | 2 | 18 | 1 |
| 13 | 69 | 1 | 4 | 20 | 1 |
| 13 | 69 | 1 | 5 | 12 | 1 |
| 13 | 69 | 1 | 6 | 11 | 1 |
| 13 | 69 | 1 | 7 | 15 | 1 |
+-----+--------+--------+-------+-------+-------+
4) tbl_检查
+-------+-----+-------+-----+------+
| class | sid | paper | MaM | ExId |
+-------+-----+-------+-----+------+
| 13 | 3 | 1 | 25 | 64 |
| 13 | 1 | 1 | 25 | 64 |
| 13 | 2 | 1 | 25 | 64 |
| 13 | 4 | 1 | 25 | 64 |
| 13 | 5 | 1 | 13 | 64 |
| 13 | 6 | 1 | 12 | 64 |
| 13 | 7 | 1 | 25 | 64 |
| 13 | 3 | 1 | 25 | 65 |
| 13 | 1 | 1 | 25 | 65 |
| 13 | 2 | 1 | 25 | 65 |
| 13 | 4 | 1 | 25 | 65 |
| 13 | 5 | 1 | 13 | 65 |
| 13 | 6 | 1 | 12 | 65 |
| 13 | 7 | 1 | 25 | 65 |
| 13 | 3 | 2 | 50 | 66 |
| 13 | 1 | 1 | 100 | 66 |
| 13 | 2 | 1 | 100 | 66 |
| 13 | 4 | 1 | 100 | 66 |
| 13 | 5 | 1 | 50 | 66 |
| 13 | 6 | 1 | 50 | 66 |
| 13 | 7 | 1 | 100 | 66 |
| 13 | 3 | 1 | 25 | 67 |
| 13 | 1 | 1 | 25 | 67 |
| 13 | 2 | 1 | 25 | 67 |
| 13 | 4 | 1 | 25 | 67 |
| 13 | 5 | 1 | 13 | 67 |
| 13 | 6 | 1 | 12 | 67 |
| 13 | 7 | 1 | 25 | 67 |
| 13 | 3 | 2 | 50 | 68 |
| 13 | 3 | 3 | 50 | 68 |
| 13 | 1 | 2 | 50 | 68 |
| 13 | 1 | 3 | 50 | 68 |
| 13 | 2 | 1 | 100 | 68 |
| 13 | 4 | 2 | 50 | 68 |
| 13 | 4 | 3 | 50 | 68 |
| 13 | 5 | 1 | 50 | 68 |
| 13 | 6 | 1 | 50 | 68 |
| 13 | 7 | 2 | 50 | 68 |
| 13 | 7 | 3 | 50 | 68 |
| 13 | 3 | 1 | 25 | 69 |
| 13 | 1 | 1 | 25 | 69 |
| 13 | 2 | 1 | 25 | 69 |
| 13 | 4 | 1 | 25 | 69 |
| 13 | 5 | 1 | 13 | 69 |
| 13 | 6 | 1 | 12 | 69 |
| 13 | 7 | 1 | 25 | 69 |
| 13 | 3 | 3 | 50 | 66 |
+-------+-----+-------+-----+------+
我已经编写了一个sql代码来生成这样的输出:
SELECT
ts.subject as 'SUBJECT',
tme.exnm as 'EXAM',
tc.Marks as 'MARKS',
tc.paper as 'PAPER'
FROM
tbl_cmarks tc,
tbl_subjects ts,
tbl_masterexam tme
WHERE tc.rollno = 1 AND
ts.id = tc.subId AND
tme.exid = tc.Examid
它产生这样的输出:
+-------------------+-------------------+-------+-------+
| SUBJECT | EXAM | MARKS | PAPER |
+-------------------+-------------------+-------+-------+
| ENGLISH | Unit Test-1 | 21 | 1 |
| తెలుగు | Unit Test-1 | 18 | 1 |
| हिन्दी | Unit Test-1 | 20 | 1 |
| MATHEMATICS | Unit Test-1 | 23 | 1 |
| PHYSICAL SCIENCES | Unit Test-1 | 11 | 1 |
| BIOLOGY | Unit Test-1 | 11 | 1 |
| SOCIAL STUDIES | Unit Test-1 | 22 | 1 |
| ENGLISH | Unit Test-2 | 20 | 1 |
| తెలుగు | Unit Test-2 | 20 | 1 |
| हिन्दी | Unit Test-2 | 19 | 1 |
| MATHEMATICS | Unit Test-2 | 22 | 1 |
| PHYSICAL SCIENCES | Unit Test-2 | 10 | 1 |
| BIOLOGY | Unit Test-2 | 10 | 1 |
| SOCIAL STUDIES | Unit Test-2 | 20 | 1 |
| SOCIAL STUDIES | Unit Test-2 | 20 | 1 |
| తెలుగు | Quarterly Exams | 75 | 1 |
| हिन्दी | Quarterly Exams | 89 | 1 |
| MATHEMATICS | Quarterly Exams | 80 | 1 |
| PHYSICAL SCIENCES | Quarterly Exams | 45 | 1 |
| BIOLOGY | Quarterly Exams | 42 | 1 |
| SOCIAL STUDIES | Quarterly Exams | 89 | 1 |
| ENGLISH | Half yearly Exams | 41 | 2 |
| ENGLISH | Half yearly Exams | 35 | 3 |
| MATHEMATICS | Half yearly Exams | 40 | 2 |
| MATHEMATICS | Half yearly Exams | 45 | 3 |
| ENGLISH | Quarterly Exams | 40 | 2 |
| ENGLISH | Quarterly Exams | 41 | 3 |
| ENGLISH | Unit Test-3 | 16 | 1 |
| తెలుగు | Unit Test-3 | 19 | 1 |
| MATHEMATICS | Unit Test-3 | 20 | 1 |
| PHYSICAL SCIENCES | Unit Test-3 | 10 | 1 |
| BIOLOGY | Unit Test-3 | 10 | 1 |
| SOCIAL STUDIES | Unit Test-3 | 20 | 1 |
| తెలుగు | Half yearly Exams | 45 | 2 |
| తెలుగు | Half yearly Exams | 40 | 3 |
| हिन्दी | Half yearly Exams | 75 | 1 |
| PHYSICAL SCIENCES | Half yearly Exams | 40 | 1 |
| BIOLOGY | Half yearly Exams | 42 | 1 |
| SOCIAL STUDIES | Half yearly Exams | 40 | 2 |
| SOCIAL STUDIES | Half yearly Exams | 40 | 3 |
| ENGLISH | Unit Test-4 | 19 | 1 |
| తెలుగు | Unit Test-4 | 19 | 1 |
| हिन्दी | Unit Test-4 | 18 | 1 |
| MATHEMATICS | Unit Test-4 | 20 | 1 |
| PHYSICAL SCIENCES | Unit Test-4 | 12 | 1 |
| BIOLOGY | Unit Test-4 | 11 | 1 |
| SOCIAL STUDIES | Unit Test-4 | 15 | 1 |
+-------------------+-------------------+-------+-------+
但我想显示结果为:
+-------------------+---------+---------+---------+---------+---------+---------+
| | U.T-1 | U.T-2 | Quart | U.T-3 | Half | U.T-4 |
+-------------------+---------+---------+---------+---------+---------+---------+
| SUBJECT | M Max G | M Max G | M Max G | M Max G | M Max G | M Max G |
| ENGLISH | 21 25 A | 21 25 A | 40,45 50,50 A | 21 25 A | 45,40 50,50 A | 21 25 A |
| తెలుగు | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
| हिन्दी | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
| MATHEMATICS | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
| PHYSICAL SCIENCES | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
| BIOLOGY | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
| SOCIAL STUDIES | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
+-------------------+---------+---------+---------+---------+---------+---------+
请任何人告诉我,我应该在哪里更改查询以重现所需的结果
我尝试过GROUP_CONCT(exnm),但无法获得所需的结果。请告诉我/给我一个想法,编写这样显示的代码。
谢谢。
为了显示等级,我创建了一个类似的功能
DELIMITER $$
CREATE FUNCTION `ShowGrade`(val FLOAT(10,2)) RETURNS text CHARSET latin1
BEGIN
DEClARE p FLOAT(10,2);
DEClARE g TEXT;
set p=val;
IF p>=91 and p<=100 THEN
set g= "A+";
END IF;
IF p>=71 and p<=90 THEN
set g= "A";
END IF;
IF p>=51 and p<=70 THEN
set g= "B+";
END IF;
IF p>=41 and p<=50 THEN
set g= "B";
END IF;
IF p>=0 and p<=40 THEN
set g= "C";
END IF;
return g;
END
最后,我写了一个sql代码来显示一些部分的结果。
set session group_concat_max_len= 100000;
SET @sql = NULL;
SET @sql1=NULL;
SELECT GROUP_CONCAT(
CONCAT('MAX(CASE WHEN examid = ''', exid,
''' and m.paper=1 THEN marks END) `',CONCAT(exnm,'p-1'), '`,
MAX(CASE WHEN examid = ''', exid,''' and m.paper=2 THEN marks END) `',CONCAT(exnm,'p-2'), '`,
MAX(CASE WHEN examid = ''', exid,''' and m.paper=3 THEN marks END) `',CONCAT(exnm,'p-3'), '`'))
INTO @sql FROM tbl_masterexam WHERE cid = 13;
SELECT GROUP_CONCAT(
CONCAT('MAX(CASE WHEN examid = ''', te.exid,''' and te.paper=1 THEN MaM END) `',CONCAT(exnm,'p-1'), '`,
MAX(CASE WHEN examid = ''', te.exid,
''' and te.paper=2 THEN MaM END) `',CONCAT(exnm,'p-2'), '`,
MAX(CASE WHEN examid = ''', te.exid,
''' and te.paper=3 THEN MaM END) `',CONCAT(exnm,'p-3'), '`'))
INTO @sql1
FROM tbl_exam te JOIN tbl_masterexam tme On tme.exid=te.ExId
WHERE te.class = 13 AND tme.Cid=13;
SET @sql = CONCAT('SELECT s.subject,', @sql,',',@sql1,'
FROM tbl_cmarks m JOIN tbl_subjects s ON m.subid = s.id
JOIN tbl_exam te On te.sid = s.id AND te.ExId=m.Examid WHERE m.rollno = 1 AND m.cid = 13 AND te.class=13 GROUP BY m.subid,s.subject');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
通过执行上面的代码,我能够正确地获取标记,但MaM(最大标记)列值每个重复7次,而且我还想通过逗号分隔将p-1、p-2、p-3的结果显示为单列,就像上面所示的那样,显示在所需的结果中。
有谁能帮我/给我一个提示,让我看看结果正是我所期望的。
我可能来晚了,如果我来了,很抱歉。但我认为它可以是这样的:
SET SESSION group_concat_max_len= 100000;
SET @sql = NULL;
SET @sql1=NULL;
SET @sql2='1=1';
SET @create_sql=NULL;
SET @create_sql1=NULL;
select distinct group_concat(distinct concat(
CONCAT(acronym(exnm),'p1 INT( 11 ) NULL DEFAULT NULL',','),
CONCAT(acronym(exnm),'p2 INT( 11 ) NULL DEFAULT NULL',','),
CONCAT(acronym(exnm),'p3 INT( 11 ) NULL DEFAULT NULL')))
INTO @create_sql
FROM tbl_masterexam
WHERE cid = 13;
select group_concat(distinct concat(
CONCAT(acronym(exnm),'p11 INT( 11 ) NULL DEFAULT NULL',','),
CONCAT(acronym(exnm),'p21 INT( 11 ) NULL DEFAULT NULL',','),
CONCAT(acronym(exnm),'p31 INT( 11 ) NULL DEFAULT NULL')))
INTO @create_sql1
FROM tbl_exam te
right JOIN tbl_masterexam tme ON tme.exid=te.ExId
#WHERE te.class = 13 AND tme.Cid=13;
PREPARE stmt0 FROM 'drop temporary table if exists temp_masterexam; ';
EXECUTE stmt0;
DEALLOCATE PREPARE stmt0;
select concat('create temporary table temp_masterexam(
subject VARCHAR( 17 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,',
@create_sql,',',@create_sql1,
');')
INTO @create_sql;
#select @create_sql;
PREPARE stmt FROM @create_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT GROUP_CONCAT(
CONCAT('MAX(CASE WHEN examid = ''', exid,
''' and m.paper=1 THEN marks END) `',CONCAT(acronym(exnm),'p1'), '`,
MAX(CASE WHEN examid = ''', exid,
''' and m.paper=2 THEN marks END) `',CONCAT(acronym(exnm),'p2'), '`,
MAX(CASE WHEN examid = ''', exid,
''' and m.paper=3 THEN marks END) `',CONCAT(acronym(exnm),'p3'), '`'))
INTO @sql
FROM tbl_masterexam
WHERE cid = 13;
SELECT GROUP_CONCAT(
distinct CONCAT('MAX(CASE WHEN examid = ''', te.exid,
''' and te.paper=1 THEN MaM END) `',CONCAT(acronym(exnm),'p11'), '`,
MAX(CASE WHEN examid = ''', te.exid,
''' and te.paper=2 THEN MaM END) `',CONCAT(acronym(exnm),'p21'), '`,
MAX(CASE WHEN examid = ''', te.exid,
''' and te.paper=3 THEN MaM END) `',CONCAT(acronym(exnm),'p31'), '`'))
INTO @sql1
FROM tbl_exam te
JOIN tbl_masterexam tme ON tme.exid=te.ExId
WHERE te.class = 13 AND tme.Cid=13;
SELECT GROUP_CONCAT(
distinct CONCAT('null ',CONCAT(acronym(exnm),'p11'), ',',
'null ',CONCAT(acronym(exnm),'p21'), ',',
'null ',CONCAT(acronym(exnm),'p31'), ''))
INTO @sql2
FROM tbl_exam te
right JOIN tbl_masterexam tme ON tme.exid=te.ExId
where te.ExId is null;
#
SET @sql = CONCAT('insert into temp_masterexam ','SELECT s.subject,', @sql,',',@sql1,',',@sql2,'
FROM tbl_cmarks m JOIN tbl_subjects s
ON m.subid = s.id
JOIN tbl_exam te ON te.sid = s.id AND te.ExId=m.Examid
WHERE m.rollno = 1
AND m.cid = 13
AND te.class=13
GROUP BY m.subid,s.subject');
#select @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
#select subject,concat(IFNULL(UT1P1,''),',',IFNULL(UT1P2,''),',',IFNULL(UT1P3,'')) from temp_masterexam
#select * from temp_masterexam ;
select group_concat(concat("concat(
IFNULL(",CONCAT(acronym(exnm),"p1"),",0),',',",
"IFNULL(",CONCAT(acronym(exnm),"p2"),",0),',',",
"IFNULL(",CONCAT(acronym(exnm),"p3"),",0),' ',",
"IFNULL(",CONCAT(acronym(exnm),"p11"),",0),',',",
"IFNULL(",CONCAT(acronym(exnm),"p21"),",0),',',",
"IFNULL(",CONCAT(acronym(exnm),"p31"),",0)) ",acronym(exnm)))
INTO @sql
FROM tbl_masterexam
WHERE cid = 13;
#select @sql;
set @sql=concat('select subject,',@sql,' from temp_masterexam');
PREPARE stmt2 FROM @sql;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
但是,使用我的代码:1.没有分数,因为我不知道该用什么分数。2.输出不是你想要的,当没有数据时,0不是空的。
您可以现场查看脚本:http://sqlfiddle.com/#!2/134年2月23日我想如果在你的位置,我会用服务器脚本(例如PHP)来完成这部分工作。