我得到了下面这条select语句,它可以获取二级子记录,但是我想要更深层次的记录,有人能帮忙吗?
SELECT
id_mobile AS ID_PROJETO,
UM.qtd_UC,
AM.qtd_AMBIENTE
FROM projetos_mobile AS PM
LEFT JOIN (
SELECT
COUNT(id) AS qtd_UC,
projeto,
data_hora_importacao,
id_uc_mobile
FROM ucs_mobile
WHERE data_hora_importacao = '2015-05-15 17:21:02'
GROUP BY projeto) AS UM
ON PM.id_mobile = UM.projeto
LEFT JOIN (
SELECT
COUNT(id_uc_mobile) AS qtd_AMBIENTE,
id_uc_mobile
FROM ucs_mobile
LEFT JOIN (
SELECT
uc
FROM ambientes_mobile AS s
WHERE data_hora_importacao = '2015-05-15 17:21:02') AS G
ON G.uc = ucs_mobile.id_uc_mobile
WHERE data_hora_importacao = '2015-05-15 17:21:02') AS AM
ON UM.id_uc_mobile = AM.id_uc_mobile
WHERE PM.data_hora_importacao = '2015-05-15 17:21:02'
http://sqlfiddle.com/#!9/2eecf
如果有人想尝试解决方案,这里有一个 SQLFiddle。我有特定的层次结构:项目>使用者>环境>区域>测量。
ucs_mobile.projeto refers to projetos_mobile.id_mobile
ambientes_mobile.uc refers to ucs_mobile.id_uc_mobile
secoes_iluminacao_mobile.ambiente refers to ambientes_mobile.id_ambiente_mobile
我需要对传入的父级进行每个子级的计数,我将有5个函数返回给定父级的每个子级的计数,例如,对于项目父级,我应该有count(ucs),count(ambientes),count(secoes),count(medicoes)。
所以,希望你们能帮助我。数据库非常丑陋,但这就是我拥有的。感谢任何帮助。