mysql - 对来自同一来源的数据进行分组 (MYSQL)

Question

这是我的表格，与依赖关系

Table: data_kel

+---------+--------------+
| alamat  | nomor_kk     |
+---------+--------------+
| tes1    | 11           |
+---------+--------------+
| tes2    | 12           |
+---------+--------------+
| tes3    | 12           |
+---------++-------------+
| tes4    | 12           |
+---------+--------------+
| tes5    | 11           |
+---------+--------------+

Table: data_mustahik

+---------+---------------+------------+
| nama    | nomor_kk      | kepala_kel |
+---------+---------------+------------+
| 11      | 11            | 1          |
+---------+--------------+-------------+
| 12      | 12            | 1          |
+---------+--------------+-------------+
| 13      | 12            | 0          |
+---------++---------------+-----------+
| 14      | 12            | 0          |
+---------+--------------+-------------+
| 15      | 11            | 0          |
+---------+--------------+-------------+

Table: data_survey

   +--------------+---------------+
   | id_survey    | nomor_kk      |
   +--------------+---------------+
   | 21           | 11            |
   +--------------+---------------+
   | 22           | 12            |
   +--------------+---------------+
   | 23           | 12            |
   +--------------+---------------+
   | 24           | 12            |
   +--------------+---------------+
   | 25           | 11            |
   +--------------+---------------+

Table: rekapitulasi_kelayakan

+--------------+---------------+
| id_srv       | kelayakan     |
+--------------+---------------+
| 21           | layak         |
+--------------+---------------+
| 22           | tidak         |
+--------------+---------------+
| 23           | -             |
+--------------+---------------+
| 24           | -             |
+--------------+---------------+
| 25           | -             |
+--------------+---------------+

立即输出(假)

11 | 11 | Layak 
12 | 12 | Tidak
13 | 12 | - 
14 | 12 | -
15 | 11 | -

我想输出这个(右)

11 | 11 | Layak 
12 | 12 | Tidak
13 | 12 | Layak 
14 | 12 | Layak
15 | 11 | Tidak 

我一直在查询这个..实际上这个查询很长..它很简单..如果我使用组..输出只有一个而不是全部出现..

i want to grouping 
nomor_kk 11 with nama 11,15 => layak
nomor_kk 12 with nama 12,13,14 => tidak 

我的查询是

SELECT * FROM data_mustahik dk
         INNER JOIN data_survey ds 
         ON dk.nomor_kk=ds.nomor_kk
         INNER JOIN rekapitulasi_kelayakan rk
         ON ds.id_survey=rkid_srv

查询

CREATE TABLE data_kel
    (`alamat` varchar(5), `nomor_kk` int)
;

INSERT INTO data_kel
    (`alamat` , `nomor_kk`)
VALUES
    ('tes1', 11),
    ('tes2', 12),
    ('tes3', 12),
    ('tes4', 12),
    ('tes5', 11)
;

CREATE TABLE data_survey
    (`id_survey` int, `nomor_kk` int)
;

INSERT INTO data_survey
    (`id_survey`, `nomor_kk`)
VALUES
    (21, 11),
    (22, 12),
    (23, 12),
    (24, 12),
    (25, 11)
;




CREATE TABLE rekapitulasi_kelayakan
    (`id_srv` int, `kelayakan` varchar(5))
;

INSERT INTO rekapitulasi_kelayakan
    (`id_srv`, `kelayakan`)
VALUES
    (21, 'layak'),
    (22, 'tidak'),
    (23, '-'),
    (24, '-'),
    (25, '-')
;


CREATE TABLE data_mustahik
    (`nama` int, `nomor_kk` int, `kepala_kel` int)
;

INSERT INTO data_mustahik
    (`nama`, `nomor_kk`, `kepala_kel`)
VALUES
    (11, 11, 1),
    (12, 12, 1),
    (13, 12, 0),
    (14, 12, 0),
    (15, 11, 0)
;

查询2

SELECT dk.nama,dl.alamat,rk.kelayakan FROM data_mustahik dk
INNER JOIN data_kel dl 
ON dk.nomor_kk=dl.nomor_kk
INNER JOIN data_survey ds 
ON dk.nomor_kk=ds.nomor_kk
INNER JOIN rekapitulasi_kelayakan rk
ON ds.id_survey=rk.id_srv
WHERE rk.kelayakan != '-'
order by nama

Answer 1

您需要过滤掉 keleyakan 列中带有 - 的行。

SELECT * FROM data_mustahik dk
INNER JOIN data_survey ds 
ON dk.nomor_kk=ds.nomor_kk
INNER JOIN rekapitulasi_kelayakan rk
ON ds.id_survey=rk.id_srv
WHERE rk.kelayakan != '-'
order by nama

DEMO