mysql - 需要帮助来计算最不同的书籍

Question

哪些客户订购了最多不同的书籍？显示客户 ID 并包含联系。这不是指订购书籍的数量。例如，假设我的唯一订单是客户 1 订购了 400 份 book_id 34 客户 2 订购了 2 份 book_id 62 和 3 份 book_id 29 客户 1 订购了更多数量的书籍，但客户 2 订购了更多不同的书籍。在本例中，查询应返回客户 2:

这是我尝试的解决方案。不知道如何解决这个问题:

SELECT cust_id
  FROM a_bkorders.customers
 WHERE cust_id IN 
       (
       SELECT cust_id
         FROM a_bkorders.order_headers
         Join a_bkorders.order_details using (order_id)  
        GROUP BY cust_id
        HAVING count(book_id) <=  
         (
          SELECT MAX(numBooks) 
                  FROM (  
                    SELECT cust_id
                        ,  count(book_id) AS numBooks
                      Join a_bkorders.order_details using (order_id)  
                      FROM a_bkorders.order_headers 
                     GROUP BY cust_id
                      ) t
        )
    )ORDER BY cust_id;

以下是表格:

   -- create customers
    create  table  a_bkorders.customers (
        cust_id           integer          not null 
      , cust_name_last    varchar(20)      not null
      , cust_name_first   varchar(20)      null
      , cust_state        char(2)          not null
      , cust_postal_code  char(10)         not null
      , cust_acct_opened  date             not null 
      , constraint bk_cust_pk              primary key (cust_id)
      , constraint bk_cust_id_range        check (cust_id > 1000)
      , constraint bk_cust_acct_opened_ck  check (cust_acct_opened >=  '1975-01-01' )
    )engine = INNODB;


-- create order_headers 
create  table   a_bkorders.order_headers (
    order_id          integer          not null 
  , order_date        date             not null
  , cust_id           integer          not null 
  , constraint bk_orders_pk            primary key (order_id)
  , constraint bk_orders_cust_fk       foreign key(cust_id) 
               references a_bkorders.customers(cust_id) 
  , constraint bk_order_id_range       check (order_id > 100)
 ,  constraint bk_order_date_ck        check (order_date >=  '2000-01-01')
)engine = INNODB;


-- create order_details  
create  table   a_bkorders.order_details (
    order_id          integer          not null 
  , order_line        integer          not null 
  , book_id           integer          not null 
  , quantity          integer          not null 
  , order_price       numeric(6,2)     not null   
  , constraint bk_orderline_pk         primary key (order_id, order_line)
  , constraint bk_orderline_order_fk   foreign key (order_id) 
               references a_bkorders.order_headers(order_id) on delete cascade
  , constraint bk_orderline_book_fk    foreign key (book_id )  
               references a_bkinfo.books(book_id)
  , constraint bk_quantity_ck          check (quantity > 0) 
  , constraint bk_ordprice_ck          check (order_price >= 0) 
)engine = INNODB;

以下是插入内容:

