Jmsilva Posted October 21, 2022 Report Share Posted October 21, 2022 Em uma tabela "products" com os campos 'id', 'cesta' e 'Itens', como montar um comando SQL para fazer uma análise em 'X' e descobrir os códigos invertidos. id cesta itens 1 C1 I1 2 C1 I2 3 I1 C1 Resultado: Id 1 e 3 Grato! JMSILVA Quote Link to comment Share on other sites More sharing options...
CIACPD Posted October 22, 2022 Report Share Posted October 22, 2022 Bom dia Colega, Não sei se é isto, mas... CREATE TABLE `products` ( `ID` INTEGER(11) NOT NULL AUTO_INCREMENT, `CESTA` CHAR(2), `ITENS` CHAR(2), PRIMARY KEY (`ID`))ENGINE=InnoDB; COMMIT; INSERT INTO products ( CESTA , ITENS ) VALUES( 'C1' , 'I1' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'I2' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C3' , 'I3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C4' , 'I4' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C5' , 'I5' ); COMMIT; INSERT INTO products ( CESTA , ITENS ) VALUES( 'C1' , 'I2' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C1' , 'I3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'I3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'I4' ); COMMIT; INSERT INTO products ( CESTA , ITENS ) VALUES( 'I1' , 'I2' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'I1' , 'I3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'I2' , 'C3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'I2' , 'C4' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'C3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'C4' ); COMMIT; SELECT * FROM products WHERE CESTA LIKE 'I%' UNION ALL SELECT * FROM products WHERE ITENS LIKE 'C%' ; Daniel Segura Quote Link to comment Share on other sites More sharing options...
Jmsilva Posted October 24, 2022 Author Report Share Posted October 24, 2022 Em 22/10/2022 at 11:35, CIACPD disse: Bom dia Colega, Não sei se é isto, mas... CREATE TABLE `products` ( `ID` INTEGER(11) NOT NULL AUTO_INCREMENT, `CESTA` CHAR(2), `ITENS` CHAR(2), PRIMARY KEY (`ID`))ENGINE=InnoDB; COMMIT; INSERT INTO products ( CESTA , ITENS ) VALUES( 'C1' , 'I1' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'I2' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C3' , 'I3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C4' , 'I4' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C5' , 'I5' ); COMMIT; INSERT INTO products ( CESTA , ITENS ) VALUES( 'C1' , 'I2' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C1' , 'I3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'I3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'I4' ); COMMIT; INSERT INTO products ( CESTA , ITENS ) VALUES( 'I1' , 'I2' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'I1' , 'I3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'I2' , 'C3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'I2' , 'C4' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'C3' ); INSERT INTO products ( CESTA , ITENS ) VALUES( 'C2' , 'C4' ); COMMIT; SELECT * FROM products WHERE CESTA LIKE 'I%' UNION ALL SELECT * FROM products WHERE ITENS LIKE 'C%' ; Daniel Segura Obrigado Daniel, pela resposta! Após pesquisa consegui montar o comando sql que precisava ficou assim: SELECT a.cesta, a.itens FROM products AS a WHERE EXISTS (SELECT b.cesta, b.itens FROM products AS b WHERE b.cesta != b.itens AND a.cesta = b.itens AND a.itens = b.cesta) ; Valeu! Objetivo é eliminar registros inválidos após importação dos dados. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.