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;