我有两张桌子; members和telephone 。
一个成员可以有多个电话行。 每个电话行可以设置标志来指示它是哪种类型的电话号码。
我需要一个结果集，每个成员单行，包含全部或部分不同的电话行。
这是来自同一个表的不同部分的LEFT JOINS的连接。
数据库表格在所有数字列上都有完整的索引。
表结构
（可能不那么重要，但为了完整起见，这里）
成员：
CREATE TABLE `members` ( `mem_id` smallint(6) NOT NULL AUTO_INCREMENT, ... `reg` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'No Reg', PRIMARY KEY (`mem_id`) ) ENGINE=MyISAM AUTO_INCREMENT=968 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci电话：
CREATE TABLE `telephone` ( `tel_id` int(8) NOT NULL AUTO_INCREMENT, `mem_id` int(8) NOT NULL, `tel_name` varchar(64) CHARACTER SET utf8 NOT NULL, `tel_num` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL, `main` char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N', `player` char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N', `home` char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N' COMMENT 'Y= Home Phone', PRIMARY KEY (`tel_id`), KEY `memid` (`mem_id`), KEY `main` (`main`), KEY `player` (`player`), KEY `home` (`home`), KEY `telnum` (`tel_num`) ) ENGINE=MyISAM AUTO_INCREMENT=2237 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci我的查询到目前为止：
查询的目的是为每个成员详细说明（除其他数据外）所有相关电话号码并能够在每个号码之间区分的单行：
WHERE条件仅适用于members表。
SELECT members.mem_id, ... , thome.tel_num as thome, tmob.tel_num as tmob, twork.tel_num as twork FROM members LEFT JOIN telephone thome FROM telephone ON thome.mem_id = members.mem_id AND thome.home = 'Y' LEFT JOIN telephone tmob FROM telephone ON tmob.mem_id = members.mem_id AND tmob.main = 'Y' AND tmob.tel_num LIKE '07%' LEFT JOIN telephone twork FROM telephone ON twork.mem_id = members.mem_id AND twork.player = 'Y' WHERE ...示例数据：
会员桌：
memid | mname | reg --------------------------------- 22 | george | 1456436456电话表：
tel_id | mem_id | tel_name | tel_num | main | player | home ----------------------------------------------------------------------- 1 | 22 | Home phone | 01234 456463 | N | N | Y 12 | 22 | Work phone | 01334 457893 | Y | N | N 19 | 22 | Mobile num | 07564 456333 | N | Y | N 23 | 22 | Home phone | 01987 657353 | N | N | N预期结果：
memid | mname | reg | twork | tmob | thome | etc... ------------------------------------------------------------------- 22 | george | 1456436456 | 01334...| 07564...| 01234...| ...笔记：
我知道标志栏（ main ， player等）可能是ENUM
我也知道这些表可能是InnoDB并且设置了外键。
（我完全不反对这些事情中的任何一件事，这只是他们的一个案例）
没有电话号码是必需的，所以我知道INNER JOIN更有效率; 它在这种情况下不能使用。
GROUP_CONCAT不能在每个列的基础上工作，因为每个列都由不同的条件（标志）选择。 也许它可以在更广泛的设置上工作，但每个tel_num值都需要重新命名（ 我想我可以在这种情况下使用一些关于使用GROUP_CONCAT指导 ）。
标志不是互斥的：电话行可以是| Y | Y | Y | Y | Y | Y | Y | Y | Y 但每个成员只有一个旗帜。
所有电话号码都是有效的，有些没有标志，因此不需要退回。
题：
我可以改进我的SQL查询，不需要三个单独的LEFT JOIN吗？
我用类似的问题看过的其他问题：
Mysql - 优化 - 使用具有多个group_concat和连接
INNER或LEFT将多个表格记录合并为一行
MySQL：将连接后的多个值组合到一个结果列中
I have two tables; members and telephone.
A member can have multiple telephone rows. Each telephone row can have flags set to indicate what type of phone number it is.
I need a result set that is a single row per member, containing all or some of the different telephone rows.
This is a concatenation of LEFT JOINS from different parts of the same table.
The database tables both have full indexes on all numeric columns.
Table structures
(probably not that important but here for completeness sake)
members:
CREATE TABLE `members` ( `mem_id` smallint(6) NOT NULL AUTO_INCREMENT, ... `reg` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'No Reg', PRIMARY KEY (`mem_id`) ) ENGINE=MyISAM AUTO_INCREMENT=968 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ciTelephone:
CREATE TABLE `telephone` ( `tel_id` int(8) NOT NULL AUTO_INCREMENT, `mem_id` int(8) NOT NULL, `tel_name` varchar(64) CHARACTER SET utf8 NOT NULL, `tel_num` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL, `main` char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N', `player` char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N', `home` char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N' COMMENT 'Y= Home Phone', PRIMARY KEY (`tel_id`), KEY `memid` (`mem_id`), KEY `main` (`main`), KEY `player` (`player`), KEY `home` (`home`), KEY `telnum` (`tel_num`) ) ENGINE=MyISAM AUTO_INCREMENT=2237 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ciMy Query so far:
Aim of the query is to return a single row for each member detailing (amongst other data unshown) all their associated telephone numbers and being able to differenciate between each number:
The WHEREconditionals are only applied to the members table only.
SELECT members.mem_id, ... , thome.tel_num as thome, tmob.tel_num as tmob, twork.tel_num as twork FROM members LEFT JOIN telephone thome FROM telephone ON thome.mem_id = members.mem_id AND thome.home = 'Y' LEFT JOIN telephone tmob FROM telephone ON tmob.mem_id = members.mem_id AND tmob.main = 'Y' AND tmob.tel_num LIKE '07%' LEFT JOIN telephone twork FROM telephone ON twork.mem_id = members.mem_id AND twork.player = 'Y' WHERE ...Example data:
Members table:
memid | mname | reg --------------------------------- 22 | george | 1456436456Telephone table:
tel_id | mem_id | tel_name | tel_num | main | player | home ----------------------------------------------------------------------- 1 | 22 | Home phone | 01234 456463 | N | N | Y 12 | 22 | Work phone | 01334 457893 | Y | N | N 19 | 22 | Mobile num | 07564 456333 | N | Y | N 23 | 22 | Home phone | 01987 657353 | N | N | NDesired Result:
memid | mname | reg | twork | tmob | thome | etc... ------------------------------------------------------------------- 22 | george | 1456436456 | 01334...| 07564...| 01234...| ...Notes:
I'm aware that the flag columns (main, player, etc.) could be ENUMs
I'm also aware the tables could be InnoDB and have foreign keys setup.
(I'm not at all against either of these things it's just a case of getting round to them)
None of the telephone numbers are required so while I am aware INNER JOIN is more efficient; it can't be used in this case.
GROUP_CONCAT doesn't work on a per-column basis as each column is selected by differet criteria (flags). Maybe it can work on a broader setting but each tel_num value would need to be renamed (I think I could use some guidance on using GROUP_CONCAT in this situation).
Flags are not mutually exclusive: a telephone row could be | Y | Y | Y . But each member only has one of each flag.
All the phone numbers are active, some will have no flags and so not need to be returned.
Question:
Can I improve my SQL query to not need three seperate LEFT JOINs?
Other questions I've looked at with similar issues:
Mysql - optimisation - multiple group_concat & joins using having
INNER or LEFT Joining Multiple Table Records Into A Single Row
MySQL: combining multiple values after a join into one result column
最满意答案
在不改变你当前的模式的情况下，我没有看到改善你当前查询的方法，除了试图用索引等来调整它。然而，我可以对你的表格设计进行建议改变，这可以使它本身变得更简单查询您想要的输出。 考虑将telephone表重构为以下内容：
tel_id | mem_id | tel_name | tel_num | type | active ----------------------------------------------------------------------- 1 | 22 | Home phone | 01234 456463 | 1 | 1 12 | 22 | Work phone | 01334 457893 | 2 | 1 19 | 22 | Mobile num | 07564 456333 | 3 | 1 23 | 22 | Home phone | 01987 657353 | 1 | 0现在，如果你想要一个查询，将检索每个成员的活跃的家庭，工作和手机号码，以下就足够了：
SELECT m.memid, m.mname, m.reg, GROUP_CONCAT(t.tel_name ORDER BY t.type) names, GROUP_CONCAT(t.tel_num ORDER BY t.type) numbers FROM members m LEFT JOIN telephone t ON m.mem_id = t.mem_id AND t.active = 1 GROUP BY m.memid;我在这里假设每个成员将始终有一些家庭，工作和手机号码的条目，即使该条目应该为空或空字符串。 我还假设您只想报告活动数字。 如果您需要报告所有数字，那么组连接解决方案就会变得不那么有吸引力了，因为您可能会找回一个长CSV字符串，并且可能不太直观如何阅读它。
编辑：
我们还可以通过GROUP_CONCAT电话名称标签。 这意味着结果集中的每个记录除了包含CSV数字列表外，还将包含相应的电话号码类型。
Without changing your current schema, I don't see a way to improve your current query, other than perhaps trying to tune it with indices etc. However, I can make a suggested change in your table design which can lend itself to a much simpler query for the output you want. Consider refactoring the telephone table to the following:
tel_id | mem_id | tel_name | tel_num | type | active ----------------------------------------------------------------------- 1 | 22 | Home phone | 01234 456463 | 1 | 1 12 | 22 | Work phone | 01334 457893 | 2 | 1 19 | 22 | Mobile num | 07564 456333 | 3 | 1 23 | 22 | Home phone | 01987 657353 | 1 | 0Now if you want a query which will retrieve the active home, work, and mobile numbers for each member, the following should suffice:
SELECT m.memid, m.mname, m.reg, GROUP_CONCAT(t.tel_name ORDER BY t.type) names, GROUP_CONCAT(t.tel_num ORDER BY t.type) numbers FROM members m LEFT JOIN telephone t ON m.mem_id = t.mem_id AND t.active = 1 GROUP BY m.memid;I have assumed here that each member will always have some entry for the home, work, and mobile numbers, even if that entry should be null or empty string. I also assume that you only want to report the active numbers. If you need to report all numbers, then a group concat solution becomes less attractive, because you might get back a long CSV string and it might not be intuitive how to read it.
Edit:
We can also GROUP_CONCAT the telephone name labels. This means that each record in the result set will have, in addition to a CSV list of numbers, the corresponding phone number types.
更多推荐
发布评论