mysql - 过滤表联合mysql中的行

Question

您好，我在这个糟糕的数据库结构中遇到了困难，我需要帮助来过滤表联合中的行，这可能吗？

发生的事情是我试图选择 pre1, pre2, pre3, pre4, pre5在值不为空的表中，因此所有行都会出现，但是当我尝试过滤掉 trno = <value> 的行时我收到错误，因为我不知道该怎么做。你能帮我吗？

我正在处理的代码是这个

SELECT * FROM
(
SELECT pre1 AS col FROM ccsubject
UNION
SELECT pre2 AS col FROM ccsubject
UNION
SELECT pre3 AS col FROM ccsubject
UNION
SELECT pre4 As col FROM ccsubject
UNION
SELECT pre5 AS col FROM ccsubject
) T1
WHERE col IS NOT NULL

我也尝试过这个

SELECT * FROM
(
SELECT pre1 AS col FROM ccsubject
UNION
SELECT pre2 AS col FROM ccsubject
UNION
SELECT pre3 AS col FROM ccsubject
UNION
SELECT pre4 As col FROM ccsubject
UNION
SELECT pre5 AS col FROM ccsubject
UNION 
SELECT trno AS tr FROM ccsubject
) T1
WHERE col IS NOT NULL AND tr = 29005

这是我的表结构

Answer 1

试试这个:

SELECT * FROM
(
SELECT pre1 AS col FROM ccsubject WHERE trno = 29005 AND pre1 IS NOT NULL
UNION
SELECT pre2 AS col FROM ccsubject WHERE trno = 29005 AND pre2 IS NOT NULL
UNION
SELECT pre3 AS col FROM ccsubject WHERE trno = 29005 AND pre3 IS NOT NULL
UNION
SELECT pre4 As col FROM ccsubject WHERE trno = 29005 AND pre4 IS NOT NULL
UNION
SELECT pre5 AS col FROM ccsubject WHERE trno = 29005 AND pre5 IS NOT NULL
) T1