ASP +ACCESS 数据库 如何查找某字段值 在当前表4个字段中唯一？

Question

我有一ACCESS题库表，里面有 序号 |题干 |A选项 |B选项 |C选项 |D选项 |答案 这些字段，现在我想查出所有选项值在全表4个选项中唯一的题目，比如记录一，它的A选项是a,如果这个a在整个表的其余记录的4个选项中都未出现，那么它就是属于我要找的记录，请问这个SQL查询语句该怎么写？
我原来写了一条，发现效率超级低：
sql="select * from tiku where daan='A' and sa not in (select sa from tiku group by sa having count(sa)>1) and sa not in (select sb from tiku group by sb having count(sb)>1) and sa not in (select sc from tiku group by sc having count(sc)>1) and sa not in (select sd from tiku group by sd having count(sd)>1)"

Answer 1

可以使用UNION ALL和GROUP BY语句来实现该查询，具体语句如下：
SELECT *
FROM (
SELECT 序号, 题干, A选项 AS 选项, 'A' AS 答案
FROM tiku
UNION ALL
SELECT 序号, 题干, B选项 AS 选项, 'B' AS 答案
FROM tiku
UNION ALL
SELECT 序号, 题干, C选项 AS 选项, 'C' AS 答案
FROM tiku
UNION ALL
SELECT 序号, 题干, D选项 AS 选项, 'D' AS 答案
FROM tiku
) AS t
WHERE 选项 NOT IN (
SELECT A选项
FROM tiku
UNION
SELECT B选项
FROM tiku
UNION
SELECT C选项
FROM tiku
UNION
SELECT D选项
FROM tiku
GROUP BY A选项, B选项, C选项, D选项
HAVING COUNT(*) > 1
)
上述SQL查询语句将题库表中的所有选项转化为一列数据，并将其按照序号、题干、选项和答案的顺序输出。同时，使用子查询的方式统计出所有4个选项中重复出现的选项值，并将这些选项从输出结果中剔除。