56,679
社区成员
发帖
与我相关
我的任务
分享
SELECT
t1.Name_Zh,
t1.LevelName,
t2.FatherID,
t2.NodeID,
t2.SP_ID,
(select CASE count(*) WHEN 0 THEN 'True' ELSE 'False' END
from taxon where FatherID = t2.NodeID ) as EndNode
FROM plantsp t1, taxon t2 ,taxonsystems t3
where t3.ID = t2.S_ID
and t2.SP_ID = t1.ID
and t3.id= '2802'
and t1.LevelName = 'City'
SELECT t1.Name_Zh,
t1.LevelName,
t2.FatherID,
t2.NodeID,
t2.SP_ID,
case when t.FatherID is null then 'True' else 'False' end EndNode
FROM plantsp t1,
taxon t2
left join taxon t on t.FatherID = t2.NodeID,
taxonsystems t3
WHERE t3.ID = t2.S_ID
AND t2.SP_ID = t1.ID
AND t3.id = '2802'
AND t1.LevelName = 'City'
group by t2.NodeID
CREATE TABLE `taxon` (
`NodeID` varchar(36) NOT NULL COMMENT '节点数据的唯一ID',
`S_ID` char(36) NOT NULL COMMENT '分类树ID',
`SP_ID` varchar(36) NOT NULL COMMENT '物种表ID(plantsp表ID,物种名称、地名都保存于此)。如S_ID非空,可以到plantsp表查询更多信息。',
`S_SN` varchar(20) NOT NULL DEFAULT '' COMMENT '各分类树内的节点代码',
`LevelID` char(36) NOT NULL COMMENT '节点名称等级',
`IsEndNode` enum('Yes','No') NOT NULL DEFAULT 'No' COMMENT '是否是分类树末节点',
`SubSystemCount` int(11) NOT NULL DEFAULT '0' COMMENT '以此节点为根节点的分类树数量',
`FatherID` varchar(36) NOT NULL DEFAULT '' COMMENT '父节点',
`PrevID` varchar(36) NOT NULL DEFAULT '' COMMENT '前一节点',
`LocationSN` int(11) NOT NULL DEFAULT '0' COMMENT '',
`Description` varchar(10000) NOT NULL DEFAULT '' COMMENT '节点描述',
`NameStatus` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '节点名称状态',
`Agrees` int(4) NOT NULL DEFAULT '0' COMMENT '',
`DisAgrees` int(4) NOT NULL DEFAULT '0' COMMENT '',
`Editor` varchar(20) NOT NULL COMMENT '',
`EditTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
`EditMemo` varchar(200) NOT NULL DEFAULT '' COMMENT '',
`NodeNameZh` varchar(100) DEFAULT NULL COMMENT '节点中文名',
`NodeNameEn` varchar(100) DEFAULT NULL COMMENT '节点英文名',
`NodePhotos` text COMMENT '节点照片页',
`NodeSpecimens` text COMMENT '节点标本页',
`NodeWikiPage` varchar(100) DEFAULT NULL COMMENT '节点百科信息页面',
`NodeRelatingNames` text,
`NodeRefs` text COMMENT '节点参考文献',
PRIMARY KEY (`NodeID`),
UNIQUE KEY `TreeLocation` (`S_ID`,`FatherID`,`PrevID`),
KEY `SpID` (`S_ID`,`SP_ID`),
KEY `SP` (`SP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;