SQL Server：GROUP BY有一列

I tried to set a left join, but when I did the group by with one column and selected multiple columns I got an SQL error.

我尝试设置左连接，但是当我使用一列并选择多列时，我得到了一个SQL错误。

This is my query:

这是我的查询：

SELECT
    b.ClientCode,
    b.LastName, b.FirstName,
    b."Id" AS IdClient,
    c.CaseDate,
    b.Gender,
    b.BirthDate
FROM
    dbo.Clients b
LEFT JOIN
    dbo.ClientCases c ON c.ClientCode = b.ClientCode
WHERE
    b.ClientCode LIKE '%1%'
    AND DATEDIFF(DAY, '01/06/2017', c.CaseDate) >= 0
    AND DATEDIFF(DAY, '05/06/2017', c.CaseDate) <= 0
GROUP BY
    b.ClientCode
ORDER BY
    b.ClientCode

When I write the query like this:

当我写这样的查询时：

SELECT
    b.ClientCode,
    b.LastName, b.FirstName,
    b."Id" AS IdClient,
    c.CaseDate,
    b.Gender,
    b.BirthDate
FROM
    dbo.Clients b
LEFT JOIN
    dbo.ClientCases c ON c.ClientCode = b.ClientCode
WHERE
    b.ClientCode LIKE '%1%'
    AND DATEDIFF(DAY, '01/06/2017', c.CaseDate) >= 0
    AND DATEDIFF(DAY, '05/06/2017', c.CaseDate) <= 0
GROUP BY
    b.ClientCode, b.LastName, b.FirstName, b."Id",
    b.Gender, b.BirthDate, c.CaseDate
ORDER BY
    b.ClientCode

It's working - but it returns duplicate ClientCode...

它工作 - 但它返回重复的ClientCode ...

What can I do?

我能做什么？

1 个解决方案

#1

You need to put MAX (or MIN or other aggregate function) for your other columns, because of the GROUP BY. Otherwise it doesn't know the value you want.

由于GROUP BY，您需要为其他列放置MAX（或MIN或其他聚合函数）。否则它不知道你想要的值。

SELECT b.ClientCode
    ,MAX(b.LastName)
    ,MAX(b.FirstName)
    ,MAX(b."Id") AS IdClient
    ,MAX(c.CaseDate)
    ,MAX(b.Gender)
    ,MAX(b.BirthDate)
FROM dbo.Clients b
LEFT JOIN dbo.ClientCases c ON c.ClientCode = b.ClientCode
WHERE b.ClientCode LIKE '%1%'
    AND DATEDIFF(day, '01/06/2017', c.CaseDate) >= 0
    AND DATEDIFF(day, '05/06/2017', c.CaseDate) <= 0
GROUP BY b.ClientCode
ORDER BY b.ClientCode

I suspect the error you are getting is:

我怀疑你得到的错误是：

Msg 8120, Level 16, State 1, Line 1
Column 'blah blah' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

#1