使用 DISTINCT 和 UNION 在 SQL 查询中获取字段名称

Question

我有以下查询：

SELECT DISTINCT(uniq)
FROM
(
    SELECT sex AS uniq FROM type4
    UNION
    SELECT fason AS uniq FROM type4
    UNION
    SELECT color AS uniq FROM type4
    UNION
    SELECT size AS uniq FROM type4
) AS Temp

它几乎运行良好，它返回：

[uniq] => some unique value

是否可以知道这个唯一值来自哪一列？ 我的意思是这样的：

[uniq] => some unique value
[from] => size

我怎样才能做到这一点？

Answer 1

您的原始查询不需要子查询，也不需要 DISTINCT。您可以使用：

SELECT sex AS uniq FROM type4
UNION
SELECT fason FROM type4
UNION
SELECT color FROM type4
UNION
SELECT size FROM type4

---

现在对于您的问题，如果四列没有重叠值，您可以使用：

  SELECT DISTINCT sex AS uniq
                , 'sex' AS FromColumn 
  FROM type4
UNION ALL
  SELECT DISTINCT fason 
                , 'fason' 
  FROM type4
UNION ALL
  SELECT DISTINCT color 
                , 'color' 
  FROM type4
UNION ALL
  SELECT DISTINCT size 
                , 'size' 
  FROM type4

Answer 2

SELECT DISTINCT(uniq)
FROM
(
    SELECT (sex + ' 1') AS uniq FROM type4
    UNION
    SELECT (fason + ' 2') AS uniq FROM type4
    UNION
    SELECT (color + ' 3') AS uniq FROM type4
    UNION
    SELECT (size + ' 4') AS uniq FROM type4
) AS Temp

然后你可以使用 substring 删除右边的最后 2 个