时间戳

首页 > 解决方案 > mysql子查询查找等级

mysql - mysql子查询查找等级

问题描述

我有这样的表

+----+-------+------------+-------+
| No | NIK   | Nama       | Nilai |
+----+-------+------------+-------+
|  1 | 06001 | Syafruddin |    70 |
|  2 | 20000 | Maman      |    90 |
|  3 | 30000 | Linda      |    40 |
|  4 | 40000 | Tesa       |    71 |
+----+-------+------------+-------+
+-------------+-------+------------------+
| Range_Nilai | Point | Keterangan       |
+-------------+-------+------------------+
| 0 s/d 50    | E     | Tidak Lulus      |
| 51 s/d 80   | C     | Cukup            |
| 81 s/d 100  | A     | Sangat Memuaskan |
+-------------+-------+------------------+

我想得到这样的结果

在此处输入图像描述

但我的结果是这样的

在此处输入图像描述

当我尝试像这样更改我的查询时

THEN (SELECT b.Point,b.Keterangan FROM range_nilai b where b.Range_Nilai='0 s/d 50')

我收到错误“操作数应包含 1 列”

有人可以帮我吗？这是我的小提琴 

SELECT  a.*,
        ( CASE 
            WHEN a.nilai >=0 && a.Nilai<=50 
            THEN (SELECT Point FROM range_nilai where Range_Nilai='0 s/d 50')
            WHEN a.nilai >50 && a.Nilai<=80 
            THEN (SELECT Point FROM range_nilai where Range_Nilai='51 s/d 80')
            WHEN a.nilai >80 && a.Nilai<=100 
            THEN (SELECT Point FROM range_nilai where Range_Nilai='81 s/d 100')
            ELSE NULL
        END
         ) as Point,
        ( CASE 
            WHEN a.nilai >=0 && a.Nilai<=50 
            THEN (SELECT Keterangan FROM range_nilai where Range_Nilai='0 s/d 50')
            WHEN a.nilai >50 && a.Nilai<=80 
            THEN (SELECT Keterangan FROM range_nilai where Range_Nilai='51 s/d 80')
            WHEN a.nilai >80 && a.Nilai<=100 
            THEN (SELECT Keterangan FROM range_nilai where Range_Nilai='81 s/d 100')
            ELSE NULL
        END
         ) as Keterangan
FROM nilai a

这是我的最后一个查询。可能有人比我有更好的查询吗？

标签： mysql

解决方案

您可以加入表并使用子句SUBSTRING_INDEX()中的函数：ON

select n.*, r.Point, r.Keterangan
from nilai n inner join range_nilai r
on n.Nilai 
  between 
    substring_index(r.Range_Nilai, ' s/d ', 1)
    and
    substring_index(r.Range_Nilai, ' s/d ', -1)

请参阅演示。
结果：

| No  | NIK   | Nama       | Nilai | Point | Keterangan       |
| --- | ----- | ---------- | ----- | ----- | ---------------- |
| 1   | 6001  | Syafruddin | 70    | C     | Cukup            |
| 2   | 20000 | Maman      | 90    | A     | Sangat Memuaskan |
| 3   | 30000 | Linda      | 40    | E     | Tidak Lulus      |
| 4   | 40000 | Tesa       | 71    | C     | Cukup            |

推荐阅读