mysql - SQL 按不同字段排序

Question

我有下一个表格和行。我需要编写一个 SELECT 来返回按以下方式排序的所有类别:

(A) 他们拥有的元素数量(B) 类别名称。

此查询应获取以下列:类别名称、项目数 (AS N_ITEMS) 和该类别中标题的平均价格 (AS AVERAGE_PRICE)

CREATE TABLE `category` (
  `CATEGORY_ID` int(11) NOT NULL,
  `CATEGORY_NAME` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `category` (`CATEGORY_ID`, `CATEGORY_NAME`) VALUES
(1, 'Sports'),
(2, 'Actualités'),
(3, 'Animaux'),
(4, 'Economie'),
(5, 'Cuisine');


CREATE TABLE `item` (
  `ITEM_ID` int(11) NOT NULL,
  `CATEGORY_ID` int(11) NOT NULL,
  `ITEM_NAME` varchar(50) NOT NULL,
  `ITEM_PRICE` decimal(8,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `item` (`ITEM_ID`, `CATEGORY_ID`, `ITEM_NAME`, `ITEM_PRICE`) VALUES
(1, 1, 'Equip', '6.00'),
(2, 2, 'Le Monde', '3.00'),
(3, 2, 'Le Parisien', '2.50'),
(4, 2, 'France soir', '3.00'),
(5, 3, '30 Million damis', '6.20'),
(6, 3, 'Cheval pratique', '4.50'),
(7, 4, 'Capital', '2.50');

ALTER TABLE `category`
  ADD PRIMARY KEY (`CATEGORY_ID`);

ALTER TABLE `item`
  ADD PRIMARY KEY (`ITEM_ID`);

ALTER TABLE `category`
  MODIFY `CATEGORY_ID` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `item`
  MODIFY `ITEM_ID` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

Answer 1

您可以尝试以下 -

select CATEGORY_NAME, count(ITEM_ID) as noofItem,avg(item_price) as avgprice
from category inner join item on category.category_id=item.category_id
group by CATEGORY_NAME
order by noofItem,CATEGORY_NAME