Iniciar sesión
Crear cuenta
Crear cuentaCrear cuenta
mysql> SELECT * FROM tabla1;
+---------+------+
| nombre | id |
+---------+------+
| Primero | 1 |
| Primero | 2 |
| Primero | 3 |
| Primero | 6 |
| Segundo | 1 |
| Segundo | 2 |
| Segundo | 4 |
| Tercero | 1 |
| Tercero | 7 |
| Cuarto | 3 |
| Quinto | 8 |
+---------+------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM tabla2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> SELECT tabla1.*
-> FROM tabla1
-> INNER JOIN tabla2 ON tabla1.id = tabla2.id;
+---------+------+
| nombre | id |
+---------+------+
| Primero | 1 |
| Primero | 2 |
| Primero | 3 |
| Segundo | 1 |
| Segundo | 2 |
| Tercero | 1 |
| Cuarto | 3 |
+---------+------+
7 rows in set (0.00 sec)
| Primero | 1 |
| Primero | 1 |
| Primero | 2 |
| Primero | 2 |
| Primero | 2 |
mysql> SELECT tabla1.*, count(tabla1.id) suma
-> FROM tabla1
-> INNER JOIN tabla2 ON tabla1.id = tabla2.id
->GROUP BY nombre ORDER BY DESC;
mysql> SELECT * FROM tabla1;
+---------+------++------+
| nombre | id | cantidad
+---------+------++------+
| Primero | 1 | 1
| Primero | 2 | 1
| Primero | 3 | 2
| Primero | 6 | 1
| Segundo | 1 | 3
| Segundo | 2 | 1
| Segundo | 4 | 2
| Tercero | 1 | 1
| Tercero | 7 | 1
| Cuarto | 3 | 2
| Quinto | 8 | 3
+---------+------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM tabla1;
+---------+------+----------+
| nombre | id | cantidad |
+---------+------+----------+
| Primero | 1 | 1 |
| Primero | 2 | 1 |
| Primero | 3 | 2 |
| Primero | 6 | 1 |
| Segundo | 1 | 3 |
| Segundo | 2 | 1 |
| Segundo | 4 | 2 |
| Tercero | 1 | 1 |
| Tercero | 7 | 1 |
| Cuarto | 3 | 2 |
| Quinto | 8 | 3 |
+---------+------+----------+
11 rows in set (0.06 sec)
mysql> SELECT * FROM tabla2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 3 |
+------+
4 rows in set (0.00 sec)
mysql> SELECT id, COUNT(id) cantidad
-> FROM tabla2
-> GROUP BY id;
+------+----------+
| id | cantidad |
+------+----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
+------+----------+
3 rows in set (0.00 sec)
mysql> SELECT *
-> FROM tabla1
-> INNER JOIN
-> ( SELECT id, COUNT(id) cantidad
-> FROM tabla2
-> GROUP BY id ) t2
-> ON tabla1.id = t2.id AND tabla1.cantidad = t2.cantidad;
+---------+------+----------+------+----------+
| nombre | id | cantidad | id | cantidad |
+---------+------+----------+------+----------+
| Primero | 1 | 1 | 1 | 1 |
| Primero | 2 | 1 | 2 | 1 |
| Primero | 3 | 2 | 3 | 2 |
| Segundo | 2 | 1 | 2 | 1 |
| Tercero | 1 | 1 | 1 | 1 |
| Cuarto | 3 | 2 | 3 | 2 |
+---------+------+----------+------+----------+
6 rows in set (0.00 sec)
SELECT * , COUNT( Tabla1.id) total
FROM Tabla1
INNER JOIN (SELECT Tabla2.id, COUNT( Tabla2.id) cantidad
FROM Tabla2
GROUP BY Tabla2.id)Tabla2 ON Tabla1.id= Tabla2.id
AND Tabla2.cantidad <= Tabla1.CANT
AND Tabla1.total = COUNT(Tabla2.id)
GROUP BY Tabla1.DOCU, Tabla1.TIED
ORDER BY total DESC
como ayuda extra INVESTIGA LA CLÁUSULA HAVING y cómo se usa con el operador COUNT...
eso es lo único que falta...
SELECT * , COUNT( Tabla1.id) total
FROM Tabla1
INNER JOIN (SELECT Tabla2.id, COUNT( Tabla2.id) cantidad
FROM Tabla2
GROUP BY Tabla2.id)Tabla2 ON Tabla1.id= Tabla2.id
AND Tabla2.cantidad <= Tabla1.cantidad
GROUP BY Tabla1.NombreHAVING total = (SELECT COUNT ( Tabla2.id ) FROM Tabla2)
ORDER BY total DESC
+---------+------+----------+------+----------+-----------+
| nombre | id | cantidad | id | cantidad | color
+---------+------+----------+------+----------+-------------+
| Primero | 1 | 1 | 1 | 1 | rojo
| Primero | 1 | 1 | 1 | 1 | azul
| Primero | 2 | 1 | 2 | 1 | rojo
| Primero | 3 | 2 | 3 | 2 | rojo
| Segundo | 2 | 1 | 2 | 1 | azul
| Segundo | 2 | 1 | 2 | 1 | rojo
| Tercero | 1 | 1 | 1 | 1 | rojo
| Cuarto | 3 | 2 | 3 | 2 | rojo
+---------+------+----------+------+----------+
SELECT * , COUNT( Tabla1.id) total, SUM(Tabla1.cantidad)
FROM Tabla1
INNER JOIN (SELECT Tabla2.id, COUNT( Tabla2.id) cantidad
FROM Tabla2
GROUP BY Tabla2.id)Tabla2 ON Tabla1.id= Tabla2.id
AND Tabla2.cantidad <= Tabla1.cantidad
GROUP BY Tabla1.Nombre, Tabla1.id
HAVING total = (SELECT COUNT ( Tabla2.id ) FROM Tabla2)
ORDER BY total DESC