还是UNION ALL

 感谢Tiger_Zhao的指点：http://topic.youkuaiyun.com/u/20090331/09/063f9142-1e6c-4145-80a0-a23be6ad9e70.html?seed=1487681187

 

我有个表tblNode，有4个字段：lngNodeId(主键），lngFatherId, strDetailTable, lngDetailId

另外有若干表结构完全相同的细节表，表名分别是action, project, logItem等。字段有lngId（主键），strName

tblNode记录和细节表有一定的对应关系：tblNode表的strDetailTable字段存的是细节表名，即"action", "project", "logItem"等，指明当前记录是哪种细节; tblNode表的lngDetailId字段指明当前记录的细节ID，对应于细节表里的lngId字段。

现在我要做一个查询，结果集返回3个字段：lngNodeId，lngFatherId，strName。其中前2个字段来自tblNode，而strName来自相应的细节表的相应记录的strName字段。可以如下这样写：

 

SELECT N.lngNodeID, D.strName FROM tblTestTree2 N inner JOIN ( SELECT lngID, strName, 'action' AS DetailType FROM action UNION ALL SELECT lngID, strName, 'project' AS DetailType FROM project UNION ALL SELECT lngID, strName, 'logItem' AS DetailType FROM logItem UNION ALL SELECT lngID, strName, 'logItem1' AS DetailType FROM logItem1 UNION ALL SELECT lngID, strName, 'naNode' AS DetailType FROM naNode UNION ALL SELECT lngID, strName, 'workType' AS DetailType FROM workType ) D ON D.lngID = N.lngDetailID AND D.DetailType = N.strDetailTable