问获取每2个参数的前3个参数的列表
EN

Stack Overflow用户

提问于 2018-07-04 02:40:31

回答 1查看 44关注 0票数 -1

如何才能获得每个公司每个项目类型的前3名用户的表？

以下是我的方案：

contact                                           users
--------------------------                        ----------------
id                                                id        
Contact_Title                                     Name_Title
Contact_First                                     Name_First
Contact_Middle                                    Name_Middle
Contact_Last                                      Name_Last
Lead_Referral_Source                              Email
Date_of_Initial_Contact                           Password
Title                                             User_Roles
Company                                           User_Status
Industry
Address
Address_Street_1
Address_Street_2
Address_City
Address_State
Address_Zip
Address_Country
Phone
Email
Status
Website
LinkedIn_Profile
Background_Info
Sales_Rep
Rating
Project_Type
Project_Description
Proposal_Due_Date
Budget
Deliverables

当涉及到SQL时，我只是一个初学者，并且在逻辑上有一段艰难的时间。我还没能在这个问题上取得任何进展，我很想看看你们可能会如何尝试，并听取一些建议。非常感谢您的帮助:)

mysql

sql

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-07-04 05:57:53

我找不到使用单个查询的方法，但是这里有一个存储过程，它将创建一个表来存储结果，然后用所需的结果填充该表：

DROP PROCEDURE IF EXISTS Get_Top_3_Rated_Salespeople_Per_Company_and_Project_Type;
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS Get_Top_3_Rated_Salespeople_Per_Company_and_Project_Type()

    BEGIN

        DECLARE ct int;
        DECLARE comboCT int;
        DECLARE lt int;

        /* Create table where results will be stored */
        /* WARNING: Drops existing table first! */
        DROP TABLE IF EXISTS `Top_3_Rated_Salespeople_Per_Company_and_Project_Type`;
        CREATE TABLE IF NOT EXISTS `Top_3_Rated_Salespeople_Per_Company_and_Project_Type` (
            Name_First varchar(30),
            Name_Middle varchar(30),
            Name_Last varchar(100),
            Email varchar(100), 
            Rating numeric(6,4), 
            Company varchar(100), 
            Project_Type varchar(100)
        ); 


        /* Create temp table to store distinct Company/Project_Type combinations */
        CREATE TEMPORARY TABLE IF NOT EXISTS temp_distinct_combos
            SELECT Project_Type, Company FROM contact GROUP BY Project_Type, Company;

        SET ct = 0;

        SET comboCT = (SELECT COUNT(*) FROM temp_distinct_combos);

        /* For each combination in temp table, get top three ratings with associated user data
        /* and inset into Top_3_Rated_Salespeople_Per_Company_and_Project_Type */
        WHILE ct <= (comboCT + 1) DO

            SET lt = 1;

            INSERT INTO 
                `Top_3_Rated_Salespeople_Per_Company_and_Project_Type`

                SELECT
                    u.Name_First,
                    u.Name_Middle,
                    u.Name_Last,
                    u.Email,
                    c.Rating,
                    c.Company,
                    c.Project_Type
                FROM
                    contact as c 
                LEFT OUTER JOIN
                    users AS u
                    ON 
                    u.id = c.Sales_Rep
                WHERE
                    /* Company/Project_Type combination matches current record in temp_distinct_combos */
                    CONCAT(c.Company, c.Project_Type) = (
                                                        SELECT
                                                            CONCAT(Company, Project_Type)
                                                        FROM
                                                            temp_distinct_combos
                                                        LIMIT
                                                            ct, lt /* this will limit this subquery to returning only one row at a time */
                                                        )

                ORDER BY
                    c.Company,
                    c.Project_Type,
                    c.Rating DESC
                LIMIT 3;

                SET ct = ct + 1;

        END WHILE;


    END //
DELIMITER ;

我用来设置测试的代码(注意:当你发布你的问题时，你应该包括这些东西；周围的大多数人都不会这么做来帮助你)：

DROP TABLE IF EXISTS contact;
CREATE TABLE IF NOT EXISTS contact (
    id int,
    Contact_Title  varchar(40),
    Contact_First  varchar(40),
    Contact_Middle  varchar(40),
    Contact_Last  varchar(40),
    Lead_Referral_Source  varchar(40),
    Date_of_Initial_Contact  varchar(40),
    Title  varchar(40),
    Company  varchar(40),
    Industry varchar(40),
    Address varchar(40),
    Address_Street_1 varchar(40),
    Address_Street_2 varchar(40),
    Address_City varchar(40),
    Address_State varchar(40),
    Address_Zip varchar(40),
    Address_Country varchar(40),
    Phone varchar(40),
    Email varchar(40),
    Status varchar(40),
    Website varchar(40),
    LinkedIn_Profile varchar(40),
    Background_Info varchar(40),
    Sales_Rep int,
    Rating numeric(6,4),
    Project_Type varchar(40),
    Project_Description varchar(40),
    Proposal_Due_Date varchar(40),
    Budget varchar(40),
    Deliverables varchar(40)
);

DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (
id int,
Name_Title varchar(40),
Name_First varchar(40),
Name_Middle varchar(40),
Name_Last varchar(40),
Email varchar(40),
Password varchar(40),
User_Roles varchar(40),
User_Status varchar(40)
);


