如何将两个查询连接起来。子查询？

Question

我可以让它在两个独立的查询中工作，但不能在一个查询中工作。有人能帮帮我吗？我需要这样的输出：

+-----------------------------------------------------------------------------------------------------+
|    PARENT_AK    |   PARENT_RK   |PARENT_RESOURCE_NAME|  C_RESOURCE_NAME   |   C_AK    |   C_RK      |
+-----------------------------------------------------------------------------------------------------+
|    CONTAINER    | LOB           |SYSTEMROLE          | DEV                |CONTAINER  |LOB Options  |
+-----------------------------------------------------------------------------------------------------+
|    CONTAINER    | LOB           |SYSTEMROLE          | PRODUCTION         |CONTAINER  |LOB Options  |
+-----------------------------------------------------------------------------------------------------+
|    CONTAINER    | LOB           |SYSTEMROLE          | TEST               |CONTAINER  |LOB Options  |
+-----------------------------------------------------------------------------------------------------+
|    CONTAINER    | LOB           |SYSTEMROLE          | UAT                |CONTAINER  |LOB Options  |
+-----------------------------------------------------------------------------------------------------+
|    CONTAINER    | LOB           |SERVER_FUNCTION     | APPLICATION SERVER |CONTAINER  |LOB Options  |
+-----------------------------------------------------------------------------------------------------+
|    CONTAINER    | LOB           |SERVER_FUNCTION     | DATABASE SERVER    |CONTAINER  |LOB Options  |
+-----------------------------------------------------------------------------------------------------+
|    CONTAINER    | LOB           |SERVER_FUNCTION     | WEB SERVER         |CONTAINER  |LOB Options  |
+-----------------------------------------------------------------------------------------------------+

查询1：

select 
'CONTAINER' as PARENT_AK, 
'LOB' as PARENT_RK, 
'SYSTEMROLE' as PARENT_RESOURCE_NAME, 
SYSTEMROLE as C_RESOURCE_NAME, 
'CONTAINER' as C_AK, 
'LOB Options' as C_RK
FROM CMDB
GROUP by C_RESOURCE_NAME;

查询2：

select
'CONTAINER' as PARENT_AK, 
'LOB' as PARENT_RK, 
'SERVER_FUNCTION' as PARENT_RESOURCE_NAME, 
SERVER_FUNCTION as C_RESOURCE_NAME, 
'CONTAINER' as C_AK, 
'LOB Options' as C_RK
FROM CMDB
GROUP by C_RESOURCE_NAME;

表(CMDB)：

+-------------------------------------------------+
|    NAME    |   SYSTEMROLE   |  SERVER_FUNCTION  |
+-------------------------------------------------+
|    Server1 |    Test        |APPLICATION SERVER |
+-------------------------------------------------+
|    Server2 |    PRODUCTION  |APPLICATION SERVER |
+-------------------------------------------------+
|    Server3 |    UAT         |DATABASE SERVER    |
+-------------------------------------------------+
|    Server4 |    DEV         |WEB SERVER         |
+-------------------------------------------------+
|    Server5 |    DEV         |WEB SERVER         |
+-------------------------------------------------+

SQLFiddle：http://www.sqlfiddle.com/#!2/08e6a/12

Answer 1

您正在尝试UNPIVOT您的表，但据我所知，MySQL还没有内置UNPIVOT功能。因此，你将不得不诉诸于你正在做的事情。注意，您可以使用联合向服务器发出单个请求，而不是两个请求(演示)：

select 
'CONTAINER' as PARENT_AK, 
'LOB' as PARENT_RK, 
'SYSTEMROLE' as PARENT_RESOURCE_NAME, 
SYSTEMROLE as C_RESOURCE_NAME, 
'CONTAINER' as C_AK, 
'LOB Options' as C_RK
FROM CMDB
GROUP by C_RESOURCE_NAME;

UNION ALL

select
'CONTAINER' as PARENT_AK, 
'LOB' as PARENT_RK, 
'SERVER_FUNCTION' as PARENT_RESOURCE_NAME, 
SERVER_FUNCTION as C_RESOURCE_NAME, 
'CONTAINER' as C_AK, 
'LOB Options' as C_RK
FROM CMDB
GROUP by C_RESOURCE_NAME;

如果您使用的是Server 2008，则可以使用UNPIVOT获得结果，正如您在注释中指出的那样：

select
  'CONTAINER' as PARENT_AK, 
  'LOB' as PARENT_RK, 
  PARENT_RESOURCE_NAME, C_RESOURCE_NAME,
  'CONTAINER' as C_AK, 
  'LOB Options' as C_RK
FROM
(
  SELECT CI_NAME, SYSTEMROLE, SERVER_FUNCTION
  FROM CMDB
) x
UNPIVOT
(
  C_RESOURCE_NAME FOR PARENT_RESOURCE_NAME IN (SYSTEMROLE, SERVER_FUNCTION)
) p

SQL示例

Answer 2

可以在MySQL：http://dev.mysql.com/doc/refman/5.0/en/union.html中使用UNION语句

SELECT
'CONTAINER' as PARENT_AK, 
'LOB' as PARENT_RK, 
'SYSTEMROLE' as PARENT_RESOURCE_NAME, 
SYSTEMROLE as C_RESOURCE_NAME, 
'CONTAINER' as C_AK, 
'LOB Options' as C_RK
FROM CMDB
GROUP by C_RESOURCE_NAME
UNION SELECT
'CONTAINER' as PARENT_AK, 
'LOB' as PARENT_RK, 
'SERVER_FUNCTION' as PARENT_RESOURCE_NAME, 
SERVER_FUNCTION as C_RESOURCE_NAME, 
'CONTAINER' as C_AK, 
'LOB Options' as C_RK
FROM CMDB
GROUP by C_RESOURCE_NAME;

Answer 3

可以使用UNION组合两个或多个SELECT语句的结果集。

注意: UNION中的每个SELECT语句必须有相同的列数。这些列还必须具有类似的数据类型。此外，每个SELECT语句中的列必须按照相同的顺序排列。

这样会对你更有帮助。union.asp

SELECT
'CONTAINER' AS PARENT_AK, 
'LOB' AS PARENT_RK, 
'SYSTEMROLE' AS PARENT_RESOURCE_NAME, 
SYSTEMROLE AS C_RESOURCE_NAME, 
'CONTAINER' AS C_AK, 
'LOB Options' AS C_RK
FROM CMDB
GROUP BY C_RESOURCE_NAME

UNION

SELECT
'CONTAINER' AS PARENT_AK, 
'LOB' AS PARENT_RK, 
'SERVER_FUNCTION' AS PARENT_RESOURCE_NAME, 
SERVER_FUNCTION AS C_RESOURCE_NAME, 
'CONTAINER' AS C_AK, 
'LOB Options' AS C_RK
FROM CMDB
GROUP BY C_RESOURCE_NAME