主页 / dba / 问题 / 101733
Accepted
Darii Petru
Asked: 2015-05-18 06:42:07 +0800 CST

MySQL 选择与连接表

  • 772

我有 3 个表，其中一个是连接表：

用户表：

CREATE TABLE `user` (
`id`,
`name`)

媒体表：

CREATE TABLE `media` (
`id`,
`value`,
`type` enum('M', 'T'))

用户媒体表：

CREATE TABLE `user_media` (
`user_id`,
`media_id`)

我想创建一个选择，让我可以搜索 user.name 和 media.value 并返回格式化值。前任：

INSERT INTO `user` (`id`, `name`) VALUES 
(1, 'Luke'),
(2, 'Vader'),
(3, 'Gregory');

INSERT INTO `media` (`id`, `value`, 'type') VALUES 
(1, '[email protected]', 'M'),
(2, '[email protected]', 'M'),
(3, '123456789', 'T'),
(4, '123', 'T');

INSERT INTO `user_media` (`user_id`, `media_id`) VALUES 
(1, 1),
(1, 2),
(1, 3),
(2, 4);

search: [email protected]
output:
userId    |email          |tel
1         |[email protected]  |123456789

search: 123
output:
userId    |email          |tel
1         |[email protected]  |123456789
2         |null           |123

search: Gregory
output:
userId    |email          |tel
3         |null           |null

更新

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel 
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mm.value LIKE "%[email protected]%"
  OR mt.value LIKE "%[email protected]%"
  OR u.name LIKE "%[email protected]%"

结果：

user_id mail               tel
1       [email protected]      [email protected]
1       [email protected]  [email protected]
1       123456789          [email protected]
1       [email protected]      [email protected]
1       [email protected]      123456789

二

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel 
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id AND mt.type="T"
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id AND mm.type="M"
WHERE mm.value LIKE "%[email protected]%"
  OR mt.value LIKE "%[email protected]%"
  OR u.name LIKE "%[email protected]%"

结果：

user_id mail               tel
1       [email protected]      null
1       [email protected]      null
1       [email protected]      123456789

三

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel 
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mt.type="T"
  AND mm.type="M"
  AND (mm.value LIKE "%Gregory%"
   OR mt.value LIKE "%Gregory%"
   OR u.name LIKE "%Gregory%")

结果：0

mysql join

1 个回答

  1. Best Answer

    您需要 3 次不同的搜索并将结果合并。我会用UNION：

    SELECT 
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel 
FROM `user` AS u
  LEFT JOIN user_media AS umt 
         JOIN media AS mt ON  mt.id = umt.media_id
                          AND mt.type = 'T'
    ON u.id = umt.user_id
  LEFT JOIN user_media AS umm 
         JOIN media AS mm ON  mm.id = umm.media_id
                          AND mm.type = 'M'
    ON u.id = umm.user_id
WHERE
  u.name LIKE CONCAT('%', ?, '%')

UNION ALL

SELECT 
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel 
FROM media AS mt 
  LEFT JOIN user_media AS umt ON mt.id = umt.media_id
  LEFT JOIN `user` AS u ON u.id = umt.user_id 
  LEFT JOIN user_media AS umm 
        JOIN media as mm ON  mm.id = umm.media_id
                         AND mm.type = 'M'
    ON umt.user_id  = umm.user_id
WHERE
  mt.type = 'T'
  AND mt.value LIKE CONCAT('%', ?, '%')

UNION ALL

SELECT 
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel 
FROM media AS mm 
  LEFT JOIN user_media AS umm ON mm.id = umm.media_id
  LEFT JOIN `user` AS u ON u.id = umm.user_id 
  LEFT JOIN user_media AS umt 
         JOIN media AS mt ON  mt.id = umt.media_id
                          AND mt.type = 'T'
    ON umm.user_id = umt.user_id
WHERE
  mm.type = 'M'
  AND mm.value LIKE CONCAT('%', ?, '%') ;

    在SQLfiddle测试。（将 替换为?您要搜索的值。）

    • 0

相关问题