我想將與同一個人相關的所有四種類型的劃分（婚姻、出生/死亡、代理婚姻、代理出生/死亡）顯示在一行中。以下是我的數據庫表結構登記員表- registrar id | name 1 | reg1registrar_has_division 表-id | registrar id | division id | is_acting1 | 1 | 10 |02 | 1 | 11 |03 | 1 | 12 |14 | 1 | 13 |1劃分表-division id | division name | division type10 | Hulftsdrop | 111 | Modara | 212 | Slave Island | 113 | Fort | 2這里劃分類型1是婚姻劃分，2是出生/死亡劃分以下是我寫的查詢 -SELECT r.id AS rid, r.name, IF( divi.div_type = 1 && rd.is_acting = 0, divi.name_english, NULL ) AS marriage_div, IF( divi.div_type = 2 && rd.is_acting = 0, divi.name_english, NULL ) AS bd_div, IF( divi.div_type = 1 && rd.is_acting = 1, divi.name_english, NULL ) AS acting_marriage_div, IF( divi.div_type = 2 && rd.is_acting = 1, divi.name_english, NULL ) AS acting_bd_divFROM `registrar` AS `r`INNER JOIN `registrar_has_division` AS `rd`ON `rd`.`registrar_id` = `r`.`id`INNER JOIN `registrar_division` AS `divi`ON `rd`.`division_id` = `divi`.`id`結果-rid | name | marriage_div | b/d_div | acting_marriage_div | acting_bd_div1 | reg1 | NULL | Modara | NULL | NULL1 | reg1 | Hulftsdrop | NULL | NULL | NULL1 | reg1 | NULL | NULL | Slave Island | NULL1 | reg1 | NULL | NULL | NULL | Fort我想要的結果如下 rid | name | marriage_div | b/d_div | acting_marriage_div | acting_bd_div 1 | reg1 | Hulftsdrop | Modara | Slave Island | Fort我認為問題出在查詢中的 if else 語句中。我嘗試了很多，但我失敗了。任何幫助將不勝感激。
1 回答
翻翻過去那場雪
TA貢獻2065條經驗 獲得超14個贊
您需要GROUP BY并且需要聚合函數
CREATE TABLE registrar (
? `id` INTEGER,
? `name` VARCHAR(4)
);
INSERT INTO registrar
? (`id`, `name`)
VALUES
? ('1', 'reg1');
CREATE TABLE registrar_has_division (
? `id` INTEGER,
? `registrar_id` INTEGER,
? `division_id` INTEGER,
? `is_acting` INTEGER
);
INSERT INTO registrar_has_division
? (`id`, `registrar_id`, `division_id`, `is_acting`)
VALUES
? ('1', '1', '10', '0'),
? ('2', '1', '11', '0'),
? ('3', '1', '12', '1'),
? ('4', '1', '13', '1');
CREATE TABLE registrar_division (
? `id` INTEGER,
? `name_english` VARCHAR(12),
? `div_type` INTEGER
);
INSERT INTO registrar_division
? (`id`, `name_english`, `div_type`)
VALUES
? ('10', 'Hulftsdrop', '1'),
? ('11', 'Modara', '2'),
? ('12', 'Slave Island', '1'),
? ('13', 'Fort', '2');
SELECT
? ? r.id AS rid,
? ? r.name,
? ? MAX(IF(
? ? ? ? divi.div_type = 1 && rd.is_acting = 0,
? ? ? ? divi.name_english,
? ? ? ? NULL
? ? )) AS marriage_div,
? ? MAX(IF(
? ? ? ? divi.div_type = 2 && rd.is_acting = 0,
? ? ? ? divi.name_english,
? ? ? ? NULL
? ? )) AS bd_div,
? ? MAX(IF(
? ? ? ? divi.div_type = 1 && rd.is_acting = 1,
? ? ? ? divi.name_english,
? ? ? ? NULL
? ? )) AS acting_marriage_div,
? ? MAX(IF(
? ? ? ? divi.div_type = 2 && rd.is_acting = 1,
? ? ? ? divi.name_english,
? ? ? ? NULL
? ? )) AS acting_bd_div
FROM
? ? `registrar` AS `r`
INNER JOIN `registrar_has_division` AS `rd`
ON
? ? `rd`.`registrar_id` = `r`.`id`
INNER JOIN `registrar_division` AS `divi`
ON
? ? `rd`.`division_id` = `divi`.`id`
GROUP BY r.id,r.name
rid | name | marriage_div | bd_div | acting_marriage_div | acting_bd_div
--: | :--- | :----------- | :----- | :------------------ | :------------
? 1 | reg1 | Hulftsdrop? ?| Modara | Slave Island? ? ? ? | Fort? ??
db<>
- 1 回答
- 0 關注
- 178 瀏覽
添加回答
舉報
0/150
提交
取消