SQL查询-填充列取决于

Question

我的数据库逐行报告安装了软件(仅特定软件)的几个终端。下面是一个例子：

HOSTNAME | MAC ADDRESS | IP ADDRESS | SOFTWARE NAME
---------------------------------------------------
PC-AAA   | 94-da-r2-12 | xx.xx.xx.xx| TeamViewer
PC-AAA   | 94-da-r2-12 | xx.xx.xx.xx| Google Chrome
PC-BBB   | 94-da-s4-16 | xx.xx.xx.xx| TeamViewer
PC-BBB   | 94-da-s4-16 | xx.xx.xx.xx| Google Chrome 
etc.

我如何构建我的查询，以便为每个软件创建一个列，如下例所示？

HOSTNAME | MAC ADDRESS | IP ADDRESS | TeamViewer | Google Chrome
----------------------------------------------------------------
PC-AAA   | 94-da-r2-12 | xx.xx.xx.xx| TeamViewer | 
PC-AAA   | 94-da-r2-12 | xx.xx.xx.xx|            | Google Chrome
PC-BBB   | 94-da-s4-16 | xx.xx.xx.xx| TeamViewer |
PC-BBB   | 94-da-s4-16 | xx.xx.xx.xx|            | Google Chrome

Answer 1

这是如何透视SQL查询...SQL server支持pivot、MySQL和其他不支持的服务器。

如果有固定数量的软件，则可以使用CASE WHEN和GROUP BY

SELECT
HOSTNAME, MAC ADDRESS, IP_ADDRESS, 
CASE
WHEN (SOFTWARE_NAME = 'TeamViewer') THen 'TeamViewer'
ELSE ''
END AS TeamViewer
CASE
WHEN (SOFTWARE_NAME = 'GoogleChrome') THen 'GoogleChrome'
ELSE ''
END AS GoogleChrome

FROM
log_table
GROUP BY
HOSTNAME, MAC ADDRESS, IP_ADDRESS

Answer 2

使用case表达式：

SELECT HOSTNAME, MAC_ADDRESS, IP_ADDRESS,
       (CASE WHEN SOFTWARE_NAME = 'TeamViewer' THEN SOFTWARE_NAME END) as TeamViewer,
       (CASE WHEN SOFTWARE_NAME = 'GoogleChrome' THEN SOFTWARE_NAME END) as GoogleChrome
FROM log_table;

如果您希望每个主机和地址都有一行，那么可以使用聚合：

SELECT HOSTNAME, MAC_ADDRESS, IP_ADDRESS,
       MAX(CASE WHEN SOFTWARE_NAME = 'TeamViewer' THEN SOFTWARE_NAME END) as TeamViewer,
       MAX(CASE WHEN SOFTWARE_NAME = 'GoogleChrome' THEN SOFTWARE_NAME END) as GoogleChrome
FROM log_table
GROUP BY HOSTNAME, MAC_ADDRESS, IP_ADDRESS;

这不是您指定的输出，但它似乎更有用。