mysql - 如何在2个类别的sql中显示最大值

Question

我现在有一个看起来像这样的表:

表 siswa:

create table siswa(nis char(4) primary key, nama char(20), jk char(1), tmpt_lahir char(10), tgl_lahir date, nm_wali char(20), alamat char(50));

插入表 siswa:

insert into siswa values('0001', 'budi', 'L', 'sekayu', '1994-05-30', 'yanto', 'kayuara'), ('0002', 'joko', 'L', 'lumpatan', '1995-05-28', 'usman', 'bailangu'), ('0003', 'santoso', 'L', 'kayuara', '1994-05-30', 'yanto', 'kayuara'), ('0004', 'susan', 'P', 'bailangu', '1995-03-02', 'michael', 'bailangu'), ('0005', 'marta', 'L', 'epil', '1995-05-07', 'yanto', 'sekayu'), ('0006', 'lusi', 'P', 'sekayu', '1995-03-02', 'budi', 'lumpatan'), ('0007', 'manap', 'L', 'lumpatan', '1995-05-28', 'abdul', 'kayuara'), ('0008', 'budi', 'L', 'kayuara', '1994-12-01', 'subhan', 'kayuara');

表 mata_pljran

create table mata_pljran(kd_mp char(10) primary key, nm_mp char(20), kelas char(2), semester char(10), kkm int);

插入表 mata_pljran

insert into mata_pljran values('10.01.001', 'matematika', '10', 'ganjil', '70'), ('10.01.002', 'bahasa indonesia', '10', 'ganjil', '80'), ('10.01.003', 'kewarganegaraan', '10', 'ganjil', '80'), ('10.01.004', 'agama', '10', 'ganjil', '80'), ('10.01.005', 'bahasa inggris', '10', 'ganjil', '65'), ('10.02.001', 'fisika', '10', 'genap', '65'), ('10.02.002', 'kimia', '10', 'genap', '70'), ('10.02.003', 'biologi', '10', 'genap', '70'), ('10.02.004', 'matematika', '10', 'genap', '70');

表汝来

create table nilai( nis char(4), kd_mp char(10) , nk decimal(5,2), nuh decimal(5,2), nuts decimal(5,2), nuas decimal(5,2), akhir decimal(5,2) default null, primary key (nis,kd_mp), foreign key (nis) references siswa(nis) on update cascade on delete cascade, foreign key (kd_mp) references mata_pljran(kd_mp) on update cascade on delete cascade ); update nilai set akhir=nk*(0.15)+nuh*(0.15)+nuts*(0.3)+nuas*(0.4);

插入表nilai

INSERT INTO `nilai` (`nis`, `kd_mp`, `nk`, `nuh`, `nuts`, `nuas`) VALUES ('0001', '10.01.001', '80.00', '75.00', '85.00', '90.00'), ('0001', '10.01.002', '85.00', '85.00', '85.00', '85.00'), ('0001', '10.01.003', '75.00', '90.00', '85.00', '85.00'), ('0001', '10.01.004', '90.00', '65.00', '85.00', '85.00'), ('0001', '10.01.005', '95.00', '55.00', '85.00', '85.00'), ('0002', '10.01.001', '65.00', '68.00', '85.00', '85.00'), ('0002', '10.01.002', '80.00', '78.00', '85.00', '85.00'), ('0002', '10.01.003', '95.00', '100.00', '85.00', '85.00'), ('0002', '10.01.004', '85.00', '98.00', '85.00', '95.00'), ('0003', '10.01.001', '90.00', '68.00', '85.00', '95.00'), ('0003', '10.01.002', '85.00', '76.00', '35.00', '68.00'), ('0003', '10.01.003', '45.00', '87.00', '20.00', '45.00'), ('0003', '10.01.004', '35.00', '65.00', '85.00', '75.00'), ('0003', '10.01.005', '85.00', '95.00', '85.00', '85.00'), ('0005', '10.01.001', '90.00', '65.00', '85.00', '90.00'), ('0005', '10.01.002', '85.00', '78.00', '85.00', '90.00'), ('0005', '10.01.003', '95.00', '98.00', '85.00', '90.00'), ('0005', '10.01.004', '85.00', '58.00', '85.00', '75.00');

显示最终值(na):

select s.nis, nama, m.kd_mp, nm_mp, kkm, (akhir) as na,
if (akhir <kkm,'GAGAL','TUNTAS') as ket from siswa s, mata_pljran m, nilai n where
s.nis=n.nis and m.kd_mp=n.kd_mp;