-- customers  
    insert into a_bkorders.customers  values  (208950, 'Adams', 'Samuel',        'MA', '02106', '1996-04-15' );
    insert into a_bkorders.customers  values  (200368, 'Blake', 'William',       'CA', '95959', '1997-07-15' );
    insert into a_bkorders.customers  values  (258595, 'Jobs', 'Peter',          'MA', '02575', '1997-01-09' );
    insert into a_bkorders.customers  values  (263119, 'Jones', null,            'IL', '62979', '1997-03-02' );
    insert into a_bkorders.customers  values  (224038, 'Austin', 'Pat',          'CA', '95900', '1997-08-02' );
    insert into a_bkorders.customers  values  (255919, 'Milton', 'John',         'NJ', '08235', '2011-05-31' );
    insert into a_bkorders.customers  values  (211483, 'Carroll', 'Lewis',       'CA', '94203', '1998-08-08' );
    insert into a_bkorders.customers  values  (221297, 'Dodgson', 'Charles',     'MI', '49327', '2001-05-06' );
    insert into a_bkorders.customers  values  (261502, 'Hawthorne', 'Nathaniel', 'MA', '02297', '2001-10-12' );
    insert into a_bkorders.customers  values  (212921, 'Books on Tap', NULL,     'CA', '94112', '2002-01-06' );
    insert into a_bkorders.customers  values  (260368, 'Muller', 'Jonathan',     'IL', '62885', '2005-12-15' );
    insert into a_bkorders.customers  values  (259969, 'Carlsen', 'Benny',       'NJ', '08505', '2011-07-12' );
    insert into a_bkorders.customers  values  (239427, 'Marksa', 'Anna',         'NJ', '08495', '2011-02-28' );
    insert into a_bkorders.customers  values  (296598, 'Collins', 'Douglas',     'MO', '65836', '2005-04-25' );
    insert into a_bkorders.customers  values  (276381, 'Collins', 'Douglas',     'OH', '22451', '2005-02-08' );
    insert into a_bkorders.customers  values  (234138, 'Keats', 'John',          'IL', '61500', '2006-04-30' );
    insert into a_bkorders.customers  values  (267780, 'Shelly', 'Mary',         'CA', '94100', '2010-10-02' );
    insert into a_bkorders.customers  values  (290298, 'Swift', 'Jonathan',      'MI', '49201', '2010-10-12' );
    insert into a_bkorders.customers  values  (226656, 'Randall', 'Randell',     'NJ', '08251', '2011-08-08' );
    insert into a_bkorders.customers  values  (222477, 'Rossetti', 'Christina',  'MI', '49742', '2011-07-11' );
    insert into a_bkorders.customers  values  (227105, 'Kafka', 'Franz',         'MA', '02297', '2010-12-31' );
    insert into a_bkorders.customers  values  (202958, 'Denver', null,           'IL', '60405', '2011-01-15' );
    insert into a_bkorders.customers  values  (218709, 'Bonnard', 'Paul',        'MA', '02558', '2005-11-15' );
    insert into a_bkorders.customers  values  (217796, 'Anders', null,           'IL', '62505', '2011-03-30' );
    insert into a_bkorders.customers  values  (272787, 'Carlson', 'Ben',         'IL', '62505', '2011-05-05' );
    insert into a_bkorders.customers  values  (234709, 'Brahms', 'Johnnie',      'MA', '02558', '2012-01-15' );
    insert into a_bkorders.customers  values  (217002, 'Grieg', 'Edvard',        'IL', '62329', '2012-02-29' );
    insert into a_bkorders.customers  values  (272611, 'Jarrett', 'Keith',       'IL', '62329', '2011-11-11' );
    insert into a_bkorders.customers  values  (299099, 'Sam', 'Dave',            'CA', '94141', '2011-01-01' );
    insert into a_bkorders.customers  values  (259906, 'Capybara', 'Wile E.',    'CA', '94132', '2012-01-05' );
    insert into a_bkorders.customers  values  (259907, 'Hedge', 'Mr.',           'CA', '94132', '2011-09-05' );
    insert into a_bkorders.customers  values  (282716, 'Biederbecke','Dwight',   'PA', '18106', '2013-01-01' );
    insert into a_bkorders.customers  values  (287261, 'Biederbecke','Bix',      'PA', '18106', '2012-01-01' );
    insert into a_bkorders.customers  values  (226275, 'Dalrymple','Jack',       'SD', '57216', '2013-01-01' );
    insert into a_bkorders.customers  values  (228175, 'Cardin','Benjamin',      'MD', '20609', '2013-04-02' );
    insert into a_bkorders.customers  values  (228275, 'Mikulski','Barbara',     'MD', '21203', '2013-04-04' );
    insert into a_bkorders.customers  values  (228352, 'Edwards','Donna',        'MD', '21205', '2013-06-08' );


-- orders and  order_details 
/* July 2011 */
Insert into a_bkorders.order_headers    values(21841,  '2011-07-02', 267780);
   Insert into a_bkorders.order_details values(21841, 1, 1448, 50,  25.00);

Insert into a_bkorders.order_headers    values(21850,  '2011-07-02', 261502);
   Insert into a_bkorders.order_details values(21850, 1, 1162,  1,  30.49);
   Insert into a_bkorders.order_details values(21850, 2, 1109,  1,  25.00);

