SQL - Agrupar irmãos de pais diferentes (Composição Familiar)
23/10/2014
0
Boa tarde, a todos os amigos do fórum.
Necessito de uma ajuda de todos vocês.
-Necessidade: Listar os alunos que são irmãos (consanguíneos ou não)
- O que foi realizado: Criei uma query que consegue pesquisar todos os irmãos e seus respectivos pais, seja os pais consanguíneos ou não.
- Problemática: Preciso agrupar os irmãos, porem ao colocar no parâmetro do GROUP BY para agrupar o CODMAE quando o pai não é consanguíneo de uns dos alunos que são irmãos, o que ocorre é que o grup by não agrupa essa família.
Código da Query
SELECT
EALUNOS.MATRICULA,
EALUNOS.NOME,
EMATRICPL.CODTUR,
EALUNOS.MAE,
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.MAE = EALUNOS.MAE) AS TOTALMAE,
EALUNOS.PAI,
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.PAI = EALUNOS.PAI) AS TOTALPAI,
((SELECT COUNT (EA.MATRICULA) FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.MAE = EALUNOS.MAE)
+
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.PAI = EALUNOS.PAI)) AS QDTOTAL
FROM
EALUNOS LEFT OUTER JOIN EMATRICPL ON EMATRICPL.MATALUNO = EALUNOS.MATRICULA
WHERE
EMATRICPL.PERLETIVO='2014' AND
EMATRICPL.SITMAT='01' AND
EMATRICPL.CODFILIAL = '7'
GROUP BY
EALUNOS.MAE, EALUNOS.PAI,
EALUNOS.MATRICULA,
EALUNOS.NOME,
EMATRICPL.CODTUR,
EMATRICPL.PERLETIVO,
EMATRICPL.SITMAT,
EMATRICPL.CODFILIAL
HAVING
((SELECT COUNT (EA.MATRICULA) FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.MAE = EALUNOS.MAE)
+
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.PAI = EALUNOS.PAI)) =3
Vejamos o resultado obtido
[img]http://arquivo.devmedia.com.br/forum/imagem/392416-20141023-160717.jpg[/img]
Como eu posso fazer para agrupar os dois irmãos Arturo Garcia e Rodrigo Garcia, sendo que o pai deles é o mesmo porem a mãe não é.
Agradeço a todos pela ajuda.
MATRICULA NOME CODTUR MAE TM PAI TP QDTOTAL
---------------------------------------------------------------------------------------------------------------------------------------
3819 | Pedro Carvalho |2ª série A |12108 |2 |12107 |1 |3 |
5546 | Samuel Filho |8º ano A |12108 |2 |20289 |1 |3 |
6397 | Artur Santali |5º ano B |18948 |2 |18947 |1 |3 |
8047 | Leonardo Santali |1º ano C| |18948 |2 |23317 |1 |3 |
----> 6427 | Arturo Garcia |5º ano B |19039 |1 |19038 |2 |3 |
8002 | Victor Zangar |2º ano A |19084 |2 |19082 |1 |3 |
6442 | Mariana Zangar |5º ano A |19084 |2 |19083 |1 |3 |
3401 | Victor Basset |3ª série B |22833 |2 |5572 |1 |3 |
7857 | Antonio Sulzba |4º ano C |22833 |2 |22832 |1 |3 |
----> 8011 | Rodrigo Garcia |2º ano D |23219 |1 |19038 |2 |3 |
Necessito de uma ajuda de todos vocês.
-Necessidade: Listar os alunos que são irmãos (consanguíneos ou não)
- O que foi realizado: Criei uma query que consegue pesquisar todos os irmãos e seus respectivos pais, seja os pais consanguíneos ou não.
- Problemática: Preciso agrupar os irmãos, porem ao colocar no parâmetro do GROUP BY para agrupar o CODMAE quando o pai não é consanguíneo de uns dos alunos que são irmãos, o que ocorre é que o grup by não agrupa essa família.
Código da Query
SELECT
EALUNOS.MATRICULA,
EALUNOS.NOME,
EMATRICPL.CODTUR,
EALUNOS.MAE,
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.MAE = EALUNOS.MAE) AS TOTALMAE,
EALUNOS.PAI,
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.PAI = EALUNOS.PAI) AS TOTALPAI,
((SELECT COUNT (EA.MATRICULA) FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.MAE = EALUNOS.MAE)
+
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.PAI = EALUNOS.PAI)) AS QDTOTAL
FROM
EALUNOS LEFT OUTER JOIN EMATRICPL ON EMATRICPL.MATALUNO = EALUNOS.MATRICULA
WHERE
EMATRICPL.PERLETIVO='2014' AND
EMATRICPL.SITMAT='01' AND
EMATRICPL.CODFILIAL = '7'
GROUP BY
EALUNOS.MAE, EALUNOS.PAI,
EALUNOS.MATRICULA,
EALUNOS.NOME,
EMATRICPL.CODTUR,
EMATRICPL.PERLETIVO,
EMATRICPL.SITMAT,
EMATRICPL.CODFILIAL
HAVING
((SELECT COUNT (EA.MATRICULA) FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.MAE = EALUNOS.MAE)
+
(SELECT COUNT (EA.MATRICULA)
FROM EALUNOS EA LEFT OUTER JOIN EMATRICPL EMA ON EMA.MATALUNO = EA.MATRICULA
WHERE EMA.PERLETIVO = EMATRICPL.PERLETIVO AND EMA.SITMAT = EMATRICPL.SITMAT AND EMA.CODFILIAL = EMATRICPL.CODFILIAL AND EA.PAI = EALUNOS.PAI)) =3
Vejamos o resultado obtido
[img]http://arquivo.devmedia.com.br/forum/imagem/392416-20141023-160717.jpg[/img]
Como eu posso fazer para agrupar os dois irmãos Arturo Garcia e Rodrigo Garcia, sendo que o pai deles é o mesmo porem a mãe não é.
Agradeço a todos pela ajuda.
MATRICULA NOME CODTUR MAE TM PAI TP QDTOTAL
---------------------------------------------------------------------------------------------------------------------------------------
3819 | Pedro Carvalho |2ª série A |12108 |2 |12107 |1 |3 |
5546 | Samuel Filho |8º ano A |12108 |2 |20289 |1 |3 |
6397 | Artur Santali |5º ano B |18948 |2 |18947 |1 |3 |
8047 | Leonardo Santali |1º ano C| |18948 |2 |23317 |1 |3 |
----> 6427 | Arturo Garcia |5º ano B |19039 |1 |19038 |2 |3 |
8002 | Victor Zangar |2º ano A |19084 |2 |19082 |1 |3 |
6442 | Mariana Zangar |5º ano A |19084 |2 |19083 |1 |3 |
3401 | Victor Basset |3ª série B |22833 |2 |5572 |1 |3 |
7857 | Antonio Sulzba |4º ano C |22833 |2 |22832 |1 |3 |
----> 8011 | Rodrigo Garcia |2º ano D |23219 |1 |19038 |2 |3 |
Ruy Guerra
Curtir tópico
+ 0
Responder
Posts
23/10/2014
Emerson Brito
Boa Tarde Prezado!
Olha pelo que eu analisei do seu problema é bem simples:
Copiei o script abaixo que gera uma tabela com exemplo (tabela temporária no SQL), para você entender a adaptar a sua query.
--===============================================================
-- Apaga uma tabela temporária caso exista...
if exists (select * from tempdb..sysobjects (nolock) where [name] like '#tmp1_%')
begin drop table #tmp1 end
-- Cria uma Tabela de Teste para Exemplo...
SELECT TOP 0 identity(int,1,1) as id,
cast(null as varchar(100)) as 'Filho',
cast(null as varchar(100)) as 'Pai',
cast(null as varchar(100)) as 'Mae'
INTO #tmp1
-- Mostra Tabela Criada Vazia para Exemplo...
SELECT * from #tmp1
-- Inserindo Registros para Exemplo...
INSERT INTO #tmp1
SELECT 'Ana' as Filho, 'Jose' as PAI, 'Maria' as MAE
UNION
SELECT 'Beatriz' as Filho, 'Jose' as PAI, 'Maria' as MAE
UNION
SELECT 'Carlos' as Filho, 'Jose' as PAI, 'Claudia' as MAE
UNION
SELECT 'Daniel' as Filho, 'Jose' as PAI, 'Claudia' as MAE
UNION
SELECT 'Elias' as Filho, 'Raimundo' as PAI, 'Maria' as MAE
UNION
SELECT 'EMERSON' as Filho, 'ENIO' as PAI, 'EUNICE' as MAE
-- Registros Inseridos - Sendo que o id 6 (EMERSON) não é consanguineo...
SELECT * FROM #tmp1
-- Sua Problemática Resolvida!!!
SELECT t1.*
FROM #tmp1 t1
WHERE (PAI IN (SELECT Pai FROM #tmp1 group by Pai having count(*) > 1)
OR MAE IN (SELECT Mae FROM #tmp1 group by Mae having count(*) > 1))
--===============================================================
Espero ter lhe ajudado!
Um abraço!
Olha pelo que eu analisei do seu problema é bem simples:
Copiei o script abaixo que gera uma tabela com exemplo (tabela temporária no SQL), para você entender a adaptar a sua query.
--===============================================================
-- Apaga uma tabela temporária caso exista...
if exists (select * from tempdb..sysobjects (nolock) where [name] like '#tmp1_%')
begin drop table #tmp1 end
-- Cria uma Tabela de Teste para Exemplo...
SELECT TOP 0 identity(int,1,1) as id,
cast(null as varchar(100)) as 'Filho',
cast(null as varchar(100)) as 'Pai',
cast(null as varchar(100)) as 'Mae'
INTO #tmp1
-- Mostra Tabela Criada Vazia para Exemplo...
SELECT * from #tmp1
-- Inserindo Registros para Exemplo...
INSERT INTO #tmp1
SELECT 'Ana' as Filho, 'Jose' as PAI, 'Maria' as MAE
UNION
SELECT 'Beatriz' as Filho, 'Jose' as PAI, 'Maria' as MAE
UNION
SELECT 'Carlos' as Filho, 'Jose' as PAI, 'Claudia' as MAE
UNION
SELECT 'Daniel' as Filho, 'Jose' as PAI, 'Claudia' as MAE
UNION
SELECT 'Elias' as Filho, 'Raimundo' as PAI, 'Maria' as MAE
UNION
SELECT 'EMERSON' as Filho, 'ENIO' as PAI, 'EUNICE' as MAE
-- Registros Inseridos - Sendo que o id 6 (EMERSON) não é consanguineo...
SELECT * FROM #tmp1
-- Sua Problemática Resolvida!!!
SELECT t1.*
FROM #tmp1 t1
WHERE (PAI IN (SELECT Pai FROM #tmp1 group by Pai having count(*) > 1)
OR MAE IN (SELECT Mae FROM #tmp1 group by Mae having count(*) > 1))
--===============================================================
Espero ter lhe ajudado!
Um abraço!
Responder
23/10/2014
Ruy Guerra
Olá Emerson!
Como vai?
Muito obrigado pelo retorno, agradeço pela ajuda.
bom... vamos lá, testei o que você descreveu e infelizmente não atendeu, deixa eu explicar detalhadamente.
- Eu listei todos os irmãos, porem eu tenho que agrupa-los, para agrupar utilizei o campo do código "MAE", então o group by entende que aquele campo é o campo chave para agrupamento (Figura 01), porem eu tenho um caso em que exite um pai "Cod: 19038" que ele tem dois filhos porem com mães diferentes "cods: 19039 e 23219", então eu gostaria de agrupar esse pai (Figura 02).
Como faço isso?
(Figura 01) Agrupado por Código da Mãe.
[img]http://arquivo.devmedia.com.br/forum/imagem/392416-20141023-180740.jpg[/img]
(Figura 02) Resultado de como deve ficar - agrupo todas as mães identificas que possuem o mesmo pais e que for diferente desse universo é agrupado pelo código do pai.
[img]http://arquivo.devmedia.com.br/forum/imagem/392416-20141023-181447.jpg[/img]
Aguardo o seu retorno, muito obrigado.
Como vai?
Muito obrigado pelo retorno, agradeço pela ajuda.
bom... vamos lá, testei o que você descreveu e infelizmente não atendeu, deixa eu explicar detalhadamente.
- Eu listei todos os irmãos, porem eu tenho que agrupa-los, para agrupar utilizei o campo do código "MAE", então o group by entende que aquele campo é o campo chave para agrupamento (Figura 01), porem eu tenho um caso em que exite um pai "Cod: 19038" que ele tem dois filhos porem com mães diferentes "cods: 19039 e 23219", então eu gostaria de agrupar esse pai (Figura 02).
Como faço isso?
(Figura 01) Agrupado por Código da Mãe.
[img]http://arquivo.devmedia.com.br/forum/imagem/392416-20141023-180740.jpg[/img]
(Figura 02) Resultado de como deve ficar - agrupo todas as mães identificas que possuem o mesmo pais e que for diferente desse universo é agrupado pelo código do pai.
[img]http://arquivo.devmedia.com.br/forum/imagem/392416-20141023-181447.jpg[/img]
Aguardo o seu retorno, muito obrigado.
Responder
24/10/2014
Ruy Guerra
Boa tarde, Emerson!
Vou aplicar a logica, os campos TOTALPAI E TOTALMAE, indica quantos alunos estão atrelados em seu código "MAE e PAI"
Vou aplicar a logica, os campos TOTALPAI E TOTALMAE, indica quantos alunos estão atrelados em seu código "MAE e PAI"
Responder
24/10/2014
Ruy Guerra
Emerson coloquei um parâmetro Where filtrando os valores dos campos TOTALMAE e TOTALPAI, deu certo.
(SELECT MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL
FROM ALUNOS
WHERE TOTALPAI = 2 GROUP BY PAI, MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL HAVING COUNT(*) = 1)
UNION
(SELECT MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL
FROM ALUNOS
WHERE TOTALPAI = 1 GROUP BY MAE, MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL HAVING COUNT(*) = 1)
[img]http://arquivo.devmedia.com.br/forum/imagem/392416-20141024-162602.jpg[/img]
(SELECT MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL
FROM ALUNOS
WHERE TOTALPAI = 2 GROUP BY PAI, MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL HAVING COUNT(*) = 1)
UNION
(SELECT MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL
FROM ALUNOS
WHERE TOTALPAI = 1 GROUP BY MAE, MATRICULA, NOME,CODTUR,MAE,TOTALMAE,PAI,TOTALPAI, QDTOTAL HAVING COUNT(*) = 1)
[img]http://arquivo.devmedia.com.br/forum/imagem/392416-20141024-162602.jpg[/img]
Responder
Clique aqui para fazer login e interagir na Comunidade :)
