導讀:1建站知識來自Madrid且訂單數少于3的消費者,針對這個要求作出以下:建表做題分析以及sql語句的寫法，感興趣的朋友可以參考下seo網站優化營銷型網站建設。

題目:來自Madrid且訂單數少于3的消費者

建表:

復制代碼 代碼如下:

set nocount on --當 SET NOCOUNT 為 ON 時，不返回計數（表示受 Transact-SQL 語句影響的行數）。當 SET NOCOUNT 為 OFF 時，返回計數 use SY GO if object_Id('dbo.Orders') is not null drop table dbo.Orders GO if object_Id('dbo.Customers') is not null drop table dbo.Customers GO create table dbo.Customers ( customerid char(5) not null primary key , city varchar(10) not null ); insert into dbo.Customers values('FISSA','Madrid'); insert into dbo.Customers values('FRNDO','Madrid'); insert into dbo.Customers values('KRLOS','Madrid'); insert into dbo.Customers values('MRPHS','Zion'); create table dbo.Orders ( orderid int not null primary key , customerid char(5) null references customers(customerid) ) insert into dbo.Orders values(1,'FRNDO'); insert into dbo.Orders value高端網站建設s(2,'FRNDO'); insert into dbo.Orders values(3,'KRLOS'); insert into dbo.Orders values(4,'KRLOS'); insert into dbo.Orders values(5,'KRLOS'); insert into dbo.Orders values(6,'MRPHS'); insert into dbo.Orders values(7,null);

---百度seo網站優化---------------------------------------------------------------------------------------------------------------------------

做題分析：

復制代碼 代碼如下:

select customerid as 消費者,count(customerid) as 訂單數 from dbo.Orders where customerid in ( select customerid from dbo.Customers where city = 'Madrid') group by customerid having count(customerid) < 3

結果如圖所示：

--第一次想到的答案,突然發現少了一個來自Madrid的FISSA訂單,FISSA訂單數量為0,所以在Orders表中沒有出現,所以上面的寫法會少一個.--推網站seo優化課程翻了上面的答案,又想到了用表的連接,而用內連接出現的情況會和上面的一樣,所以我選擇了左連接,如下:

復制代碼 代碼如下:

select C.customerid as 消費者,count(O.customerid) as 訂單數 from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid where C.city= 'Madrid' group by C.customerid having count(C.customerid) < 3

結果如圖所示：

--查詢發現是正確的。--分析查看不帶條件的左連接

復制代碼 代碼如下:

select * from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid

復制代碼 代碼如下:

select * from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid

結果如圖所示：

--書中給的標準答案是：

復制代碼 代碼如下:

select C.customerid , count(O.orderid) as numorders from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid where C.city= 'Madrid' group by C.customerid having count(O.orderid) < 3 order by numorders

關鍵詞標簽: SQL 語句 練習題