主页 / dba / 问题 / 37560
Accepted
automatem
Asked: 2013-03-26 17:37:37 +0800 CST

快速高效地查询会员首次加入，会员表中的最新类别（最小值，最大值）

  • 772

我有下表代表会员信息：

    CREATE TABLE IF NOT EXISTS `membership` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `organisation_id` int(11) NOT NULL,
      `membership_subcategory_id` int(11) NOT NULL,
      `start` datetime DEFAULT NULL,
      `end` datetime DEFAULT NULL,
      `amount` decimal(9,2) DEFAULT NULL,
      `amount_paid` decimal(9,2) DEFAULT NULL,
      `notes` mediumtext,
      `order_id` int(11) DEFAULT NULL,
      `payment_type` varchar(20) NOT NULL,
      `active` tinyint(4) NOT NULL DEFAULT '1',
      `cancelled` tinyint(4) NOT NULL DEFAULT '0',
      `cancelled_date` datetime DEFAULT NULL,
      `cancellation_reason` mediumtext,
      `certificate_sent` date DEFAULT NULL,
      `welcome_email_sent` date DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id` (`id`),
      UNIQUE KEY `order_id_2` (`order_id`,`start`,`end`,`organisation_id`),
      KEY `membership_subcategory_id_idx` (`membership_subcategory_id`),
      KEY `organisation_id_idx` (`organisation_id`),
      KEY `order_id` (`order_id`)
    )
  • organization_id 是成员
  • 会员年从 7 月 1 日到 6 月 30 日，在每年的会员开始时开始记录 - 这可能是第一年的任何地方，但除非跳过一年，否则它总是 7 月 1 日
  • membership_subcategory_id 是会员每年申请的行业类别。

我需要一个有效的查询来获取加入日期和最新的会员类别。

我试过这个查询，但我收到“组函数的无效使用”错误

SELECT m.organisation_id, m2.membership_subcategory_id, MIN( m.start ) 
FROM membership m
INNER JOIN membership m2 ON m.organisation_id = m2.organisation_id
WHERE MAX( m.start ) = m2.start
GROUP BY m.organisation_id, m2.membership_subcategory_id
mysql performance

1 个回答

  1. Best Answer

    不确定我是否正确理解您的要求。此查询将为每个组织返回最短start日期和最新日期membership_subcategory_id（当订单为 by 时start）：

    SELECT    organisation_id, 
            MIN(`start`) AS 
          first_start_date,
            ( SELECT    m2.membership_subcategory_id
              FROM      membership AS m2
              WHERE     m2.organisation_id = m.organisation_id
              ORDER BY  m2.`start` DESC
                LIMIT 1
            ) AS 
          last_membership_subcategory_id
FROM      membership AS m
GROUP BY  organisation_id ;

    子查询将执行与表中不同组织的数量一样多的次数，因此效率取决于此。上的索引(organisation_id, start, membership_subcategory_id)将有助于最小化子查询执行时间。

    使用派生表：

    SELECT    gm.organisation_id, 
          gm.first_start_date,
          m.membership_subcategory_id AS last_membership_subcategory_id,
          m.*                           --- whatever other data you want
                                        --- from the latest `start` date
FROM      ( SELECT    organisation_id, 
                      MIN(`start`) AS first_start_date,
                      MAX(`start`) AS last_start_date
            FROM      membership
            GROUP BY  organisation_id 
          ) AS gm 
    JOIN
          membership AS m
              ON  m.organisation_id = gm.organisation_id
              AND m.`start` = gm.last_start_date
  ;
    • 1

相关问题