博客

参考：

http://www.cnblogs.com/kingthy/archive/2008/04/29/1175697.html（主要参考他）

http://blog.csdn.net/wuxiaokaixinguo/article/details/8523048

https://msdn.microsoft.com/zh-cn/dd378780.aspx

http://bbs.csdn.net/topics/370089729 （这的人说的真好，只不过我本文没用到存储过程)

1、先介绍下我的数据库结构，3个表分别是 整机mac(爷爷)—-设备dev（爸爸）—组件comp(儿子)

</pre><pre code_snippet_id="1664094" snippet_file_name="blog_20160427_1_6906197" name="code" class="cpp">

</pre><p>2、爷爷和爸爸表的查询SELECT MacSN,MAX(CASE devTypeId WHEN 2 THEN devSN ELSE 0 END) AS 显示器,MAX(CASE devTypeId WHEN 3 THEN devSN ELSE 0 END) AS 条码阅读器,MAX(CASE devTypeId WHEN 4 THEN devSN ELSE 0 END) AS 主板盒,MAX(CASE devTypeId WHEN 5 THEN devSN ELSE 0 END) AS 打印机,MAX(CASE devTypeId WHEN 6 THEN devSN ELSE 0 END) AS 打印头, MAX(CASE devTypeId WHEN 7 THEN devSN ELSE 0 END) AS 固件版本号,MAX(CASE devTypeId WHEN 8 THEN devSN ELSE 0 END) AS 电源,MAX(CASE devTypeId WHEN 9 THEN devSN ELSE 0 END) AS 键盘 FROM (SELECT a.ID,a.MacSN,b.devSN,b.devTypeId,b.Remark FROM t_machineinfo A LEFT JOIN t_deviceinfo B ON B.MacId = A.id ORDER BY a.id ) AS TMPGROUP BY MacSN</p><pre code_snippet_id="1664094" snippet_file_name="blog_20160427_1_6906197" name="code" class="cpp">

结果图5：

第二部、终结步骤

SELECT MacSN AS 0整机号,
MAX(CASE devTypeId WHEN 2 THEN devSN ELSE 0 END) AS 1显示器,
MAX(CASE devTypeId WHEN 3 THEN devSN ELSE 0 END) AS 2条码阅读器,
MAX(CASE devTypeId WHEN 4 THEN devSN ELSE 0 END) AS 3主板盒,
MAX(CASE devTypeId WHEN 5 THEN devSN ELSE 0 END) AS 4打印机,
MAX(CASE devTypeId WHEN 6 THEN devSN ELSE 0 END) AS 5打印头, 
MAX(CASE devTypeId WHEN 7 THEN devSN ELSE 0 END) AS 6固件版本号,
MAX(CASE devTypeId WHEN 8 THEN devSN ELSE 0 END) AS 7电源,
MAX(CASE devTypeId WHEN 9 THEN devSN ELSE 0 END) AS 8键盘, 
MAX(CASE compTypeId WHEN 10 THEN devSN ELSE 0 END) AS 9液晶屏, 
MAX(CASE compTypeId WHEN 11 THEN devSN ELSE 0 END) AS 10识别引擎, 
MAX(CASE compTypeId WHEN 12 THEN devSN ELSE 0 END) AS 11主板, 
MAX(CASE compTypeId WHEN 13 THEN devSN ELSE 0 END) AS 12内存, 
MAX(CASE compTypeId WHEN 14 THEN devSN ELSE 0 END) AS 13存储卡 
FROM 
(SELECT a.ID,a.MacSN,
b.devSN,b.devTypeId,b.Remark, 
c.CompSN,c.compTypeId,c.Remark AS cmpRK
FROM t_machineinfo A
LEFT JOIN t_deviceinfo B  ON B.MacId = A.id 
LEFT JOIN t_componentinfo C ON B.id = C.devId
ORDER BY a.id ) AS TMP

GROUP BY MacSN

结果：

搞定，下班~~~~