Insert into a_bkorders.order_headers    values(2045,  '2011-07-18', 267780);
   Insert into a_bkorders.order_details values(2045, 1, 1894,  1,  35.99);   

Insert into a_bkorders.order_headers    values(2200,  '2011-07-18', 261502);
   Insert into a_bkorders.order_details values(2200, 1, 1200,  5,  16.33);
   Insert into a_bkorders.order_details values(2200, 2, 1180,  5,  45.99);
   Insert into a_bkorders.order_details values(2200, 3, 1128,  5,  46.20);


/* Sep 2011 */

Insert into a_bkorders.order_headers    values(22909,  '2011-09-25', 239427);
   Insert into a_bkorders.order_details values(22909, 1, 1104,  5,  45.00);

Insert into a_bkorders.order_headers    values(22910,  '2011-09-25', 218709);
   Insert into a_bkorders.order_details values(22910, 1, 1678,  5,  49.99);
   Insert into a_bkorders.order_details values(22910, 2, 1162,  5,  35.00);

Insert into a_bkorders.order_headers    values(32997,  '2011-09-22', 239427);
   Insert into a_bkorders.order_details values(32997, 1, 1948,  5,  40.94);  
   Insert into a_bkorders.order_details values(32997, 2, 1199,  5,  18.39);
   Insert into a_bkorders.order_details values(32997, 3, 1457,  5,  53.99);
   Insert into a_bkorders.order_details values(32997, 4, 1133,  5,  18.15);
   Insert into a_bkorders.order_details values(32997, 5, 1894,  5,  36.79);


Insert into a_bkorders.order_headers    values(32998,  '2011-09-22', 261502);
   Insert into a_bkorders.order_details values(32998, 1, 2006,  3,  20.00);

Insert into a_bkorders.order_headers    values(41005,  '2011-09-28', 290298);
   Insert into a_bkorders.order_details values(41005, 1, 1142,  2,  42.45);
   Insert into a_bkorders.order_details values(41005, 2, 1107,  4,  21.50);

Insert into a_bkorders.order_headers    values(41006,  '2011-09-28', 267780);
   Insert into a_bkorders.order_details values(41006, 1, 1142, 10, 42.95);  

Insert into a_bkorders.order_headers    values(42899,  '2011-09-29', 261502);
   Insert into a_bkorders.order_details values(42899, 1, 1128,  5, 25.00);
   Insert into a_bkorders.order_details values(42899, 2, 1103,  1 , 10.95);  

/* Oct 2011 */

Insert into a_bkorders.order_headers    values(21254,  '2011-10-23', 263119);
   Insert into a_bkorders.order_details values(21254, 2, 2008,  10,   46.95);
   Insert into a_bkorders.order_details values(21254, 3, 2007,  10,   39.00);

Insert into a_bkorders.order_headers    values(21255,  '2011-10-28', 267780);
   Insert into a_bkorders.order_details values(21255, 1, 1101,  5,    59.99);
   Insert into a_bkorders.order_details values(21255, 2, 1142,  5,    39.00);
   Insert into a_bkorders.order_details values(21255, 3, 1162,  2,    35.00);

Insert into a_bkorders.order_headers    values(21261,  '2011-10-28', 200368);
   Insert into a_bkorders.order_details values(21261, 1, 1142,  100,  34.95);
   Insert into a_bkorders.order_details values(21261, 2, 1128,  50,   46.95);
   Insert into a_bkorders.order_details values(21261, 3, 2001,  100,  39.00);

Insert into a_bkorders.order_headers    values(32905,  '2011-10-02', 259906);
   Insert into a_bkorders.order_details values(32905, 1, 2028,  1,  58.00);

Answer 1

试试这个:

SELECT c.cust_id, COUNT(DISTINCT od.book_id) bookCnt
FROM a_bkorders.customers c 
INNER JOIN a_bkorders.order_headers oh ON c.cust_id = oh.cust_id 
INNER JOIN a_bkorders.order_details od ON oh.order_id = od.order_id 
GROUP BY c.cust_id
ORDER BY bookCnt DESC