我有以下设置:
- 包含
a
,b
,c
列的表。 - 包含随机数项(i1, i2, i3,…)的数组。)。
-
表包含像
这样的行1 1 i1 1 1 i2 . . . . . . . . . 1 1 in 1 2 i1 . . . . . . . . . 2 1 i1 2 2 i2
问题是并不是所有的记录都在那里。例如,行1 2 1可能缺失。
我想做的是,从查询(没有得到所有的行,并通过它们迭代)是看看是否有任何行缺失(我不关心巫婆一个,只有如果一个缺失)。
对于一个复杂得多的问题,这是一个非常简单的例子,所以如果我没有说清楚,或者我忘记提到任何事情,请随时询问细节。
PHP中的select和process是可以接受的,只要我不选择表中的所有内容(尽管我不知道如何通过不选择所有数据来处理数据,但我觉得值得一提)。
你们有些人想要一个图案,所以…:
让我们再简化一些…假设第一列有一个可能的数据数组可以在那里找到,第二列也是如此,第三列已经说过了。所有可能的组合应该在桌子上找到。
假设您知道列a和b的值,您可以尝试以下操作:
select c, count (*) group by c;
这将告诉您每个值有多少个条目。
i1 3
i2 0
in 3
,然后你可以遍历结果,看看缺少什么
假设我们有一个包含这些数据的表。
mysql> SELECT * FROM stuff;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | i1 |
| 1 | 1 | i2 |
| 1 | 2 | i2 |
| 1 | 2 | i3 |
| 2 | 1 | i1 |
+------+------+------+
5 rows in set (0.00 sec)
我们还假设C的所有可能值都在表中。然后我们可以像这样构造一个引用表。
mysql> SELECT a,b,c FROM (SELECT DISTINCT a,b FROM stuff) t1 CROSS JOIN (SELECT DISTINCT c FROM stuff) t2;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | i1 |
| 1 | 2 | i1 |
| 2 | 1 | i1 |
| 1 | 1 | i2 |
| 1 | 2 | i2 |
| 2 | 1 | i2 |
| 1 | 1 | i3 |
| 1 | 2 | i3 |
| 2 | 1 | i3 |
+------+------+------+
9 rows in set (0.00 sec)
我们可以将表与实际数据和参考表进行比较,将它们连接在一起,得到所有缺失的行,如下所示:
mysql> SELECT * FROM stuff RIGHT JOIN (SELECT a,b,c FROM (SELECT DISTINCT a,b FROM stuff) t1 CROSS JOIN (SELECT DISTINCT c FROM stuff) t2) r ON stuff.a = r.a AND stuff.b = r.b AND stuff.c = r.c WHERE stuff.a IS NULL;
+------+------+------+------+------+------+
| a | b | c | a | b | c |
+------+------+------+------+------+------+
| NULL | NULL | NULL | 1 | 2 | i1 |
| NULL | NULL | NULL | 2 | 1 | i2 |
| NULL | NULL | NULL | 1 | 1 | i3 |
| NULL | NULL | NULL | 2 | 1 | i3 |
+------+------+------+------+------+------+
4 rows in set (0.00 sec)
在a,b,c上的RIGHT JOIN将把参考表r中的行与实际行进行匹配。缺失的行将在材料方面显示为NULL。因此,我们可以通过在stuff表中选择任何带有NULL字段的行来获得所有缺失的行。
编辑:您可以将最后一个查询中的SELECT * ...
更改为SELECT count(*) ...
,并在本例中获得缺失行数4。
您可以通过一个简单的计数来做到这一点。期望的行数等于A中不同元素的个数乘以B中不同元素的个数乘以c中不同元素的个数
要计算缺失的数量,只需对适当的值进行算术:
select (cnt - cntA*cntB*cntC) as NumMissingRows
from (select count(distinct a) as cntA,
count(distinct b) as cntB,
count(distinct c) as cntC,
count(*) as cnt
from t
) t
对于这样的查询,这可能不是最好的性能,但对于一次性任务,这应该可以工作。
SELECT t1.id,
(
SELECT t2.id FROM table t2 WHERE t2.id < t1.id ORDER BY t2.id DESC LIMIT 1
) as prv
FROM table t1
HAVING id <> prv + 1
我会考虑这样做,即使在你的值列表中有重复,它仍然会工作。这与在结果字段上做任何循环是一样的(只返回一行,它将告诉您数组中有多少惟一值没有在表中找到。
SELECT COUNT(*)
FROM (SELECT 'i1' AS aCol
UNION
SELECT 'i2' AS aCol
UNION
SELECT 'i3' AS aCol
UNION
.......
UNION
SELECT 'in' AS aCol) Sub1
LEFT OUTER JOIN aTable
ON Sub1.aCol = aTable.c
WHERE aTable.c IS NULL
也可以很容易地修改,以便在将来需要时返回未找到的项目列表。