c# - 来自同一张表的 3 个不同的不同值

Question

我将有一个名为 VISIT 表的 sql 表。

我有 3 列

WORKSTATION, CATEGORY, BUTTON

1 ,1 , 1

1 ,2 , 3

1 ,1 , 2

2 ,1 , 1

2 ,3 , 1

2 ,1 , 2

如何查询该表以获取一条查询语句中每一列的不同值？

结果应该是……

WORKSTATION, CATEGORY, BUTTON

1 ,1 , 1

2 ,2 , 2

• ,3 , 3

我可以用 3 个不同的查询来做到这一点。我只是想知道这是否可以在一个查询中完成，因为这会使程序运行得更快。

Answer 1

在我看来，尝试使用逐列来执行此操作是错误的；只需在两列中进行操作，然后在调用者处选择列:

SELECT DISTINCT 'WS' AS [Col], WORKSTATION AS [Value] FROM VISIT
UNION ALL
SELECT DISTINCT 'CA' AS [Col], CATEGORY AS [Value]  FROM VISIT
UNION ALL
SELECT DISTINCT 'BU' AS [Col], BUTTON AS [Value]  FROM VISIT

这或多或少与:

SELECT 'WS' AS [Col], WORKSTATION AS [Value] FROM VISIT
UNION
SELECT 'CA' AS [Col], CATEGORY AS [Value]  FROM VISIT
UNION
SELECT 'BU' AS [Col], BUTTON AS [Value]  FROM VISIT

然后这是一次往返等。我可能会为第一列使用一个整数(甚至可能是 tinyint)(希望客户知道 1 映射到 WORKSTATION 等)而不是 varchar，但上面给出的只是为了调味。

---

编辑评论；考虑以下因素:

• 在联合中包含不同的数据类型(转换为通用类型)
• 对原始数据使用序数排序，因此整数仍按 1,2,10(整数排序)排序，而不是 1,10,2(字符串排序)

TSQL:

DECLARE @VISIT TABLE (WORKSTATION int, CATEGORY varchar(10), BUTTON int)
INSERT @VISIT VALUES (1,'1',1)
INSERT @VISIT VALUES (1,'2',3)
INSERT @VISIT VALUES (2,'1',2)
INSERT @VISIT VALUES (10,'1',1)
INSERT @VISIT VALUES (10,'3',1)
INSERT @VISIT VALUES (10,'1',2)

SELECT x.Col, x.Value
FROM (
  SELECT CAST(1 as tinyint) AS [Col], ROW_NUMBER() over(order by WORKSTATION)
    AS [Sort], CONVERT(varchar(20), WORKSTATION) AS [Value] FROM @VISIT
  UNION
  SELECT CAST(2 as tinyint) AS [Col], ROW_NUMBER() over(order by CATEGORY)
    AS [Sort], CATEGORY AS [Value] FROM @VISIT
  UNION
  SELECT CAST(3 as tinyint) AS [Col], ROW_NUMBER() over (order by BUTTON)
    AS [Sort], CONVERT(varchar(20), BUTTON) AS [Value] FROM @VISIT
) x ORDER BY x.Col, x.Sort