sql - 有什么方法可以按文本列分组吗？

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))

但它给了我这个错误:

Column MRD_DOCUMENT_HEADER.TERMINAL_DESCRIPTION is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

我尝试了 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