php - mysql 左连接和返回已连接和未连接

Question

我有 2 个表，我想将 a 联接到 b 并 for eace join 获取未联接的结果

表格像素

+----+------------+------------+-------------------+--------+------+------------+
| id | account_id | project_id | uuid              | name   | type | date       |
+----+------------+------------+-------------------+--------+------+------------+
| 10 |          2 |          3 | E03AA~F86A1~7C661 | test 1 |    0 | 1420553362 |
| 11 |          2 |          3 | A3E3B~B4182~DA556 | test 2 |    1 | 1420553933 |
|  9 |          1 |          1 | 57EAE~E633C~B929F | test 3 |    1 | 1420041387 |
+----+------------+------------+-------------------+--------+------+------------+

表pixel_tags

+----+------------+-------------------+--------------+--------------+------------+
| id | project_id | pixel             | tag          | name         | date       |
+----+------------+-------------------+--------------+--------------+------------+
|  6 |          0 | 57EAE~E633C~B929F | facebook-cpc | facebook-cpc | 1420041606 |
|  7 |          0 | 57EAE~E633C~B929F | google-cpc   | google-cpc   | 1420041621 |
|  8 |          0 | A3E3B~B4182~DA556 | utm_google   | test         | 1420554059 |
+----+------------+-------------------+--------------+--------------+------------+

这是我的查询

SELECT
    `pixel`.*,
    (CASE  WHEN `pixel_tags`.`name` <>'' THEN `pixel_tags`.`name` ELSE `pixel`.`name` END ) `p_name`,
    `pixel_tags`.`tag`
FROM `pixel`
LEFT JOIN `pixel_tags` ON `pixel`.`uuid`=`pixel_tags`.`pixel`
WHERE `pixel`.`account_id`='1'

结果是如果像素有标签，它们将显示在名称中，但我需要返回两者，例如，如果一个像素有 2 个标签，我想要 3 行，1 行带有名称，2 行带有标签谢谢。

转储

-- phpMyAdmin SQL Dump
-- version 4.0.10.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 14, 2015 at 01:19 PM
-- Server version: 5.5.42-cll
-- PHP Version: 5.4.23

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `mslm_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `pixel`
--

CREATE TABLE IF NOT EXISTS `pixel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `uuid` text NOT NULL,
  `name` text NOT NULL,
  `type` int(11) NOT NULL,
  `date` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

--
-- Dumping data for table `pixel`
--

INSERT INTO `pixel` (`id`, `account_id`, `project_id`, `uuid`, `name`, `type`, `date`) VALUES
(10, 2, 3, '75D79~535F8~96FA1~F1B5E~C21E7~E03AA~F86A1~7C661', '×—×ž×•×¦×™× ×–×” ××—', 0, 1420553362),
(11, 2, 3, '195A6~83005~4C660~62EF3~8C79A~A3E3B~B4182~DA556', '×—×ž×•×¦×™× ×–×” ×חות', 1, 1420553933),
(9, 1, 1, 'D0950~15D68~354C8~5FBAE~DAF05~57EAE~E633C~B929F', 'טורקיז', 1, 1420041387),
(12, 4, 5, 'E92E8~DEDA0~11571~86FEA~13AF9~B2266~F8EFD~FB9D3', 'חביתה', 0, 1420554873),
(13, 4, 5, '38FFD~3A1F4~A3CDE~7A90E~AF099~CD11D~28752~67D77', 'חלומי', 1, 1420555402),
(14, 4, 5, '9525D~A8682~1932E~85D96~B5830~03BF8~9C77D~7EBE2', 'סביח', 1, 1420555681),
(15, 5, 6, '3784E~151DA~7BFDE~C12F6~A6C01~435E3~36E4E~ED4AB', 'ביקיני בוטו×', 1, 1420556203),
(16, 1, 1, '1211B~9C86C~83024~9039C~43F8F~B639D~547EB~8CFAC', 'שולחן', 0, 1421322108),
(17, 1, 1, 'A8DF0~23617~904F6~94880~99192~4781E~D8712~221A7', 'כס×', 1, 1421322943),
(18, 1, 1, '0A492~EA76B~01948~061AB~A74A7~34F58~42DAC~366DE', 'חזותה', 0, 1421945914),
(19, 1, 20, '2E3FE~200C7~FC8E1~17323~A9A1D~6F278~CBECF~CDD6E', '×’× - מעיל', 0, 1422351583),
(20, 1, 20, '85CF8~71D5A~71C24~D9FC1~3A041~A1AC7~AB6CE~E1B1D', 'שפן של מורדי', 0, 1431527532),
(21, 1, 0, 'AF627~4E88E~13138~49BE4~49BB7~DAB92~DF35E~14C97', '', 0, 1431589094);

-- --------------------------------------------------------

--
-- Table structure for table `pixel_tags`
--

