有没有按文本列分组的方法？

Question

我有这样的代码：

select CAST(OPERATOR_NAME AS NVARCHAR(MAX)) as 'Utilizador',
TERMINAL_DESCRIPTION as 'Terminal',
DOCUMENT_DATE as 'Data de Inicio',
PAYMENT_LIMIT_DATE as 'Data de Fim',
ORIGIN_WAREHOUSE_NAME as 'Loja',
DOCUMENT_TYPE_DESCRIPTION as 'Tipos de Documentos' 
from MRD_DOCUMENT_HEADER 
GROUP BY CAST(OPERATOR_NAME AS NVARCHAR(MAX))

但它给了我一个错误：

列MRD_DOCUMENT_HEADER.TERMINAL_DESCRIPTION在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。

我试过VARCHAR(100)和MAX，但这也不起作用。

我该怎么做呢？

已更新

SELECT DISTINCT
      Utilizador = OPERATOR_NAME 
    , Terminal = TERMINAL_DESCRIPTION 
    , [Data de Inicio] = DOCUMENT_DATE
    , [Data de Fim] = PAYMENT_LIMIT_DATE
    , Loja = ORIGIN_WAREHOUSE_NAME 
    , [Tipos de Documentos] = DOCUMENT_TYPE_DESCRIPTION
FROM dbo.MRD_DOCUMENT_HEADER
WHERE
       MRD_DOCUMENT_HEADER.DOCUMENT_TYPE_DESCRIPTION = (@DOCUMENT_TYPE_DESCRIPTION)
    OR MRD_DOCUMENT_HEADER.DOCUMENT_DATE = (@DOCUMENT_DATE)
    OR MRD_DOCUMENT_HEADER.PAYMENT_LIMIT_DATE =(@PAYMENT_LIMIT_DATE)
    OR MRD_DOCUMENT_HEADER.CORPORATION_ID = (@CORPORATION_ID)

Answer 1

试试这个-

SELECT  
      Utilizador = OPERATOR_NAME 
    , Terminal = TERMINAL_DESCRIPTION 
    , [Data de Inicio] = DOCUMENT_DATE
    , [Data de Fim] = PAYMENT_LIMIT_DATE
    , Loja = ORIGIN_WAREHOUSE_NAME 
    , [Tipos de Documentos] = DOCUMENT_TYPE_DESCRIPTION
FROM dbo.MRD_DOCUMENT_HEADER
GROUP BY 
      OPERATOR_NAME
    , TERMINAL_DESCRIPTION
    , DOCUMENT_DATE
    , PAYMENT_LIMIT_DATE
    , ORIGIN_WAREHOUSE_NAME
    , DOCUMENT_TYPE_DESCRIPTION
        DOCUMENT_TYPE_DESCRIPTION

或者尝试更优雅的样品-

SELECT DISTINCT
      Utilizador = OPERATOR_NAME 
    , Terminal = TERMINAL_DESCRIPTION 
    , [Data de Inicio] = DOCUMENT_DATE
    , [Data de Fim] = PAYMENT_LIMIT_DATE
    , Loja = ORIGIN_WAREHOUSE_NAME 
    , [Tipos de Documentos] = DOCUMENT_TYPE_DESCRIPTION
FROM dbo.MRD_DOCUMENT_HEADER

关于最后评论的更新：

SELECT
      Utilizador = OPERATOR_NAME 
    , Terminal = MAX(TERMINAL_DESCRIPTION)
    , [Data de Inicio] = MAX(DOCUMENT_DATE)
    , [Data de Fim] = MAX(PAYMENT_LIMIT_DATE)
    , Loja = MAX(ORIGIN_WAREHOUSE_NAME)
    , [Tipos de Documentos] = MAX(DOCUMENT_TYPE_DESCRIPTION)
FROM dbo.MRD_DOCUMENT_HEADER
GROUP BY OPERATOR_NAME

关于最后的评论，请更新2：

SELECT DISTINCT
      Utilizador = OPERATOR_NAME 
    , Terminal = TERMINAL_DESCRIPTION 
    , [Data de Inicio] = CONVERT(VARCHAR(10), DOCUMENT_DATE, 120)
    , [Data de Fim] = PAYMENT_LIMIT_DATE
    , Loja = ORIGIN_WAREHOUSE_NAME 
    , [Tipos de Documentos] = DOCUMENT_TYPE_DESCRIPTION
FROM dbo.MRD_DOCUMENT_HEADER t
WHERE t.DOCUMENT_TYPE_DESCRIPTION = @DOCUMENT_TYPE_DESCRIPTION
    OR t.DOCUMENT_DATE = @DOCUMENT_DATE
    OR t.PAYMENT_LIMIT_DATE = @PAYMENT_LIMIT_DATE
    OR t.CORPORATION_ID = @CORPORATION_ID

Answer 2

您必须将这些列放在GROUP BY子句中。它会解决你的问题。只需使用以下代码：

select CAST(OPERATOR_NAME AS NVARCHAR(MAX)) as 'Utilizador',
TERMINAL_DESCRIPTION as 'Terminal',
DOCUMENT_DATE as 'Data de Inicio',
PAYMENT_LIMIT_DATE as 'Data de Fim',
ORIGIN_WAREHOUSE_NAME as 'Loja',
DOCUMENT_TYPE_DESCRIPTION as 'Tipos de Documentos' 
from MRD_DOCUMENT_HEADER 
GROUP BY
  CAST(OPERATOR_NAME AS NVARCHAR(MAX)),
  TERMINAL_DESCRIPTION, DOCUMENT_DATE,
  PAYMENT_LIMIT_DATE,
  ORIGIN_WAREHOUSE_NAME,
  DOCUMENT_TYPE_DESCRIPTION

Answer 3

如果在SQL中使用GROUP BY子句，则在选择列表中只能使用与GROUP BY中使用的列相同的列，而任何其他列必须包装在聚合函数中，如count()、min()、max()、avg()等。

这是SQL99 92/SQL99 99的要求。但是，一些SQL引擎非常宽容，例如MySQL和SQLite允许打破这一规则。但MS、Oracle和PostgreSQL都相当严格。