结果:

+------+---------+-----------+------------------+------+---------+--------+
| nis  | nama    | kd_mp     | nm_mp            | kkm  | na      | ket    |
+------+---------+-----------+------------------+------+---------+--------+
| 0001 | budi    | 10.01.001 | matematika       |   70 | 84.7500 | TUNTAS |
| 0002 | joko    | 10.01.001 | matematika       |   70 | 79.4500 | TUNTAS |
| 0003 | santoso | 10.01.001 | matematika       |   70 | 87.2000 | TUNTAS |
| 0005 | marta   | 10.01.001 | matematika       |   70 | 84.7500 | TUNTAS |
| 0001 | budi    | 10.01.002 | bahasa indonesia |   80 | 85.0000 | TUNTAS |
| 0002 | joko    | 10.01.002 | bahasa indonesia |   80 | 83.2000 | TUNTAS |
| 0003 | santoso | 10.01.002 | bahasa indonesia |   80 | 61.8500 | GAGAL  |
| 0005 | marta   | 10.01.002 | bahasa indonesia |   80 | 85.9500 | TUNTAS |
| 0001 | budi    | 10.01.003 | kewarganegaraan  |   80 | 84.2500 | TUNTAS |
| 0002 | joko    | 10.01.003 | kewarganegaraan  |   80 | 88.7500 | TUNTAS |
| 0003 | santoso | 10.01.003 | kewarganegaraan  |   80 | 43.8000 | GAGAL  |
| 0005 | marta   | 10.01.003 | kewarganegaraan  |   80 | 90.4500 | TUNTAS |
| 0001 | budi    | 10.01.004 | agama            |   80 | 82.7500 | TUNTAS |
| 0002 | joko    | 10.01.004 | agama            |   80 | 90.9500 | TUNTAS |
| 0003 | santoso | 10.01.004 | agama            |   80 | 70.5000 | GAGAL  |
| 0005 | marta   | 10.01.004 | agama            |   80 | 76.9500 | GAGAL  |
| 0001 | budi    | 10.01.005 | bahasa inggris   |   65 | 82.0000 | TUNTAS |
| 0003 | santoso | 10.01.005 | bahasa inggris   |   65 | 86.5000 | TUNTAS |
+------+---------+-----------+------------------+------+---------+--------+

我会为 nm_mp=matematika 和 bahasa inggris 显示 ma​​x(na) 或 ma​​x(akhir) like this

+------+---------+-----------+------------------+------+---------+--------+
| nis  | nama    | kd_mp     | nm_mp            | kkm  | na      | ket    |
+------+---------+-----------+------------------+------+---------+--------+
| 0003 | santoso | 10.01.001 | matematika       |   70 | 87.2000 | TUNTAS |
| 0003 | santoso | 10.01.005 | bahasa inggris   |   65 | 86.5000 | TUNTAS |
+------+---------+-----------+------------------+------+---------+--------+

我如何在 mySQL 中做到这一点？非常感谢!

我是用cmd做的，抱歉我的英语不好

编辑:由我完成 ^_^ :

select s.nis, nama, m.kd_mp, nm_mp, kkm, (akhir) as na,
if (akhir <kkm,'GAGAL','TUNTAS') as ket from siswa s, mata_pljran m, nilai n where
akhir=(select max(akhir)
from nilai as a where a.kd_mp=n.kd_mp
having nm_mp in('bahasa inggris' , 'matematika')) and
s.nis=n.nis and m.kd_mp=n.kd_mp;

结果:

+------+---------+-----------+----------------+------+-------+--------+
| nis  | nama    | kd_mp     | nm_mp          | kkm  | na    | ket    |
+------+---------+-----------+----------------+------+-------+--------+
| 0003 | santoso | 10.01.001 | matematika     |   70 | 87.20 | TUNTAS |
| 0003 | santoso | 10.01.005 | bahasa inggris |   65 | 86.50 | TUNTAS |
+------+---------+-----------+----------------+------+-------+--------+

谢谢:D

Answer 1

只是一个想法(没有实际尝试过)

SELECT * FROM TABLE_NAME having max(na) = na GROUP BY nm_mp

如果它有效或无效，请告诉我。它适用于所有类别。

对于这两个类别，试试这个。

SELECT * FROM TABLE_NAME WHERE nm_mp IN('matematika', 'bahasa inggris') having max(na) = na GROUP BY nm_mp