SQL sever 数据库统计查询和组合查询

淩亂°似流年 2022-08-22 13:25 224阅读 0赞

商品（编号，品名，进价，库存，售价，厂商编号）   
顾客（卡号，姓名，电话，积分）  
厂商（编号，厂址，名称、电话）  
销售（顾客卡号，商品编号，数量，日期）  
根据上面基本表的信息完成下列查询。  
1 查询积分最高的顾客姓名  
2 查询销售数量1000以上的商品的厂商电话。  
3 查询生产商品5种以上的厂商信息。  
4 查询没有顾客买的商品信息。

建表语句

create table \[dbo\].\[Guke\]  
(Gno char(10) primary key,  
Gname char(20),  
G\_tel smallint,  
jifen smallint  
);  
create table \[dbo\].\[Changshang\]  
(Cno char(10)primary key,  
addre char(40),  
Cname char(20),  
C\_tel smallint  
);

create table \[dbo\].\[Shangpin\]  
(Sno char(10) primary key,  
Sname char(10),  
jin\_jia smallint,  
kucun smallint,  
shou\_jia smallint,  
Cno char(10),  
foreign key(Cno)references Changshang(Cno)  
  
  
);  
  
  
create table \[dbo\].\[Xiaoshou\]  
(Gno char(10),  
Sno char(10),  
shuliang smallint,  
X\_time char(20),  
primary key(Gno,Sno),  
foreign key(Sno)references Shangpin(Sno),  
foreign key(Gno)references Guke(Gno)  
);

\--查询积分最高的客户姓名

\--方法1  
SELECT Gname  
FROM Guke  
WHERE jifen = ( SELECT MAX(jifen)  
FROM Guke  
)  
\------------------------   
\--方法2   
SELECT TOP 1  
Gname  
FROM Guke  
ORDER BY jifen DESC   
\--------------------------------------------------  
\--查询销售数量1000以上的商品的厂商电话  
SELECT DISTINCT  
C.C\_tel  
FROM ( SELECT Sno ,  
SUM(shuliang) AS shuliang  
FROM Xiaoshou  
GROUP BY Sno  
) A  
LEFT JOIN Shangpin B ON A.Sno = B.Sno  
LEFT JOIN Changshang C ON B.Cno = C.Cno  
WHERE A.shuliang > 1000  
\--------------------------------------------------  
\--查询生产商品5种以上的厂商信息  
SELECT B.\*  
FROM Shangpin A  
LEFT JOIN Changshang B ON A.Cno = B.Cno  
GROUP BY B.Cno ,  
B.addre ,  
B.Cname ,  
B.C\_tel  
HAVING COUNT(B.Cno) > 5  
\--------------------------------------------------  
\--查询没有顾客买的商品和购买数量少于3个的商品  
\--方法1  
SELECT DISTINCT  
A.\*  
FROM Shangpin A  
LEFT JOIN ( SELECT Sno ,  
SUM(shuliang) AS shuliang  
FROM Xiaoshou  
GROUP BY Sno  
) B ON A.Sno = B.Sno  
WHERE  B.shuliang IS NULL  
\-------------------------  
\--方法2  
SELECT A.\*  
FROM Shangpin A  
WHERE NOT EXISTS (  
SELECT B.Sno  
FROM Xiaoshou B  
WHERE B.Sno = A.Sno  
GROUP BY B.Sno  
HAVING SUM(shuliang) !=0   
)