mysql - MySQL服务器上的SQL语法需要修改为sql_mode=only_full_group_by

Question

请帮助我纠正 SQL 语法:

SELECT DISTINCT 
  kodemk, a.namamk, a.sks, IF( b.status='K', 0, a.smt ) as smt, 
  a.kompetensi, MIN(b.nilai) nilai, b.kodesmt
FROM 
  (SELECT * 
   FROM matakuliah 
   WHERE tahunkur='2013' AND (prodi= '55201' OR kompetensi= '523')) AS a 
  LEFT JOIN 
  (SELECT 
     kodemk, ( IF( MIN(nilai)<>'', MIN(nilai),'P' ) ) AS nilai, kodesmt, status 
     FROM kuliah 
     WHERE nimhs= '52014024' AND status<>'B' 
     GROUP BY kodemk, kodesmt, status) AS b 
  USING(kodemk) 
ORDER BY smt, a.kompetensi, kodemk

它给了我这样的结果:

kodemk    namamk                           sks  smt  kompetensi  nilai  kodesmt
-----------------------------------------------------------------------------------
13000001  PENDIDIKAN AGAMA ISLAM           2    1    000         A      20141
13000002  PENDIDIKAN AGAMA KRISTEN         2    1    000         NULL   NULL
13000003  PENDIDIKAN AGAMA KATOLIK         2    1    000         NULL   NULL
13000004  PENDIDIKAN AGAMA HINDU           2    1    000         NULL   NULL 
13000005  PENDIDIKAN AGAMA BUDHA           2    1    000         NULL   NULL
13000006  PANCASILA                        2    1    000         B      20141
13000007  BAHASA INDONESIA                 2    1    000         B      20141
13500001  PENGANTAR TEKNOLOGI INFORMASI    2    1    500         A      20141
13500002  ALGORITMA DAN PEMROGRAMAN I      2    1    500         C      20141
13500003  PRAK. ALGORITMA & PEMROGRAMAN I  1    1    500         B      20141 
13500004  PERANGKAT LUNAK APLIKASI         2    1    500         B      20141
13500005  PRAK. PERANGKAT LUNAK APLIKASI   1    1    500         B      20141
13500006  STATISTIKA                       3    1    500         B      20141
13520001  KALKULUS I                       2    1    520         C      20151
13520001  KALKULUS I                       2    1    520         D      20141
13520002  FISIKA                           2    1    520         C      20141
13000008  PENDIDIKAN KEWARGANEGARAAN       2    2    000         B      20142
13000009  BAHASA INGGRIS                   2    2    000         B      20142
13500007  ALJABAR LINEAR                   3    2    500         B      20172
13500007  ALJABAR LINEAR                   3    2    500         E      20152 
13500007  ALJABAR LINEAR                   3    2    500         E      20162
13500007  ALJABAR LINEAR                   3    2    500         E      20142
13500007  ALJABAR LINEAR                   3    2    500         P      20161
13500008  ALGORITMA DAN PEMROGRAMAN II     2    2    500         B      20142
13500009  PRAK. ALGORITMA & PEMROGRAMAN II 1    2    500         C      20142
...
...

但我只需要为每个具有最佳成绩 (nilai) 的 kodemk 提供一条记录，例如 ALJABAR LNIER 和 B code> 等级，以及 KALKULUS I 和 C 等级

当我使用不同版本的 MySQL 服务器迁移我的服务器后，出现此问题。在以前的版本中，我只使用这个查询，它按我想要的方式工作:

SELECT DISTINCT 
  kodemk, a.namamk, a.sks, IF( b.status='K', 0, a.smt ) as smt, 
  a.kompetensi, MIN(b.nilai) nilai, b.kodesmt
