如何在数据库mysql中制作foreach

debugcn 发表于 Dev

塔拉

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

表：data_kel

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

表：data_mustahik

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

表格：data_survey

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

表：rekapitulasi_kelasiswa

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

现在输出（假）

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

我想输出这个（右）

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.nomor_kk,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

鲑鱼

您的第二个查询正确输出13行。为了获得所需的输出，您要么需要删除对data_kel的连接（似乎并不需要），要么确定您要从该表中选择哪个重复的data_kel.nomor_kk，或者使用像这样的不同子句

SELECT distinct dk.nama,dl.nomor_kk,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
;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。