【SQL】SQL语句多表联合查询

SQL语句多表联合查询

CREATE TABLE orders(

id int not null primary key identity(1,1),
customerName varchar(100),
orderDate varchar(100),
orderPrice float
);

insert into orders(customerName,orderDate,orderPrice)values(‘B’,‘2018-3-1’,1000);
insert into orders(customerName,orderDate,orderPrice)values(‘C’,‘2018-2-30’,1600);
insert into orders(customerName,orderDate,orderPrice)values(‘B’,‘2018-3-6’,700);
insert into orders(customerName,orderDate,orderPrice)values(‘B’,‘2018-1-1’,1000);
insert into orders(customerName,orderDate,orderPrice)values(‘A’,‘2017-3-1’,2000);
insert into orders(customerName,orderDate,orderPrice)values(‘C’,‘2017-12-1’,100);
select *from orders;

–1.distinct 去除重复
SELECT distinct customerName FROM Orders

–2、GROUP BY 提取组合 并去重
SELECT customerName, orderPrice
FROM orders
GROUP BY customerName, orderPrice
—3、GROUP BY + COUNT 提取组合 计算重复 ,
SELECT customerName, orderPrice, count() as 重复数
FROM orders
GROUP BY customerName, orderPrice ;
– Count(1)和Count()实际上的意思是，评估Count（）中的表达式是否为NULL，如果为NULL则不计数，而非NULL则会计数。
SELECT customerName, orderPrice, count(*) as 重复数
FROM orders
GROUP BY customerName, orderPrice ;
– COUNT根据select后的列进行查询，有则计数。不管其他的列。
SELECT CustomerName, COUNT(1) AS 重复数
FROM Orders
GROUP BY CustomerName --记录Customer 每种值的记录数

–5、sum 和 GROUP BY
SELECT CustomerName, sum(orderPrice) AS sumOrderPrice
FROM Orders
GROUP BY CustomerName --记录Customer 每种值的对应的OrderPrice的累加和
–6、AVG 和 GROUP BY 的多表联合查询 不加where
SELECT B.id, A.CustomerName, AVG(OrderPrice) AS 平均值
FROM Orders AS A,t_Admin AS B
GROUP BY B.id, A.CustomerName ;

–7、AVG 和 GROUP BY 的多表联合查询 加上where
SELECT B.id, A.customerName, AVG(OrderPrice) AS 平均值
FROM orders AS A,t_Admin AS B
WHERE A.id = B.id
GROUP BY B.id, A.customerName;

SELECT * FROM orders;
select * from t_Admin;

---

  对数据分析、机器学习、数据科学、金融风控等感兴趣的小伙伴，需要数据集、代码、行业报告等各类学习资料，可关注微信公众号：风控圏子（别打错字，是圏子，不是圈子，算了直接复制吧！）

  关注公众号后，可联系圈子助手加入我们的机器学习风控讨论群和反欺诈讨论群。（记得要备注喔！）

  相互学习，共同成长。