时间戳

首页 > 解决方案 > 使用存储在两个不同表中的数据和一个表中的性别和另一个表中的购买来查找前 5 名男性和前 5 名女性购买的 sql 表

mysql - 使用存储在两个不同表中的数据和一个表中的性别和另一个表中的购买来查找前 5 名男性和前 5 名女性购买的 sql 表

问题描述

CREATE TABLE mutual_fund (
    transaction_id INTEGER(40),
    customer_id INTEGER(40),
    transaction_type ENUM('Purchase','Sale'),
    nav_value INTEGER(40),
    no_of_units INTEGER(40),
    transaction_time TIMESTAMP,
    transaction_status ENUM('Success','Failed','Pending')
);

INSERT INTO mutual_fund (transaction_id,customer_id,transaction_type,nav_value,no_of_units,transaction_time,transaction_status) 
VALUES (1200,11,'Sale',3000,13,'2019-04-01 12:28:05','Success'),
        (1201,12,'Purchase',6000,6,'2019-04-17 14:54:10','Failed'),
        (1202,13,'Sale',2000,20,'2019-01-19 16:41:12','Failed'),
        (1203,14,'Purchase',3400,11,'2019-01-27 20:08:45','Success'),
        (1204,15,'Sale',7000,5,'2019-04-27 14:38:45','Success'),
        (1205,16,'Purchase',1000,10,'2019-04-01 09:28:55','Success'),
        (1206,17,'Sale',20000,12,'2019-01-29 19:01:32','Success'),
        (1207,18,'Purchase',8000,5,'2021-01-26 11:57:02','Success'),
        (1208,19,'Purchase',10000,3,'2021-01-26 12:34:53','Success'),
        (1209,20,'Purchase',9000,9,'2021-04-26 16:13:18','Success'),
        (1210,21,'Sale',19000,9,'2021-04-26 11:03:19','Failed');
CREATE TABLE customer_details (
    customer_id INTEGER(40),
    customer_name CHARACTER VARYING(40),
    customer_PAN CHAR(40),
    banned BOOLEAN,
    customer_join_time TIMESTAMP,
    gender ENUM('Male','Female')
);

INSERT INTO customer_details(customer_id,customer_name,customer_PAN,banned,customer_join_time,gender) 
VALUES (11,'salil','sa11',0,'2019-04-01','Male'),
        (12,'puran','pu12',1,'2019-04-17','Male'),
        (13,'saumya','sa12',1,'2019-01-19','Female'),
        (14,'priya','pr11',0,'2019-01-27','Female'),
        (15,'suresh','su15',0,'2019-04-27','Male'),
        (16,'amit','am11',0,'2019-04-01','Male'),
        (17,'rahul','ra45',0,'2019-01-29','Male'),
        (18,'rajesh','ra21',0,'2021-01-26','Male'),
        (19,'aswini','as65',0,'2021-01-26','Female'),
        (20,'prabha','pr95',0,'2021-04-26','Female'),
        (21,'shubham','sh01',1,'2021-04-26','Male');


SELECT c.customer_id,c.gender,m.nav_value,m.transaction_type,m.transaction_status
FROM customer_details c
INNER JOIN mutual_fund m on c.customer_id=m.customer_id
WHERE (SELECT c.gender='Male' FROM customer_details c INNER JOIN mutual_fund m on c.customer_id=m.customer_id ORDER BY m.nav_value DESC LIMIT 5) AS t1
UNION ALL
(SELECT c.gender="Female" FROM customer_details c INNER JOIN mutual_fund m on c.customer_id=m.customer_id ORDER BY m.nav_value DESC LIMIT 5) AS t2;

我正在尝试找出前 5 名男性和前 5 名女性购买（nav_value），但我被困在这里，因为性别在 customer_detail 表中，而购买（nav_value）在mutual_fund 表中，所以使用内部联接然后认为联合会起作用，但我是卡在这里，没有留下任何想法，所以任何想法人们都可以这样做？？？？？？

标签： mysqlsql

解决方案

你可以用这样的东西：

SELECT *
FROM
(
SELECT c.customer_id,c.gender,m.nav_value,m.transaction_type,m.transaction_status,
RANK() OVER (PARTITION BY c.gender ORDER BY m.nav_value DESC) AS RNum
FROM customer_details c
INNER JOIN mutual_fund m on c.customer_id=m.customer_id
) t
WHERE Rnum <= 5

您可以在此处阅读有关窗口函数的更多信息。不过，您可能希望使用其他窗口函数之一，例如 DENSE_RANK 或 ROW_NUMBER，具体取决于您的需要。

推荐阅读