FROM 
  (SELECT * 
   FROM matakuliah 
   WHERE tahunkur='2013' AND (prodi= '55201' OR kompetensi= '523')) AS a 
  LEFT JOIN 
  (SELECT 
     kodemk, ( IF( MIN(nilai)<>'', MIN(nilai),'P' ) ) AS nilai, kodesmt, status 
     FROM kuliah 
     WHERE nimhs= '52014024' AND status<>'B' 
     GROUP BY kodemk AS b 
  USING(kodemk) 
ORDER BY smt, a.kompetensi, kodemk

但迁移后我收到此错误消息:

Error Number: 1055

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sia40.kuliah.kodesmt' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

所以，我通过添加列名称来修改 BROUP BY 子句

...
GROUP BY kodemk, kodesmt, status
...

但结果不合适

我认为，更改sql_mode不是一个好的选择

编辑:

这是我的表结构:

-- Table structure for table `matakuliah`

CREATE TABLE `matakuliah` (
  `prodi` varchar(5) DEFAULT NULL,
  `kodemk` varchar(10) DEFAULT NULL,
  `namamk` varchar(128) DEFAULT NULL,
  `sks` tinyint(4) DEFAULT NULL,
  `skst` tinyint(4) DEFAULT NULL,
  `sksp` tinyint(4) DEFAULT NULL,
  `sksl` tinyint(4) DEFAULT NULL,
  `smt` tinyint(4) DEFAULT NULL,
  `kelompok` varchar(5) DEFAULT NULL,
  `kompetensi` char(3) DEFAULT NULL,
  `tahunkur` varchar(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Sample data for table `siska_matakuliah`
--

INSERT INTO `matakuliah` (`prodi`, `kodemk`, `namamk`, `sks`, `skst`, `sksp`, `sksl`, `smt`, `kelompok`, `kompetensi`, `tahunkur`) VALUES
('57601', '112001', 'PRAKTEK KERJA & LAPORAN', 2, 0, 0, 2, 2, '', '111', '2003'),
('57601', '112102', 'TUGAS AKHIR (APLIKASI)', 2, 0, 0, 2, 2, '', '111', '2003'),
('57601', '111001', 'SISTEM OPERASI', 2, 1, 1, 0, 1, '', '111', '2003'),
('57601', '111101', 'INTRANET & INTERNET', 2, 1, 1, 0, 1, '', '111', '2003'),
('57601', '111401', 'PENGELOLAAN INSTALASI KOMPUTER', 2, 1, 1, 0, 1, '', '111', '2003'),
('57601', '110001', 'PENDIDIKAN AGAMA ISLAM', 2, 2, 0, 0, 1, '', '111', '2003'),
('57601', '110002', 'PENDIDIKAN AGAMA KRISTEN PROTESTAN', 2, 2, 0, 0, 1, '', '111', '2003'),
('57601', '110003', 'PENDIDIKAN AGAMA KRISTEN KATOLIK', 2, 2, 0, 0, 1, '', '111', '2003'),
('57601', '110004', 'PENDIDIKAN AGAMA HINDU', 2, 2, 0, 0, 1, '', '111', '2003'),
('57601', '110005', 'PENDIDIKAN AGAMA BUDDHA', 2, 2, 0, 0, 1, '', '111', '2003');


-- Table structure for table `kuliah`

CREATE TABLE `kuliah` (
  `kodesmt` varchar(5) DEFAULT NULL,
  `prodi` varchar(5) DEFAULT NULL,
  `nimhs` varchar(8) DEFAULT NULL,
  `kodemk` varchar(10) DEFAULT NULL,
  `nilai` char(1) DEFAULT NULL,
  `presensi` varchar(35) NOT NULL,
  `paralel` char(2) DEFAULT NULL,
  `kodekelas` varchar(9) DEFAULT NULL,
  `status` char(1) NOT NULL,
  `id_krs` char(32) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

GROUP BY 子句中未提及的所有列都必须聚合。旧版本的 MySQL 没有检查这一点，结果是......好吧......至少可以说是“随机”，在最坏的情况下是“未定义”。

任何聚合函数都可以工作。例如，您可以使用 MIN()、MAX()、SUM()、AVG()、等等。在您的特定查询中，我会添加它们，如下所示:

SELECT DISTINCT 
  IF( b.status='K', 0, a.smt ) as smt, -- in the GROUP BY; no change needed
  a.kompetensi, -- in the GROUP BY; no change needed
  kodemk, -- in the GROUP BY; no change needed
  min(a.namamk), -- added MIN() here; change function as needed
  min(a.sks, -- added MIN() here; change function as needed
  MIN(b.nilai) nilai, -- was already aggregated in your query
  min(b.kodesmt) -- added MIN() here; change function as needed
FROM 
  (SELECT * 
   FROM matakuliah 
   WHERE tahunkur='2013' AND (prodi= '55201' OR kompetensi= '523')) AS a 
  LEFT JOIN 
  (SELECT 
     kodemk, -- in the GROUP BY; no change needed
     min( IF( MIN(nilai)<>'', MIN(nilai),'P' ) ) AS nilai, -- added MIN()
     min(kodesmt),  -- added MIN()
     min(status) -- added MIN()
     FROM kuliah 
     WHERE nimhs= '52014024' AND status<>'B' 
     GROUP BY kodemk AS b 
  USING(kodemk) 
ORDER BY smt, a.kompetensi, kodemk