INSERT INTO
    users
        (id, Name_First, Name_Last, Email)
    VALUES
        (1, 'Name_First_1', 'Name_Last_1', 'Email_1'),
        (2, 'Name_First_2', 'Name_Last_2', 'Email_2'),
        (3, 'Name_First_3', 'Name_Last_3', 'Email_3'),
        (4, 'Name_First_4', 'Name_Last_4', 'Email_4'),
        (5, 'Name_First_5', 'Name_Last_5', 'Email_5'),
        (6, 'Name_First_6', 'Name_Last_6', 'Email_6'),
        (7, 'Name_First_7', 'Name_Last_7', 'Email_7'),
        (8, 'Name_First_8', 'Name_Last_8', 'Email_8'),
        (9, 'Name_First_9', 'Name_Last_9', 'Email_9'),
        (10, 'Name_First_10', 'Name_Last_10', 'Email_10'),
        (11, 'Name_First_11', 'Name_Last_11', 'Email_11');



INSERT INTO 
    contact
        (id, Contact_First, Contact_Last, Sales_Rep, Rating, Project_Type, Company)
    VALUES
        (1, 'Contact_First_1', 'Contact_Last_1', 1, 10, 'Type_1', 'Company_1'),
        (2, 'Contact_First_2', 'Contact_Last_2', 2, 9.9, 'Type_1', 'Company_1'),
        (3, 'Contact_First_3', 'Contact_Last_3', 3, 9.8, 'Type_1', 'Company_1'),
        (4, 'Contact_First_4', 'Contact_Last_4', 4, 9.7, 'Type_1', 'Company_1'),
        (5, 'Contact_First_5', 'Contact_Last_5', 5, 9.6, 'Type_1', 'Company_1'),
        (6, 'Contact_First_6', 'Contact_Last_6', 6, 9.5, 'Type_2', 'Company_1'),
        (7, 'Contact_First_7', 'Contact_Last_7', 7, 9.4, 'Type_2', 'Company_1'),
        (8, 'Contact_First_8', 'Contact_Last_8', 8, 9.3, 'Type_2', 'Company_1'),
        (9, 'Contact_First_9', 'Contact_Last_9', 9, 9.2, 'Type_2', 'Company_1'),
        (10, 'Contact_First_10', 'Contact_Last_10', 10, 9.1, 'Type_2', 'Company_1'),
        (11, 'Contact_First_11', 'Contact_Last_11', 11, 9, 'Type_2', 'Company_1'),
        (12, 'Contact_First_12', 'Contact_Last_12', 1, 10, 'Type_1', 'Company_2'),
        (13, 'Contact_First_13', 'Contact_Last_13', 2, 9.9, 'Type_1', 'Company_2'),
        (14, 'Contact_First_14', 'Contact_Last_14', 3, 9.8, 'Type_1', 'Company_2'),
        (15, 'Contact_First_15', 'Contact_Last_15', 4, 9.7, 'Type_1', 'Company_2'),
        (16, 'Contact_First_16', 'Contact_Last_16', 5, 9.6, 'Type_2', 'Company_2'),
        (17, 'Contact_First_17', 'Contact_Last_17', 6, 9.5, 'Type_2', 'Company_2'),
        (18, 'Contact_First_18', 'Contact_Last_18', 7, 9.4, 'Type_2', 'Company_2'),
        (19, 'Contact_First_19', 'Contact_Last_19', 8, 9.3, 'Type_2', 'Company_2'),
        (20, 'Contact_First_20', 'Contact_Last_20', 9, 9.2, 'Type_2', 'Company_2'),
        (21, 'Contact_First_21', 'Contact_Last_21', 10, 9.1, 'Type_2', 'Company_2'),
        (22, 'Contact_First_22', 'Contact_Last_22', 11, 9, 'Type_2', 'Company_2'),
        (23, 'Contact_First_23', 'Contact_Last_23', 1, 10, 'Type_1', 'Company_3'),
        (24, 'Contact_First_24', 'Contact_Last_24', 2, 9.9, 'Type_1', 'Company_3'),
        (25, 'Contact_First_25', 'Contact_Last_25', 3, 9.8, 'Type_2', 'Company_3'),
        (26, 'Contact_First_26', 'Contact_Last_26', 4, 9.7, 'Type_2', 'Company_3'),
        (27, 'Contact_First_27', 'Contact_Last_27', 5, 9.6, 'Type_2', 'Company_3'),
        (28, 'Contact_First_28', 'Contact_Last_28', 6, 9.5, 'Type_2', 'Company_3'),
        (29, 'Contact_First_29', 'Contact_Last_29', 7, 9.4, 'Type_2', 'Company_3'),
        (30, 'Contact_First_30', 'Contact_Last_30', 8, 9.3, 'Type_2', 'Company_3');

如果这解决了你的问题，请记得标记为已回答，这样每个人都会知道你不再需要帮助(所以我会因为我花了两个小时帮助你而得到奖励:-)。如果这不能满足您的需求，请尽可能详细地描述它与您期望的结果有何不同，我将尝试修改。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/51161094

复制

相似问题

问获取每2个参数的前3个参数的列表
EN

回答 1

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取每2个参数的前3个参数的列表EN

回答 1

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取每2个参数的前3个参数的列表
EN