获得许多具有许多 id 的行 [英] getting many rows with many ids

问题描述

我有以下表格:

Action_setid_action_p1 |id_action_p2 |id_action_p3 |等等.1 |1 |22 |3 |11 |1 |1行动id_action |id_type |价值1 |0 |空值2 |1 |空值3 |2 |空值

其中Action_set表中的id_action_p1/2/3是对Action表的id_action的fks.>

对于 Action_set 中一行的每个 id，我需要在 Action 中获取相应的行.

例如，让我们获取 Action_set 的第一行:

(id_action_p1 | id_action_p2 | id_action_p3 )1 |1 |2

必须给我结果:

(id_type | value)0 |空值0 |空值1 |空值

我是 mysql 的菜鸟，所以不知道该怎么做 :(

这里是我的表格(忽略 id_lap)

如果不存在则创建表`Action`(`id_action` int(11) NOT NULL AUTO_INCREMENT,`value` int(11) 默认为空，`id_type` tinyint(4) 非空，主键(`id_action`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;INSERT INTO `Action` (`id_action`, `value`, `id_type`) VALUES(1, NULL, 0),(2, NULL, 1),(3, NULL, 2),(4, NULL, 3),(5, NULL, 4),(6, NULL, 5);如果不存在则创建表`Action_set`(`id_action_set` int(11) NOT NULL AUTO_INCREMENT,`id_lap` int(11) 非空，`id_parent_action_set` int(11) 默认为空，`id_action_pu` int(11) 默认为空，`id_action_p1` int(11) 默认为空，`id_action_p2` int(11) 默认为空，`id_action_p3` int(11) 默认为空，`id_action_p4` int(11) 默认为空，`id_action_p5` int(11) 默认为空，`id_action_p6` int(11) 默认为空，`id_action_p7` int(11) 默认为空，`id_action_p8` int(11) 默认为空，`id_stage` tinyint(4) 非空，主键(`id_action_set`)，KEY`fk_Action_set_Lap`(`id_lap`)，KEY`fk_Action_set_Action_set1`(`id_parent_action_set`)，KEY`fk_pu`(`id_action_pu`)，密钥`fk_p1`(`id_action_p1`)，密钥`fk_p2`(`id_action_p2`)，密钥`fk_p3`(`id_action_p3`)，密钥`fk_p4`(`id_action_p4`)，密钥`fk_p5`(`id_action_p5`)，密钥`fk_p6`(`id_action_p6`)，密钥`fk_p7`(`id_action_p7`)，密钥`fk_p8`(`id_action_p8`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11;插入`Action_set`(`id_action_set`、`id_lap`、`id_parent_action_set`、`id_action_pu`、`id_action_p1`、`id_action_p2`、`id_action_p3`、`id_action_p4`、`id_action_p5`、`id_action_p6`、`id_action_p6`、`id_action_p8`、`id_stage`) 值(1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, 2, 2, 0),(2, 1, 1, 1, 1, 1, 1, 2, 1, NULL, NULL, NULL, 0),(3, 1, 2, NULL, NULL, NULL, NULL, NULL, NULL, 4, 4, 4, 1),(4, 1, 3, NULL, NULL, NULL, NULL, 4, NULL, NULL, NULL, NULL, 1),(5, 1, 4, NULL, NULL, NULL, NULL, NULL, NULL, 3, 1, 1, 2),(6, 1, 5, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, 2),(7, 1, 1, 2, 1, 2, 1, 1, 1, NULL, NULL, NULL, 0),(8, 1, 7, NULL, NULL, NULL, NULL, NULL, NULL, 4, 4, 4, 1),(9, 1, 8, 4, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 1),(10, 1, 9, NULL, NULL, NULL, NULL, NULL, NULL, 1, 1, 1, 2);

<小时>

编辑 2

大家好，我找到了这个解决方案，它完全按预期返回:

SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p1 AND id_action_set = 1联合所有SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p2 AND id_action_set = 1联合所有SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p3 AND id_action_set = 1联合所有SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p4 AND id_action_set = 1联合所有SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p5 AND id_action_set = 1联合所有SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p6 AND id_action_set = 1联合所有SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p7 AND id_action_set = 1联合所有SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p8 AND id_action_set = 1

还有怎么优化呢?

解决方案

您可以在单独的列中获得 Action 结果，如下所示:

SELECT一种.*b.id_type AS p1_type, b.value AS p1_value,c.id_type AS p2_type, c.value AS p2_value,d.id_type AS p3_type, d.value AS p3_value从action_set a内部联接动作 b ON a.id_action_p1 = b.id_action内部联接动作 c ON a.id_action_p2 = c.id_action内部联接动作 d ON a.id_action_p3 = d.id_action

这会给你一个结果集:

id_p1 |id_p2 |id_p3 |p1_type |p1_value |p2_type |p2_value |p3_type |p3_value1 |1 |2 |0 |空 |0 |空 |1 |空值......

这将是一个理想的解决方案，但如果您需要按行显示结果，您可以这样做:

SELECT*从(选择 aa.*, bb.id_type, bb.valueFROM Action_set aa内连接动作 bb ON aa.id_action_p1 = bb.id_action联合所有选择 aa.*, bb.id_type, bb.valueFROM Action_set aa内连接动作 bb ON aa.id_action_p2 = bb.id_action联合所有选择 aa.*, bb.id_type, bb.valueFROM Action_set aaINNER JOIN Action bb ON aa.id_action_p3 = bb.id_action) 一种订购者a.id_action_p1,a.id_action_p2,a.id_action_p3，a.id_type

这会给你一个结果集:

id_action_p1 |id_action_p2 |id_action_p3 |id_type |价值1 |1 |2 |0 |空值1 |1 |2 |0 |空值1 |1 |2 |1 |空值

I have the following tables:

Action_set
id_action_p1 | id_action_p2 | id_action_p3 | etc. 
1            | 1            | 2
2            | 3            | 1
1            | 1            | 1

Action
id_action | id_type | value
1         | 0       | NULL
2         | 1       | NULL
3         | 2       | NULL

Where id_action_p1/2/3 in Action_set table are fks to id_action of Action table.

For each id of a row in Action_set I need to get the respective row in Action.

For example, lets get the first row of Action_set:

(id_action_p1 | id_action_p2 | id_action_p3 )
1 | 1 | 2

must give me as result:

(id_type | value)
0 | NULL
0 | NULL
1 | NULL

I'm noob with mysql, so no idea how to do :(

Edit: here my tables (ignore id_lap)

CREATE TABLE IF NOT EXISTS `Action` (
  `id_action` int(11) NOT NULL AUTO_INCREMENT,
  `value` int(11) DEFAULT NULL,
  `id_type` tinyint(4) NOT NULL,
  PRIMARY KEY (`id_action`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

INSERT INTO `Action` (`id_action`, `value`, `id_type`) VALUES
(1, NULL, 0),
(2, NULL, 1),
(3, NULL, 2),
(4, NULL, 3),
(5, NULL, 4),
(6, NULL, 5);


CREATE TABLE IF NOT EXISTS `Action_set` (
  `id_action_set` int(11) NOT NULL AUTO_INCREMENT,
  `id_lap` int(11) NOT NULL,
  `id_parent_action_set` int(11) DEFAULT NULL,
  `id_action_pu` int(11) DEFAULT NULL,
  `id_action_p1` int(11) DEFAULT NULL,
  `id_action_p2` int(11) DEFAULT NULL,
  `id_action_p3` int(11) DEFAULT NULL,
  `id_action_p4` int(11) DEFAULT NULL,
  `id_action_p5` int(11) DEFAULT NULL,
  `id_action_p6` int(11) DEFAULT NULL,
  `id_action_p7` int(11) DEFAULT NULL,
  `id_action_p8` int(11) DEFAULT NULL,
  `id_stage` tinyint(4) NOT NULL,
  PRIMARY KEY (`id_action_set`),
  KEY `fk_Action_set_Lap` (`id_lap`),
  KEY `fk_Action_set_Action_set1` (`id_parent_action_set`),
  KEY `fk_pu` (`id_action_pu`),
  KEY `fk_p1` (`id_action_p1`),
  KEY `fk_p2` (`id_action_p2`),
  KEY `fk_p3` (`id_action_p3`),
  KEY `fk_p4` (`id_action_p4`),
  KEY `fk_p5` (`id_action_p5`),
  KEY `fk_p6` (`id_action_p6`),
  KEY `fk_p7` (`id_action_p7`),
  KEY `fk_p8` (`id_action_p8`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11;

INSERT INTO `Action_set` (`id_action_set`, `id_lap`, `id_parent_action_set`, `id_action_pu`, `id_action_p1`, `id_action_p2`, `id_action_p3`, `id_action_p4`, `id_action_p5`, `id_action_p6`, `id_action_p7`, `id_action_p8`, `id_stage`) VALUES
(1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, 2, 2, 0),
(2, 1, 1, 1, 1, 1, 1, 2, 1, NULL, NULL, NULL, 0),
(3, 1, 2, NULL, NULL, NULL, NULL, NULL, NULL, 4, 4, 4, 1),
(4, 1, 3, NULL, NULL, NULL, NULL, 4, NULL, NULL, NULL, NULL, 1),
(5, 1, 4, NULL, NULL, NULL, NULL, NULL, NULL, 3, 1, 1, 2),
(6, 1, 5, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, 2),
(7, 1, 1, 2, 1, 2, 1, 1, 1, NULL, NULL, NULL, 0),
(8, 1, 7, NULL, NULL, NULL, NULL, NULL, NULL, 4, 4, 4, 1),
(9, 1, 8, 4, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 1),
(10, 1, 9, NULL, NULL, NULL, NULL, NULL, NULL, 1, 1, 1, 2);

---

Edit 2

Hello guys, I found this solution, that return exactly as expected:

SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p1 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p2 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p3 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p4 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p5 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p6 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p7 AND id_action_set = 1
UNION ALL
SELECT id_type, value FROM Action, Action_set WHERE id_action = id_action_p8 AND id_action_set = 1

There is how to optimize it?

解决方案

You could get the Action results in separate columns like so:

SELECT
    a.*
    b.id_type AS p1_type, b.value AS p1_value,
    c.id_type AS p2_type, c.value AS p2_value,
    d.id_type AS p3_type, d.value AS p3_value
FROM
    Action_set a
INNER JOIN
    Action b ON a.id_action_p1 = b.id_action
INNER JOIN
    Action c ON a.id_action_p2 = c.id_action
INNER JOIN
    Action d ON a.id_action_p3 = d.id_action

That would give you a result set along the lines of:

id_p1 | id_p2 | id_p3 | p1_type | p1_value | p2_type | p2_value | p3_type | p3_value
1     | 1     | 2     | 0       | NULL     | 0       | NULL     | 1       | NULL
...
...

That would be an ideal solution, but if you need the result to be in rows, you could do:

SELECT
    *
FROM
    (
        SELECT aa.*, bb.id_type, bb.value
        FROM Action_set aa
        INNER JOIN Action bb ON aa.id_action_p1 = bb.id_action

        UNION ALL

        SELECT aa.*, bb.id_type, bb.value
        FROM Action_set aa
        INNER JOIN Action bb ON aa.id_action_p2 = bb.id_action

        UNION ALL

        SELECT aa.*, bb.id_type, bb.value
        FROM Action_set aa
        INNER JOIN Action bb ON aa.id_action_p3 = bb.id_action
    ) a
ORDER BY
    a.id_action_p1,
    a.id_action_p2,
    a.id_action_p3,
    a.id_type

That would give you a result set like:

id_action_p1 | id_action_p2 | id_action_p3 | id_type | value
1            | 1            | 2            | 0       | NULL
1            | 1            | 2            | 0       | NULL
1            | 1            | 2            | 1       | NULL

这篇关于获得许多具有许多 id 的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！