mysql - 如何选择第二个表中具有最多不同行的行 - MySQL？

Question

对于给定的类别，我试图找到 Track_Record 中记录的不同位置最多的产品 item_name。

无论我尝试什么，我似乎都无法弄清楚这个查询。

如果有人不介意给我一些提示，我将不胜感激。

这些是相关表格:

CREATE TABLE IF NOT EXISTS `Track_Record` (
  `longitude` varchar(15) NOT NULL ,
  `lattitude` varchar(15) NOT NULL ,
  `datetime` DATETIME NOT NULL,
  `EPC` varchar(200) NOT NULL ,
  `ip` varchar(50) NOT NULL ,

  PRIMARY KEY  (ip, EPC, datetime),
  FOREIGN KEY (EPC) REFERENCES Product(EPC) ON DELETE CASCADE,
  FOREIGN KEY (ip) REFERENCES RFID_Reader(ip) ON DELETE CASCADE
);  

CREATE TABLE IF NOT EXISTS `Prod_Cat` (
  `category_id` int(20) NOT NULL ,
  `EPC` varchar(200) NOT NULL ,
  PRIMARY KEY  (category_id, EPC),

  FOREIGN KEY (EPC) REFERENCES Product(EPC) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE CASCADE
);  

CREATE TABLE IF NOT EXISTS `Product` (
  `EPC` varchar(200) NOT NULL ,
  `expiry_date` DATE NOT NULL,
  `production_date` DATE NOT NULL,
  `prod_description` varchar(200) NOT NULL ,
  `item_name` varchar(100) NOT NULL ,
  `manufacturer_info` varchar(200) NOT NULL ,
  `account_name` varchar(100) NOT NULL ,
  `password` varchar(100) NOT NULL ,

  PRIMARY KEY  (EPC),
  FOREIGN KEY (password, account_name) REFERENCES Owner(password, account_name) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `Category` (
  `category_id` int(20) NOT NULL,
  `name` varchar(100) NOT NULL ,
  `description` varchar(200) NOT NULL,
  `sub_category_id` int(20) NULL,

  PRIMARY KEY  (category_id),

  FOREIGN KEY Category(sub_category_id) REFERENCES Category(category_id) ON DELETE CASCADE
);

这是数据:

Category (name, description, category_id)
VALUES
('Technology', 'Technological Devices', 0),
('Dining Wear', 'Dinner plates and cups etc.', 1),
('Clothing', 'Stuff you wear', 2),
('Stationary', 'Office stuff!', 3);

Product (EPC, expiry_date, production_date, prod_description, item_name, manufacturer_info, password, account_name)
VALUES
('01.0000A89.00016F.000169DCD', '2019-01-09','2014-01-09', 'for eating off', 'Plate', 'man@company.com', 'a', 'John Smith'),
('02.0000A89.00016F.000169DCD', '2016-07-03','2013-01-23', 'for drinking from', 'Cup', 'somebody@aplaceinspace.com', 'c', 'John Mccane Manufacturer'),
('03.0000A89.00016F.000169DCD', '2018-09-23','2012-02-09', 'For playing games', 'Playstaion', 'nerdsrus@bamboozled.com', 'f', 'John Twitching'),
('04.0000A89.00016F.000169DCD', '2015-02-12','2014-03-11', 'For playing CDs', 'CD Player', 'whynotuseMP3@hopeless.com', 'd', 'John Dickson Retailer'),
('05.0000A89.00016F.000169DCD', '2017-06-11','2014-06-02', 'USB Storage Device', 'USB Stick', 'imustbeenglish@pommebugger.com', 'b', 'John Mitchal'),
('06.0000A89.00016F.000169DCD', '2019-02-08','2014-08-04', 'Wrap around your neck as a fashion statement', 'Boa Constrictor', 'byebye@shortofbreath.com', 'c', 'John Mccane Manufacturer');

Prod_Cat (category_id, EPC)
VALUES
('0', '03.0000A89.00016F.000169DCD'),
('0', '04.0000A89.00016F.000169DCD'),
('0', '05.0000A89.00016F.000169DCD'),
('1', '01.0000A89.00016F.000169DCD'),
('1', '02.0000A89.00016F.000169DCD'),
('2', '06.0000A89.00016F.000169DCD');

Track_Record (ip, longitude, lattitude, datetime, EPC)
VALUES
('000.111.222', '27.4667 S', '153.0333 E', '2014-11-05 18:56:46', '03.0000A89.00016F.000169DCD'),
('000.111.222', '27.4667 S', '153.0333 E', '2015-05-12 13:21:16', '03.0000A89.00016F.000169DCD'),
('555.666.777', '22.2783 N', '114.1747 E', '2012-07-19 12:22:16', '04.0000A89.00016F.000169DCD'),
('000.111.222', '27.4667 S', '153.0333 E', '2011-03-01 11:43:26', '03.0000A89.00016F.000169DCD'),
('555.666.777', '22.2783 N', '114.1747 E', '2014-09-02 18:53:14', '06.0000A89.00016F.000169DCD'),
('222.333.444', '59.3500 N', '18.0667 E', '2015-10-15 18:23:18', '04.0000A89.00016F.000169DCD'),
('333.444.555', '15.7833 S', '47.8667 W', '2015-02-22 19:53:16', '01.0000A89.00016F.000169DCD'),
('444.555.666', '51.5072 N', '0.1275 W', '2013-01-11 22:21:15', '04.0000A89.00016F.000169DCD');

Answer 1

获取计数是一个简单的聚合:

select pc.cat_id, p.item_name, count(*)
from track_record tr join
     prod_category pc
     on tr.epc = pc.epc join
     product p
     on p.epc = pc.epc
group by pc.cat_id, p.item_name;

获取最大计数的最简单方法可能是使用 substring_index()/group_concat() 技巧:

select pc.cat_id, cnt,
       substring_index(group_concat(item_name order by cnt desc), ',', 1) as most_common_item
from (select pc.cat_id, p.item_name, count(*) as cnt
      from track_record tr join
           prod_category pc
           on tr.epc = pc.epc join
           product p
           on p.epc = pc.epc
      group by pc.cat_id, p.item_name
     ) x
group by pc.cat_id;