sql
CREATE TABLE Users (
  user_id INT PRIMARY KEY,
  join_date DATE,
  favorite_brand VARCHAR(250)
);

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  item_id INT,
  buyer_id INT,
  seller_id INT,
  FOREIGN KEY (item_id) REFERENCES Items(item_id),
  FOREIGN KEY (buyer_id) REFERENCES Users(user_id),
  FOREIGN KEY (seller_id) REFERENCES Users(user_id)
);

CREATE TABLE Items (
  item_id INT PRIMARY KEY,
  item_brand VARCHAR(250)
);

INSERT INTO Users (user_id, join_date, favorite_brand)
VALUES
  (1, '2018-01-01', 'Lenovo'),
  (2, '2018-02-09', 'Samsung'),
  (3, '2018-01-19', 'LG'),
  (4, '2018-05-21', 'HP');

INSERT INTO Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
  (1, '2019-08-01', 4, 1, 2),
  (2, '2018-08-02', 2, 1, 3),
  (3, '2019-08-03', 3, 2, 3),
  (4, '2018-08-04', 1, 4, 2),
  (5, '2018-08-04', 1, 3, 4),
  (6, '2019-08-05', 2, 2, 4);

INSERT INTO Items (item_id, item_brand)
VALUES
  (1, 'Samsung'),
  (2, 'Lenovo'),
  (3, 'LG'),
  (4, 'HP');

select * from Items;

select Users.user_id as buyer_id, join_date, ifnull(UserBuy.cnt, 0) as orders_in_2019
from Users
left join (
    select buyer_id, count(order_id) cnt
    from Orders
    where order_date between '2019-01-01' and '2019-12-31'
    group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id


SELECT
  u.user_id AS buyer_id,
  u.join_date,
  COUNT(CASE WHEN YEAR(o.order_date) = 2019 THEN 1 ELSE NULL END) AS orders_in_2019
FROM
  Users u
LEFT JOIN
  Orders o ON u.user_id = o.buyer_id
GROUP BY
  u.user_id,
  u.join_date;


select user_id as buyer_id, join_date, count(order_id) 
as orders_in_2019
from Users as u left join 
(select * from Orders where year(order_date)='2019') as o on u.user_id = o.buyer_id
group by user_id

sql

CREATE TABLE Products (
  product_id INT,
  new_price INT,
  change_date DATE,
  PRIMARY KEY (product_id, change_date)
);

INSERT INTO Products (product_id, new_price, change_date)
VALUES
  (1, 20, '2019-08-14'),
  (2, 50, '2019-08-14'),
  (1, 30, '2019-08-15'),
  (1, 35, '2019-08-16'),
  (2, 65, '2019-08-17'),
  (3, 20, '2019-08-18');



select p1.product_id, ifnull(p2.new_price, 10) as price
from (
    select distinct product_id
    from Products
) as p1 -- 所有的产品
left join (
    select product_id, new_price
    from Products
    where (product_id, change_date) in (
        select product_id, max(change_date)
        from Products
        where change_date <= '2019-08-16'
        group by product_id
    )
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id


SELECT
    product_id,
    IFNULL(new_price, 10) AS price
FROM
    Products
    RIGHT JOIN (
        SELECT
            product_id,
            MAX(IF(change_date <= '2019-08-16', change_date, NULL)) AS change_date
        FROM
            Products
        GROUP BY
            product_id
    ) AS t2 USING(product_id, change_date);
    


SELECT DISTINCT product_id, IF(filter_date IS NULL, 10, new_price) AS price
FROM (
  SELECT *, RANK() OVER(PARTITION BY product_id ORDER BY filter_date DESC) AS RANKING
  FROM (
    SELECT *, IF(change_date > '2019-08-16', NULL, change_date) AS filter_date
    FROM Products
  ) T
) TT
WHERE TT.RANKING = 1

only_full_group_by是MySQL中的一个SQL模式，它影响了GROUP BY子句的行为。在默认情况下，MySQL允许在GROUP BY子句中只列出部分列，并且可以在SELECT列表中引用未在GROUP BY子句中列出的非聚合列。

但是，启用only_full_group_by模式后，MySQL要求GROUP BY子句中列出的列必须包含所有未在聚合函数中使用的非聚合列。换句话说，所有未聚合的列必须在GROUP BY子句中显式列出。

这个模式的目的是确保查询结果的一致性和可预测性。在启用only_full_group_by模式的情况下，如果查询中的GROUP BY子句不符合规定，MySQL将抛出错误，以避免可能产生模糊或不确定结果的查询。

需要注意的是，only_full_group_by模式在MySQL 5.7.5及更高版本中默认启用。如果需要在该模式下使用非聚合列，必须将这些列添加到GROUP BY子句中或使用聚合函数对它们进行处理。

可以通过以下方式查看或更改SQL模式设置：

sql
-- 查看当前SQL模式设置
SELECT @@sql_mode;

-- 更改SQL模式设置（示例）
SET sql_mode = 'modes_to_enable';

在modes_to_enable中，可以指定所需的SQL模式，多个模式之间使用逗号分隔。要启用only_full_group_by模式，可以将其包含在modes_to_enable中。

using USING关键字在SQL中用于指定两个或多个表之间的共享列或连接列。

在JOIN操作中，USING关键字可以代替ON关键字来指定连接条件。它适用于两个表具有相同列名且希望使用这些列进行连接的情况。

以下是使用USING关键字进行连接的示例：

sql
SELECT *
FROM table1
JOIN table2 USING (common_column);

在上述示例中，table1和table2是要连接的两个表，它们都有一个名为common_column的列。通过使用USING关键字，我们指定了连接条件为两个表中的common_column列。

使用USING关键字的连接具有以下特点：

• 只能指定连接条件中的共享列，且这些列的名称在连接的两个表中必须相同。
• 在连接结果中，共享列只出现一次，而不会重复显示。
• USING关键字只能用于内连接（INNER JOIN）和自然连接（NATURAL JOIN），不能用于外连接（LEFT JOIN、RIGHT JOIN等）。

使用USING关键字进行连接时，如果两个表中的共享列有相同的列名但具有不同的数据类型，则可能会导致语法错误或意外的结果。因此，在使用USING关键字连接表之前，应确保共享列具有相同的列名和数据类型。