mysql - 如何在不使用 View 的情况下搜索 MySQL 数据透视表

Question

想象两个简单的数据透视表a和v，具有以下结构和数据:

CREATE TABLE IF NOT EXISTS `a` (
  `i` bigint(13) NOT NULL AUTO_INCREMENT,
  `a` bigint(13) NOT NULL,
  PRIMARY KEY (`i`),
  KEY `a` (`a`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='records' AUTO_INCREMENT=6 ;

INSERT INTO `a` (`i`, `a`) VALUES
(1, 0),
(2, 1),
(3, 2),
(4, 2),
(5, 2);

CREATE TABLE IF NOT EXISTS `v` (
  `i` bigint(13) NOT NULL,
  `k` bigint(13) NOT NULL,
  `v` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,
  UNIQUE KEY `ixk` (`i`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 
COMMENT='varchar (1024) fields';

INSERT INTO `v` (`i`, `k`, `v`) VALUES
(1, 1, 'Organizations'),
(3, 1, 'Apple Inc.'),
(3, 2, 'apple.com'),
(4, 1, 'Microsoft Corporation'),
(4, 2, 'microsoft.com'),
(5, 1, 'Google Corporation'),
(5, 2, 'google.com');

可以使用数据透视表选择语句从这些表中提取两个“虚拟”信息表，如下所示:

select a.i as 'id',
ifnull (max(case when v.k = 1 then v.v end),'') 'Table'
from a
left join v on a.i = v.i
where a.a = 0
group by a.i

产生以下结果:

+----+---------------+
| id | Table         |
+----+---------------+
|  1 | Organizations |
+----+---------------+

和

select a.i as 'id',
ifnull (max(case when v.k = 1 then v.v end),'') 'Company',
ifnull (max(case when v.k = 2 then v.v end),'') 'Domain'
from a 
left join v on a.i = v.i 
where a.a = 2 group by a.i

产量:

+----+-----------------------+---------------+
| id | Company               | Domain        |
+----+-----------------------+---------------+
|  3 | Apple Inc.            | apple.com     |
|  4 | Microsoft Corporation | microsoft.com |
|  5 | Google Corporation    | google.com    |
+----+-----------------------+---------------+

然后可以创建一个 View 来表示最后一个查询:

CREATE ALGORITHM = UNDEFINED VIEW  `organizations` 
AS SELECT a.i AS  'id', 
IFNULL( MAX( CASE WHEN v.k =1 THEN v.v END ) ,  '' )  'Company', 
IFNULL( MAX( CASE WHEN v.k =2 THEN v.v END ) ,  '' )  'Domain'
FROM a
LEFT JOIN v ON a.i = v.i
WHERE a.a =2
GROUP BY a.i

然后执行搜索，例如:

SELECT * 
FROM  `organizations` 
WHERE  `Company` LIKE  '%Apple%'

提取数据:

+----+------------+-----------+
| id | Company    | Domain    |
+----+------------+-----------+
|  3 | Apple Inc. | apple.com |
+----+------------+-----------+

如何在不使用 View 的情况下在数据透视表上执行相同的搜索(仅使用表 a 和 v)？

我在 where 子句中尝试了 case 语句:

select a.i as 'id',
ifnull (max(case when v.k = 1 then v.v end),'') 'Company',
ifnull (max(case when v.k = 2 then v.v end),'') 'Domain'
from a 
left join v on a.i = v.i 
where a.a = 2
AND CASE WHEN v.k = 1 THEN v.v LIKE  '%Apple%' ELSE TRUE END
group by a.i

但它不会隔离/提取虚拟行，它会显示所有行，但仅显示匹配的数据:

+----+------------+---------------+
| id | Company    | Domain        |
+----+------------+---------------+
|  3 | Apple Inc. | apple.com     |
|  4 |            | microsoft.com |
|  5 |            | google.com    |
+----+------------+---------------+

谢谢

Answer 1

使用子查询:

SELECT *
FROM (
   select a.i as 'id',
     ifnull (max(case when v.k = 1 then v.v end),'') 'Company',
     ifnull (max(case when v.k = 2 then v.v end),'') 'Domain'
   from a 
   left join v on a.i = v.i 
   WHERE a.a =2
   group by a.i
) AS sub
WHERE `Company` LIKE  '%Apple%'

SqlFiddleDemo

输出:

╔═════╦═════════════╦═══════════╗
║ id  ║  Company    ║  Domain   ║
╠═════╬═════════════╬═══════════╣
║  3  ║ Apple Inc.  ║ apple.com ║
╚═════╩═════════════╩═══════════╝