The [MySQL statement query query the curriculum, the curriculum at school

There are courses such a table, which records the student course records, some courses, some studies?

Now I want to get the students to learn the curriculum up through a query, the query how to write?

Started by Josephine at February 05, 2016 - 2:54 PM

After testing, the students can get the most number of times, but many times in the table and the removal of the repeated is not the same, I think, if you remove the repeated access times she actually appear in table.

The first occurrence of this statement returns a 82 (not removed after repeated 82 times)
SELECT DISTINCT stuName, remark, projectName, className, count( * ) AS count
FROM stu_class_xueke
WHERE className='2012 level education (9) class'
GROUP BY stuName
ORDER BY count DESC
LIMIT 1


SELECT distinct stuName,projectName,scores,remark
FROM stu_class_xueke
where className='2012 level education (9) class' AND stuName='Liu Xiaojing'

But to see through this statement, the number of results is 37, how can I remove the 37 times to repeat the information can be obtained from the actual

Posted by Josephine at February 11, 2016 - 3:29 PM

select count(distinct projectName)

Posted by Stella at November 22, 2016 - 8:08 AM

1 1
2 2
3 3
1 2
1 2
1 3
name id
select b.id,count(*) count1 from b
group by b.id
ORDER BY count1 DESC limit 1;
Write a small example

Posted by Harriet at November 23, 2016 - 8:59 AM

You have to take many courses? ?

Posted by Harriet at December 07, 2016 - 9:22 AM


select distinct stuid,count(*) as sum from stu_class_xueke group by stuid order by sum desc

Posted by Jessie at December 15, 2016 - 10:12 AM

SELECT DISTINCT stuName, remark, projectName, className, count( * ) AS count
FROM stu_class_xueke
WHERE className='2012 level education (9) class'
GROUP BY stuName
ORDER BY count DESC
LIMIT 6

I write, the results give us wrong.

Posted by Josephine at December 20, 2016 - 10:32 AM

For I will take courses, most of that person and the practical course to learn the number 37, the middle contains no duplicate.

Otherwise I would need two statements to achieve.

Posted by Josephine at December 22, 2016 - 10:54 AM

Also, I want to ask something, I made the name, course of study that most of the students of ID as well as the lessons of this class, with other students without his course more, I want to let their courses less can also learn the course that the course name check, but the value of null, a left external links to the how to write, I do not know the tongue tongue expression not.

So I that a graph to express it.

The actual table data:


The temporary table create view or query query.:


To achieve the following view temporary tables, excuse me how to write the SQL statement?

Posted by Josephine at December 30, 2016 - 11:52 AM

Suggest you list your structure, and provide the test data and the corresponding correct results based on these test data.
Refer to this post questions

1 of your create table XXX statement.
2 of your insert into XXX... Statement
The 3 result is what kind, (description and give a simple algorithm)
The database name and version 4 for you (often someone asked about MySQL MS SQL Server Edition)

So want to help your people can build and you the same environment, and tested in the present scheme, avoid the error on the comprehension of the text description.

Posted by Stella at January 02, 2017 - 12:07 PM

As far as possible with the text to describe, stick a graph is relatively easy, but not easily copied the text.

Posted by Stella at January 04, 2017 - 1:50 PM

Class table:
/*
Navicat MySQL Data Transfer

Source Server         : MySQL
Source Server Version : 50528
Source Host           : localhost:3306
Source Database       : jcmsdb

Target Server Type    : MYSQL
Target Server Version : 50528
File Encoding         : 65001

Date: 2013-07-17 13:48:56
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `classtable`
-- ----------------------------
DROP TABLE IF EXISTS `classtable`;
CREATE TABLE `classtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `className` varchar(20) DEFAULT NULL,
  `remark` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of classtable
-- ----------------------------
INSERT INTO `classtable` VALUES ('1', '2011 grade', '');
INSERT INTO `classtable` VALUES ('2', '2011 level education (1) class', '');
INSERT INTO `classtable` VALUES ('3', '2011 level education (2) class', '');
INSERT INTO `classtable` VALUES ('4', '2011 level education (3) class', '');
INSERT INTO `classtable` VALUES ('5', '2011 level education (4) class', '');
INSERT INTO `classtable` VALUES ('6', '2011 level education (5) class', '');
INSERT INTO `classtable` VALUES ('7', '2011 level education (6) class', '');
INSERT INTO `classtable` VALUES ('8', '2012 level education (1) class', '');
INSERT INTO `classtable` VALUES ('9', '2012 level education (2) class', '');
INSERT INTO `classtable` VALUES ('10', '2012 level education (3) class', '');
INSERT INTO `classtable` VALUES ('13', '2012 level education (4) class', '');
INSERT INTO `classtable` VALUES ('14', '2012 level education (5) class', '');
INSERT INTO `classtable` VALUES ('15', '2012 level education (6) class', '');
INSERT INTO `classtable` VALUES ('16', '2012 level education (7) class', '');
INSERT INTO `classtable` VALUES ('17', '2012 level education (8) class', '');
INSERT INTO `classtable` VALUES ('18', '2012 level education (9) class', '');



Student information table:
/*
Navicat MySQL Data Transfer

Source Server         : MySQL
Source Server Version : 50528
Source Host           : localhost:3306
Source Database       : jcmsdb

Target Server Type    : MYSQL
Target Server Version : 50528
File Encoding         : 65001

Date: 2013-07-17 13:50:32
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `studentinfo`
-- ----------------------------
DROP TABLE IF EXISTS `studentinfo`;
CREATE TABLE `studentinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `classId` int(11) DEFAULT NULL COMMENT 'Class',
  `stuNo` varchar(20) DEFAULT NULL COMMENT 'Student number',
  `stuName` varchar(10) DEFAULT NULL COMMENT 'Full name',
  `stuSex` varchar(10) DEFAULT NULL,
  `stuCode` varchar(19) DEFAULT NULL COMMENT 'ID number',
  `stuIntime` varchar(50) DEFAULT NULL COMMENT 'Time of admission',
  `stuAddress` varchar(100) DEFAULT NULL COMMENT 'Home address',
  `stuProfessional` varchar(20) DEFAULT NULL COMMENT 'Professional',
  `stuLeval` varchar(10) DEFAULT NULL COMMENT 'Record of formal schooling',
  `stuEducation` varchar(10) DEFAULT NULL COMMENT 'Schools',
  `stuSpeak` varchar(20) DEFAULT NULL COMMENT 'The certificate of Mandarin',
  `stuTeachar` varchar(50) DEFAULT NULL COMMENT 'The teacher qualification certificate',
  `stuEnd` varchar(20) DEFAULT NULL COMMENT 'After graduation',
  `remark` varchar(50) DEFAULT NULL COMMENT 'Note',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=787 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of studentinfo
-- ----------------------------
INSERT INTO `studentinfo` VALUES ('768', '18', '121448', 'Show the snow ',' female ','412726199502203328','2012',' Dancheng township of Dingcun County ',' education ',' secondary ','3', 'no', 'no', 'in reading', '12411100100781');
INSERT INTO `studentinfo` VALUES ('779', '18', '121459', 'Liu Xiaojing ',' female ','411024199712274763','2012',' the Yanling county only le Xiang Ye Gang Cun ',' education ',' secondary ','3', 'no', 'no', 'in reading', '12411100100792');
INSERT INTO `studentinfo` VALUES ('781', '18', '121461', 'Zheng Wenyi ',' female ','411024199812040729','2012',' Yanling County Malan town ',' education ',' secondary ','3', 'no', 'no', 'in reading', '12411100100794');


Students learned the chart of accounts:
/*
Navicat MySQL Data Transfer

Source Server         : MySQL
Source Server Version : 50528
Source Host           : localhost:3306
Source Database       : jcmsdb

Target Server Type    : MYSQL
Target Server Version : 50528
File Encoding         : 65001

Date: 2013-07-17 14:19:05
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `stuachievement`
-- ----------------------------
DROP TABLE IF EXISTS `stuachievement`;
CREATE TABLE `stuachievement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stuId` int(11) DEFAULT NULL COMMENT 'Student ID',
  `projectName` varchar(20) DEFAULT NULL COMMENT 'Courses name',
  `achievement` varchar(10) DEFAULT NULL COMMENT 'Achievement',
  `scores` varchar(10) DEFAULT NULL,
  `remark` varchar(50) DEFAULT NULL COMMENT 'Semester',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51126 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stuachievement
-- ----------------------------
INSERT INTO `stuachievement` VALUES ('10711', '768', 'Chinese', '—', '6', '1');
INSERT INTO `stuachievement` VALUES ('10711', '768', 'Mathematics', '—', '6', '1');
INSERT INTO `stuachievement` VALUES ('10603', '779', 'Chinese', '—', '0', '1');
INSERT INTO `stuachievement` VALUES ('10657', '779', 'Mathematics', '—', '0', '1');
INSERT INTO `stuachievement` VALUES ('10711', '779', 'English', '—', '6', '1');
INSERT INTO `stuachievement` VALUES ('10657', '779', 'Natural Science', '—', '0', '1');
INSERT INTO `stuachievement` VALUES ('10657', '781', 'Mathematics', '—', '0', '1');
INSERT INTO `stuachievement` VALUES ('10711', '781', 'Natural Science', '—', '6', '1');



I ultimately want to achieve results:
stuId stuName classId projectName scores remark
------- ------- --------- ----------- ------ -----------------
779 Liu Xiaojing 18 language 01
779 Liu Xiaojing 18 01 in math
779 Liu Xiaojing 18 English 61
779 Liu Xiaojing 18 Natural Science 01
The 781 exhibition of snow will be 18 language 61
The 781 exhibition of snow will be 18 mathematics 61
The 781 exhibition of snow will be 18 null 1
The 781 exhibition of snow will be 18 Natural Science null 1
768 Zheng Wenyi 18 Chinese null 1
768 Zheng Wenyi 18 61 in math
768 Zheng Wenyi 18 English null 1
768 Zheng Wenyi 18 Natural Science 61


Is this a result of SQL statement level how to write it?

Posted by Josephine at January 05, 2017 - 2:44 PM

,

Has been adjusted.

Posted by Josephine at January 11, 2017 - 12:53 PM

No one can help me. Thank you.

Posted by Josephine at January 13, 2017 - 1:35 PM