sql语句记录

需求：在供应商接龙页面内，以供应商id为分组，显示三条最新接龙（卡片格式。每个卡片的title是供应商的昵称，其下为三条供应商转发给我的接龙）。


SELECT t1.shop_supplier_id, t1.id
FROM shop_tuan_list t1
GROUP BY t1.shop_supplier_id, t1.id
ORDER BY t1.shop_supplier_id, t1.id

按相同的供应商id分组，并以小到大显示，其内部再用id以小到大排序。

SELECT t1.shop_supplier_id, COUNT(t1.id) AS count 
FROM shop_tuan_list t1
GROUP BY t1.shop_supplier_id
HAVING COUNT(t1.id) >= 2;

COUNT: 记录在同样的shop_supplier_id中的id行数
HAVING: 只保留有id行数大于等于2的shop_supplier_id

SELECT t1.id, t1.supplier_id, t2.id, t2.supplier_id
FROM test t1
LEFT JOIN test t2
    ON t2.supplier_id = t1.supplier_id 
    AND t2.id <= t1.id
ORDER BY t1.id, t2.id;

AND条件：id等于1的时候显示一条，等于2的时候显示两条，以此类推。
如果是大于等于的话，id等于4的时候显示一条，等于3的时候显示两条，以此类推。
LEFT JOIN: 链接的时候保留t1所有的内容，有可能在条件下会出现重复数据。t2不匹配t1表数据的时候，t2的字段显示null

SELECT t1.id, t1.supplier_id
FROM test t1
LEFT JOIN test t2
    ON t2.supplier_id = t1.supplier_id AND t2.id <= t1.id
GROUP BY t1.supplier_id, t1.id
HAVING COUNT(t2.id) <= 3
ORDER BY t1.supplier_id, t1.id;

从小到大

SELECT t1.id, t1.supplier_id
FROM test t1
LEFT JOIN test t2
    ON t2.supplier_id = t1.supplier_id AND t2.id >= t1.id
GROUP BY t1.supplier_id, t1.id
HAVING COUNT(t2.id) <= 3
ORDER BY t1.supplier_id, t1.id;

从大到小

Both：按照供应商分组，只显示三条最新的接龙。

LEFT JOIN 会产生很多冗余的行，接着再通过 GROUP BY 和 HAVING 进行筛选，这样的操作效率比较低。可以考虑使用Row_number () Over () 来代替 LEFT JOIN 和 GROUP BY，从而减少不必要的计算。

WITH RankedTest AS (
    SELECT
        t1.*,
        ROW_NUMBER() OVER (PARTITION BY t1.shop_supplier_id ORDER BY t1.id DESC) AS row_num
    FROM
        shop_tuan_list t1
)
SELECT
    t1.*
FROM
    RankedTest t1
WHERE
    row_num <= 3
ORDER BY
    t1.shop_supplier_id,
    t1.id;

ROW_NUMBER() ：为每个组内数据按顺序生成一个唯一的行号。
PARTITION BY：按照字段名字进行分组。

WITH Selection AS (
  WITH RankedTest AS ( 
    SELECT t1.*, ROW_NUMBER() OVER ( PARTITION BY t1.shop_supplier_id ORDER BY t1.id DESC ) AS row_num FROM shop_tuan_list t1 ) 
    SELECT t1.* 
  FROM
    RankedTest t1 
  WHERE
    row_num <= 3 
  ORDER BY
    t1.shop_supplier_id,
    t1.id
  
) SELECT
t1.*,
t2.avatarUrl 
FROM
  Selection t1
  LEFT JOIN shop_user t2 
    ON t2.user_id = t1.shop_supplier_id

需要加上供应商的头像
供应商的头像存储在shop_user中，需要用left join把两表合并