需要帮助计数最不同的书 [英] Needing assistance with counting the most different books
本文介绍了需要帮助计数最不同的书的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
哪些客户订购了最多不同的书籍？显示客户ID并包括关系。这不是说订单的数量。例如，假设我的唯一订单是客户1订购400份book_id 34客户2订购2份book_id 62和3份book_id 29客户1订购了更多的书籍，但客户2订购了更多的不同的书籍。查询应该在这种情况下返回客户2：
Which customer(s) ordered the most different books? Display the customer id and include ties. This is not talking about the quantity of books ordered. For example, suppose my only orders are Customer 1 ordered 400 copies of book_id 34 Customer 2 ordered 2 copies of book_id 62 and 3 copies of book_id 29 Customer 1 ordered a larger quantity of books but customer 2 ordered more different books. The query should - in this case- return customer 2:
这是我尝试的解决方案。不确定如何解决这个问题：
Here is my attempted solution. Not sure how to solve this one:
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);
推荐答案
尝试：
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
这篇关于需要帮助计数最不同的书的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
