mysql查询,根据相同的distributorId、customer_number分组,分组后取transaction_date最大的值,且transaction_date距今大于90天的数据
时间: 2024-03-17 10:43:30 浏览: 28
你可以使用以下的MySQL查询语句来满足你的需求:
```
SELECT distributorId, customer_number, MAX(transaction_date) AS latest_transaction_date
FROM your_table
GROUP BY distributorId, customer_number
HAVING latest_transaction_date < DATE_SUB(NOW(), INTERVAL 90 DAY)
```
这个查询语句首先根据distributorId和customer_number分组,然后使用MAX函数来获取每个分组中transaction_date的最大值。接着,使用HAVING子句过滤掉那些最近的交易时间距离当前时间不足90天的分组数据,最后的查询结果就是符合条件的数据。
相关问题
以下sql语句在mybatis的xml文件中怎么使用?SELECT distributorId, customer_number, MAX(transaction_date) AS latest_transaction_date FROM your_table GROUP BY distributorId, customer_number HAVING latest_transaction_date < DATE_SUB(NOW(), INTERVAL 90 DAY)
你可以在mybatis的xml文件中创建一个select标签,然后将该SQL语句放在其中,如下所示:
```xml
<select id="selectData" resultType="your_table">
SELECT distributorId, customer_number, MAX(transaction_date) AS latest_transaction_date
FROM your_table
GROUP BY distributorId, customer_number
HAVING latest_transaction_date < DATE_SUB(NOW(), INTERVAL 90 DAY)
</select>
```
请注意,由于XML语法的限制,你需要将小于号“<”转义为“<”,否则会导致XML解析错误。另外,你需要将resultType属性设置为你的查询结果所对应的Java类。
优化以下这个试图,把2023-06-09作为变量传入,SELECT A.office_id AS distributorId, A.customer_code AS customerCode, A.erp_name AS customerName, A.total_amount AS totalAmount, A.trade_date AS tradeDate, A.customer_type AS customerType, "初次" AS is_first_deal FROM t_first_feedback A WHERE A.`status` = '已成交' AND A.trade_date IS NOT NULL AND A.trade_date >= DATE_FORMAT( DATE_SUB( '2023-06-09', INTERVAL 1 MONTH ), '%Y-%m-01' ) AND A.trade_date <= LAST_DAY( DATE_SUB( '2023-06-09', INTERVAL 1 MONTH )) UNION ALL SELECT B.office_id AS distributorId, B.customer_number AS customerCode, B.customer_name AS customerName, B.total_amount AS totalAmount, B.transaction_date AS tradeDate, "" AS customerType, "重复" AS is_first_deal FROM t_repeat_feedback B WHERE B.transaction_date IS NOT NULL AND B.transaction_date >= DATE_FORMAT( DATE_SUB( '2023-06-09', INTERVAL 1 MONTH ), '%Y-%m-01' ) AND B.transaction_date <= LAST_DAY( DATE_SUB( '2023-06-09', INTERVAL 1 MONTH ))
可以将'2023-06-09'作为参数传入SQL语句,避免硬编码,提高代码的可维护性和复用性。具体实现方式如下:
```
DECLARE @date DATE = '2023-06-09';
SELECT A.office_id AS distributorId,
A.customer_code AS customerCode,
A.erp_name AS customerName,
A.total_amount AS totalAmount,
A.trade_date AS tradeDate,
A.customer_type AS customerType,
"初次" AS is_first_deal
FROM t_first_feedback A
WHERE A.`status` = '已成交'
AND A.trade_date IS NOT NULL
AND A.trade_date >= DATE_FORMAT(DATE_SUB(@date, INTERVAL 1 MONTH), '%Y-%m-01')
AND A.trade_date <= LAST_DAY(DATE_SUB(@date, INTERVAL 1 MONTH))
UNION ALL
SELECT B.office_id AS distributorId,
B.customer_number AS customerCode,
B.customer_name AS customerName,
B.total_amount AS totalAmount,
B.transaction_date AS tradeDate,
"" AS customerType,
"重复" AS is_first_deal
FROM t_repeat_feedback B
WHERE B.transaction_date IS NOT NULL
AND B.transaction_date >= DATE_FORMAT(DATE_SUB(@date, INTERVAL 1 MONTH), '%Y-%m-01')
AND B.transaction_date <= LAST_DAY(DATE_SUB(@date, INTERVAL 1 MONTH));
```