问一个sql 关于结果增加一列父节点
38安全生产考核证5C6FAEA9-0401-4BF3-9A47-BD09746CC224NULLPersonCategory0
39材料员590CC7A9-44B1-4BDD-AFE0-71CF09F23F7CNULLPersonCategory0
40造价员6282518E-6FC3-437F-888D-63474AF81AE6NULLPersonCategory0
41特种作业F7BA83CF-329A-414C-BFE5-76FEC894EC2FNULLPersonCategory0
42注册安全工程师4FC046D8-D4B0-4A1D-B89D-DC860701BACANULLPersonCategory0
43安全培训证书3F26EDE4-89C2-4433-9C3C-D9D83C97361ENULLPersonCategory0
44注册结构工程师DA152F29-FAC8-47F2-A582-4AF97B2F7F05NULLPersonCategory0
45注册造价师2EAB3280-5CB8-4A51-952F-C93FEF31DB6BNULLPersonCategory0
55A类（企业主要负责人）64248537-5E63-4F62-82CA-A04DC9D5DFE75C6FAEA9-0401-4BF3-9A47-BD09746CC224PersonCategory0
60C类（安全员）562E10CC-3940-48D6-81D8-F758F964CD715C6FAEA9-0401-4BF3-9A47-BD09746CC224PersonCategory0
63其它1165B079-DA0A-43A1-A1F0-C903CC97CD435C6FAEA9-0401-4BF3-9A47-BD09746CC224PersonCategory0
select * from stringMap where 1=1 and recordStatus=0 and standby1='PersonCategory'
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-20 11:09:09
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[name] varchar(19),[value] uniqueidentifier,[type] uniqueidentifier,[standby1] varchar(14),[recordstatus] int)
insert [huang]
select 38,'安全生产考核证','5C6FAEA9-0401-4BF3-9A47-BD09746CC224',null,'PersonCategory',0 union all
select 39,'材料员','590CC7A9-44B1-4BDD-AFE0-71CF09F23F7C',null,'PersonCategory',0 union all
select 40,'造价员','6282518E-6FC3-437F-888D-63474AF81AE6',null,'PersonCategory',0 union all
select 41,'特种作业','F7BA83CF-329A-414C-BFE5-76FEC894EC2F',null,'PersonCategory',0 union all
select 42,'注册安全工程师','4FC046D8-D4B0-4A1D-B89D-DC860701BACA',null,'PersonCategory',0 union all
select 43,'安全培训证书','3F26EDE4-89C2-4433-9C3C-D9D83C97361E',null,'PersonCategory',0 union all
select 44,'注册结构工程师','DA152F29-FAC8-47F2-A582-4AF97B2F7F05',null,'PersonCategory',0 union all
select 45,'注册造价师','2EAB3280-5CB8-4A51-952F-C93FEF31DB6B',null,'PersonCategory',0 union all
select 55,'A类(企业主要负责人)','64248537-5E63-4F62-82CA-A04DC9D5DFE7','5C6FAEA9-0401-4BF3-9A47-BD09746CC224','PersonCategory',0 union all
select 60,'C类(安全员)','562E10CC-3940-48D6-81D8-F758F964CD71','5C6FAEA9-0401-4BF3-9A47-BD09746CC224','PersonCategory',0 union all
select 63,'其它','1165B079-DA0A-43A1-A1F0-C903CC97CD43','5C6FAEA9-0401-4BF3-9A47-BD09746CC224','PersonCategory',0
--------------开始查询--------------------------
SELECT *,(SELECT id FROM huang b WHERE huang.[TYPE]=b.[value])ParentID
FROM huang
----------------结果----------------------------
/*
id name value type standby1 recordstatus ParentID
----------- ------------------- ------------------------------------ ------------------------------------ -------------- ------------ -----------
38 安全生产考核证 5C6FAEA9-0401-4BF3-9A47-BD09746CC224 NULL PersonCategory 0 NULL
39 材料员 590CC7A9-44B1-4BDD-AFE0-71CF09F23F7C NULL PersonCategory 0 NULL
40 造价员 6282518E-6FC3-437F-888D-63474AF81AE6 NULL PersonCategory 0 NULL
41 特种作业 F7BA83CF-329A-414C-BFE5-76FEC894EC2F NULL PersonCategory 0 NULL
42 注册安全工程师 4FC046D8-D4B0-4A1D-B89D-DC860701BACA NULL PersonCategory 0 NULL
43 安全培训证书 3F26EDE4-89C2-4433-9C3C-D9D83C97361E NULL PersonCategory 0 NULL
44 注册结构工程师 DA152F29-FAC8-47F2-A582-4AF97B2F7F05 NULL PersonCategory 0 NULL
45 注册造价师 2EAB3280-5CB8-4A51-952F-C93FEF31DB6B NULL PersonCategory 0 NULL
55 A类(企业主要负责人) 64248537-5E63-4F62-82CA-A04DC9D5DFE7 5C6FAEA9-0401-4BF3-9A47-BD09746CC224 PersonCategory 0 38
60 C类(安全员) 562E10CC-3940-48D6-81D8-F758F964CD71 5C6FAEA9-0401-4BF3-9A47-BD09746CC224 PersonCategory 0 38
63 其它 1165B079-DA0A-43A1-A1F0-C903CC97CD43 5C6FAEA9-0401-4BF3-9A47-BD09746CC224 PersonCategory 0 38
*/
