我在数据库中有两个表我正在运行查询:
表1包含产品ID和产品类型(例如,产品ID 1和2是'割草机',产品ID 3和4是'吹叶机')
表2包含用户购买,其中包含产品ID列(但不包含产品类型) . 产品ID对应于第一个表 . 每个用户在多次购买中具有相同的user_ID,但也是唯一的购买ID . 此表还包含用户数据,例如first_name,last_name和company .
我正在对产品类型(例如割草机)进行查询,以便让所有购买割草机的人,然后为购买多个割草机的人删除重复用户(我只想为每个人购买最新产品) . 我按user_id过滤,只使用购买ID的最大值(最新值) .
这是查询所在的位置:
SELECT *
FROM purchases t1
INNER JOIN
(
SELECT p1.user_id, MAX(p1.purchase_id) AS max_purchase_id
FROM purchases p1
INNER JOIN products p2
ON p1.product_id = p2.product_id
WHERE p2.product_type = 'lawnmowers'
GROUP BY p1.user_id
) t2
ON t1.user_id = t2.user_id AND t1.purchase_id = t2.max_purchase_id;
此查询过滤掉了重复的user_id,但是我还需要使用不同的user_id(仅保留最新的)过滤掉表2中first_name,last_name和company中具有相同数据的人员 . 如何修改现有查询以过滤掉此数据?
示例数据表1(运行查询之前):
| product_id | product |
| ------------- | ------------- |
| 1 | lawnmower |
| 2 | lawnmower |
| 3 | leafblower |
| 4 | leafblower |
示例数据表2(运行查询之前):
| purchase_id | product_id | user_id | first_name | last_name | company |
| ------------- | -----------| ------------- | ----------- | --------- | ----------- |
| 1 | 1 | 777 | Sally | Smith | My Homeware |
| 2 | 2 | 777 | Sally | Smith | My Homeware |
| 3 | 1 | 777 | Sally | Smith | My Homeware |
| 4 | 2 | 790 | Sally | Smith | My Homeware |
| 5 | 1 | 800 | Billy | Smith | My Homeware |
| 6 | 2 | 800 | Billy | Smith | My Homeware |
| 7 | 1 | 900 | Billy | Smith | My Homeware |
| 8 | 4 | 800 | Billy | Smith | My Homeware |
| 9 | 4 | 800 | Billy | Smith | My Homeware |
| 10 | 4 | 900 | Billy | Smith | My Homeware |
| 11 | 1 | 950 | Bobby | Smith | Cook with B |
当前输出:
| purchase_id | product_id | user_id | first_name | last_name | company |
| ------------- | -----------| ------------- | ----------- | --------- | ----------- |
| 3 | 1 | 777 | Sally | Smith | My Homeware |
| 4 | 2 | 790 | Sally | Smith | My Homeware |
| 9 | 4 | 800 | Billy | Smith | My Homeware |
| 10 | 4 | 900 | Billy | Smith | My Homeware |
| 11 | 1 | 950 | Bobby | Smith | Cook with B |
预期产量:
| purchase_id | product_id | user_id | first_name | last_name | company |
| ------------- | -----------| ------------- | ----------- | --------- | ----------- |
| 4 | 2 | 790 | Sally | Smith | My Homeware |
| 7 | 1 | 900 | Billy | Smith | My Homeware |
| 11 | 1 | 950 | Bobby | Smith | Cook with B |