具有“分组依据"的SQL请求和“最大"和“加入"? [英] SQL request with "Group by" and "max" and "join"?
本文介绍了具有“分组依据"的SQL请求和“最大"和“加入"?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下是我的数据示例:
CLIENT_ATTRIBUT:
CLIENT_ATTRIBUT :
ID_CLIENT | DATE_CLIENT | ATTRIBUT
----------+-------------+---------
000000001 | 2010:03:01 | 0000010
----------+-------------+---------
000000001 | 2010:02:16 | 0000010
----------+-------------+---------
000000001 | 2010:03:04 | 0000011
----------+-------------+---------
000000002 | 2010:03:01 | 0001000
----------+-------------+---------
客户:
ID_CLIENT | NOM_MARITAL |
----------+-------------+
000000001 | PANTROMANI |
----------+-------------+
000000002 | ELLOUQUIER |
----------+-------------+
我想获取表"CLIENT_ATTRIBUT"中的每个ID_CLIENT:
ID_CLIENT,max(DATE_CLIENT)及其对应的"ATTRIBUT"和"NOM_MARITAL"
I'd like to get, for each ID_CLIENT in the table "CLIENT_ATTRIBUT" :
ID_CLIENT, max(DATE_CLIENT) with its corresponding "ATTRIBUT", and "NOM_MARITAL"
所以在上面的示例中:
ID_CLIENT | DATE_CLIENT | ATTRIBUT | NOM_MARITAL |
----------+-------------+----------+-------------+
000000001 | 2010:03:04 | 0000011 | PANTROMANI |
----------+-------------+----------+-------------+
000000002 | 2010:03:01 | 0001000 | ELLOUQUIER |
(我正在使用Mysql,但我想它与任何数据库系统都应该没什么不同)
(i'm working with Mysql but i guess it should not be so different with any Database System)
推荐答案
您应该能够使用子查询,如下所示:
You should be able to use a sub-query as follows:
SELECT
client.id_client,
sub_query.date_client,
client_attribut.attribut,
client.nom_marital
FROM
client
INNER JOIN
(SELECT
client_attribut.id_client,
MAX(client_attribut.date_client) as date_client
FROM
client_attribut
GROUP BY
client_attribut.id_client)
AS sub_query ON (sub_query.id_client = client.id_client)
INNER JOIN
client_attribut ON (client_attribut.id_client = sub_query.id_client AND
client_attribut.date_client = sub_query.date_client);
这篇关于具有“分组依据"的SQL请求和“最大"和“加入"?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文