SQL語句多表聯(lián)合查詢

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 去除重復(fù)
SELECT distinct customerName FROM Orders

–2、GROUP BY 提取組合 并去重
SELECT customerName, orderPrice
FROM orders
GROUP BY customerName, orderPrice
—3、GROUP BY + COUNT 提取組合 計(jì)算重復(fù) ,
SELECT customerName, orderPrice, count() as 重復(fù)數(shù)
FROM orders
GROUP BY customerName, orderPrice ;
– Count(1)和Count()實(shí)際上的意思是，評估Count（）中的表達(dá)式是否為NULL，如果為NULL則不計(jì)數(shù)，而非NULL則會(huì)計(jì)數(shù)。
SELECT customerName, orderPrice, count(*) as 重復(fù)數(shù)
FROM orders
GROUP BY customerName, orderPrice ;
– COUNT根據(jù)select后的列進(jìn)行查詢，有則計(jì)數(shù)。不管其他的列。
SELECT CustomerName, COUNT(1) AS 重復(fù)數(shù)
FROM Orders
GROUP BY CustomerName --記錄Customer 每種值的記錄數(shù)

–5、sum 和 GROUP BY
SELECT CustomerName, sum(orderPrice) AS sumOrderPrice
FROM Orders
GROUP BY CustomerName --記錄Customer 每種值的對應(yīng)的OrderPrice的累加和
–6、AVG 和 GROUP BY 的多表聯(lián)合查詢 不加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 的多表聯(lián)合查詢 加上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;

---

??對數(shù)據(jù)分析、機(jī)器學(xué)習(xí)、數(shù)據(jù)科學(xué)、金融風(fēng)控等感興趣的小伙伴，需要數(shù)據(jù)集、代碼、行業(yè)報(bào)告等各類學(xué)習(xí)資料，可關(guān)注微信公眾號：風(fēng)控圏子（別打錯(cuò)字，是圏子，不是圈子，算了直接復(fù)制吧！）

??關(guān)注公眾號后，可聯(lián)系圈子助手加入我們的機(jī)器學(xué)習(xí)風(fēng)控討論群和反欺詐討論群。（記得要備注喔！）

??相互學(xué)習(xí)，共同成長。

總結(jié)

以上是生活随笔為你收集整理的【SQL】SQL语句多表联合查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。