选择多列一组?
有人可以帮我看看这个查询吗?选择多列一组?
SELECT CLIENT.ID_CLIENT ,
EVALUATION_CLIENT.ID_EVAL ,
MAX(EVALUATION_CLIENT.Date) AS DATE
FROM EVALUATION_CLIENT
INNER JOIN CLIENT
ON CLIENT.ID_CLIENT = EVALUATION_CLIENT.FK_IDClient
WHERE EVALUATION_CLIENT.Date
BETWEEN @START_DATE_LOCAL
AND @END_DATE_LOCAL
GROUP BY CLIENT.IDCLIENT,
EVALUATION_CLIENT.ID_EVAL
它返回......
ID_CLIENT | ID_EVAL | DATE_EVAL
1423 |11160 | 2008-02-12 00:00:00.000
1423 |11161 | 2008-02-18 00:00:00.000
18 |11162 | 2008-02-15 00:00:00.000
666 |11163 | 2008-02-19 00:00:00.000
但我想是这样(不重复的客户端)
ID_CLIENT | ID_EVAL | DATE_EVAL
1423 |11161 | 2008-02-18 00:00:00.000
18 |11162 | 2008-02-15 00:00:00.000
666 |11163 | 2008-02-19 00:00:00.000
我使用MSSQL 2008
感谢了很多!
更新:我需要Id_Eval为最近的日期由Id_client
如果你只想要最新的条目为每个客户端,你可以使用:
;WITH ClientEvals AS
(
SELECT
c.ID_CLIENT ,
e.ID_EVAL ,
e.Date AS ClientDATE,
ROW_NUMBER() OVER(PARTITION BY c.ID_CLIENT ORDER BY e.Date DESC) AS 'RowNo'
FROM
dbo.EVALUATION_CLIENT e
INNER JOIN
dbo.CLIENT c ON c.ID_CLIENT = e.FK_IDClient
WHERE
e.Date BETWEEN @START_DATE_LOCAL AND @END_DATE_LOCAL
)
SELECT
ID_CLIENT ,
ID_EVAL ,
ClientDATE
FROM
ClientEvals
WHERE
RowNo = 1
的CTE(公共表表达式)内的内选择选择evals为每一个客户,和分区ID_Client
的数据 - 每个客户端从1开始获取行数,其中1是最近的条目。
外部SELECT
基于CTE,并且仅选择那些具有RowNo = 1
的行 - >每个客户端的最新行。
使用不错的ROW_NUMBER – 2011-04-20 18:31:33
这正是我想要做的!谢谢 – 2011-04-20 19:02:22
你可以做以下的,如果你只是想最高ID_EVAL
的ID_CLIENT
:
SELECT CLIENT.ID_CLIENT ,
MAX(EVALUATION_CLIENT.ID_EVAL),
MAX(EVALUATION_CLIENT.Date) AS DATE
FROM EVALUATION_CLIENT
INNER JOIN CLIENT
ON CLIENT.ID_CLIENT = EVALUATION_CLIENT.FK_IDClient
WHERE EVALUATION_CLIENT.Date
BETWEEN @START_DATE_LOCAL
AND @END_DATE_LOCAL
GROUP BY CLIENT.IDCLIENT
请注意,这可能会从一行返回'ID_EVAL',而从另一行返回'Date'。这可能没什么问题,因为海报并不清楚这些要求 - 但它们似乎更有可能同时拥有这两个要素。如果保证更高的'ID_EVAL'也会有一个更晚的'Date',那么在任何情况下都可以。 – 2011-04-20 18:34:06
@Dave - 真够的。需要来自OP的更多信息来确定。 – Oded 2011-04-20 18:46:39
这种方法将允许案例MAX(ID_EVAL)
与MAX(EVALUATION_CLIENT.Date)
不在同一行
如果您不必担心这种情况se Oded的方法。
SELECT CLIENT.ID_CLIENT ,
EVALUATION_CLIENT.ID_EVAL ,
EVALUATION_CLIENT.Date
FROM evaluation_client
INNER JOIN client
ON client.id_client = evaluation_client.fk_idclient
INNER JOIN (SELECT client.id_client,
MAX(evaluation_client.DATE) AS DATE
FROM evaluation_client
INNER JOIN client
ON client.id_client = evaluation_client.fk_idclient
WHERE evaluation_client.DATE BETWEEN
@START_DATE_LOCAL AND @END_DATE_LOCAL
GROUP BY client.id_client,
evaluation_client.id_eval)maxdate
ON evaluation_client.DATE = maxdate.DATE
AND client.id_client = maxdate.id_client
你只是想为每个客户提供最高的ID_EVAL吗?或者你想要最新的日期?这些定义是否一样? – 2011-04-20 18:32:38