CREATE TABLE IF NOT EXISTS `pixel_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `pixel` text NOT NULL,
  `tag` text NOT NULL,
  `name` text NOT NULL,
  `date` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `pixel_tags`
--

INSERT INTO `pixel_tags` (`id`, `project_id`, `pixel`, `tag`, `name`, `date`) VALUES
(6, 0, 'D0950~15D68~354C8~5FBAE~DAF05~57EAE~E633C~B929F', 'facebook-cpc', 'facebook-cpc', 1420041606),
(7, 0, 'D0950~15D68~354C8~5FBAE~DAF05~57EAE~E633C~B929F', 'google-cpc', 'google-cpc', 1420041621),
(8, 0, '195A6~83005~4C660~62EF3~8C79A~A3E3B~B4182~DA556', 'utm_google', 'גוגל גוגל', 1420554059),
(9, 0, '9525D~A8682~1932E~85D96~B5830~03BF8~9C77D~7EBE2', 'utm-facebook', 'פייסבוק', 1420556056),
(10, 0, 'A8DF0~23617~904F6~94880~99192~4781E~D8712~221A7', 'h_test', 'חנה בדיקה', 1421323080);

Answer 1

查询可能是 -

   SELECT `pixel`.*,
    `pixel_tags`.`name`as p_name,
    `pixel_tags`.`tag`
FROM `pixel`
JOIN `pixel_tags` ON `pixel`.`uuid`=`pixel_tags`.`pixel`
WHERE `pixel`.`account_id`='1'
UNION
SELECT `pixel`.*, NULL , NULL 
FROM `pixel` 
WHERE `pixel`.`account_id`='1' AND `uuid` not in (select distinct pixel from pixel_tags);

我正在树立榜样-

   mysql> select * from pixel;
+----+------------+------------+------+--------+------+------------+
| id | account_id | project_id | uuid | name   | type | date       |
+----+------------+------------+------+--------+------+------------+
| 10 |          2 |          3 | a    | test 1 |    0 | 1420553362 |
| 11 |          2 |          3 | b    | test 2 |    1 | 1420553933 |
|  9 |          1 |          1 | c    | test 3 |    1 | 1420041387 |
+----+------------+------------+------+--------+------+------------+
3 rows in set (0.00 sec)


mysql> select * from pixel_tags;
+----+------------+-------+-----------+----------+------------+
| id | project_id | pixel | tag       | name     | date       |
+----+------------+-------+-----------+----------+------------+
|  6 |          0 | c     | facebook  | facebook | 1420553606 |
|  7 |          0 | c     | google    | google   | 1420041621 |
|  8 |          0 | b     | do_google | test     | 1420554059 |
+----+------------+-------+-----------+----------+------------+
3 rows in set (0.00 sec)

mysql>  SELECT `pixel`.*,
    ->     `pixel_tags`.`name`as p_name,
    ->     `pixel_tags`.`tag`
    -> FROM `pixel`
    -> JOIN `pixel_tags` ON `pixel`.`uuid`=`pixel_tags`.`pixel`
    -> WHERE `pixel`.`account_id`='2'
    -> UNION
    -> SELECT `pixel`.*, NULL , NULL
    -> FROM `pixel`
    -> WHERE `pixel`.`account_id`='2' AND `uuid` not in (select distinct pixel from pixel_tags);
+----+------------+------------+------+--------+------+------------+--------+-----------+
| id | account_id | project_id | uuid | name   | type | date       | p_name | tag       |
+----+------------+------------+------+--------+------+------------+--------+-----------+
| 11 |          2 |          3 | b    | test 2 |    1 | 1420553933 | test   | do_google |
| 10 |          2 |          3 | a    | test 1 |    0 | 1420553362 | NULL   | NULL      |
+----+------------+------------+------+--------+------+------------+--------+-----------+
2 rows in set (0.00 sec)

如果您仍然没有得到那么它可能会有所帮助。我已从两个查询中删除了 where 中的 account_id 条件。

mysql>  SELECT `pixel`.*,
    ->     `pixel_tags`.`name`as p_name,
    ->     `pixel_tags`.`tag`
    -> FROM `pixel`
    -> JOIN `pixel_tags` ON `pixel`.`uuid`=`pixel_tags`.`pixel`
    -> UNION
    -> SELECT `pixel`.*, NULL , NULL
    -> FROM `pixel`
    -> WHERE `uuid` not in (select distinct pixel from pixel_tags);
+----+------------+------------+------+--------+------+------------+----------+-----------+
| id | account_id | project_id | uuid | name   | type | date       | p_name   | tag       |
+----+------------+------------+------+--------+------+------------+----------+-----------+
|  9 |          1 |          1 | c    | test 3 |    1 | 1420041387 | facebook | facebook  |
|  9 |          1 |          1 | c    | test 3 |    1 | 1420041387 | google   | google    |
| 11 |          2 |          3 | b    | test 2 |    1 | 1420553933 | test     | do_google |
| 10 |          2 |          3 | a    | test 1 |    0 | 1420553362 | NULL     | NULL      |
+----+------------+------------+------+--------+------+------------+----------+-----------+
4 rows in set (0.02 sec)