1、有一张表t5,根据name 分类,并得出name相同数据的条数汇总展示,以及展示各个name的class集合
SELECT id,name, COUNT(*) as num,GROUP_CONCAT(class)
from t5
GROUP BY `name`
ORDER BY num desc
2、根据表books 和表book_order 查询出去年销量小于10本的书籍 不包括从今天起一个月内可使用的书籍假设今天为2019-06-23
SELECT a.book_id,a.name
FROM
(SELECT b.book_id,b.`name`,COALESCE(sum(o.quantity),0) AS sumnum,o.dispatch_date,b.available_from
FROM books b
LEFT JOIN book_order o ON b.book_id=o.book_id
GROUP BY book_id
HAVING o.dispatch_date<'2019-01-01' OR o.dispatch_date is NULL)a
WHERE a.available_from<'2019-01-01'