日韩天天综合网_野战两个奶头被亲到高潮_亚洲日韩欧美精品综合_av女人天堂污污污_视频一区**字幕无弹窗_国产亚洲欧美小视频_国内性爱精品在线免费视频_国产一级电影在线播放_日韩欧美内地福利_亚洲一二三不卡片区

Sql學習第一天——SQL 練習題(建表/sql語句)_Mssql數(shù)據(jù)庫教程

編輯Tag賺U幣

推薦：Sql學習第三天——SQL 關(guān)于with ties介紹
with ties一般是和Top , order by相結(jié)合使用的,會查詢出最后一條數(shù)據(jù)額外的返回值，接下來將為大家詳細介紹下，感興趣的各位可以參考下哈

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

建表:

復(fù)制代碼 代碼如下:www.hl5o.cn

set nocount on --當 SET NOCOUNT 為 ON 時，不返回計數(shù)（表示受 Transact-SQL 語句影響的行數(shù)）。當 SET NOCOUNT 為 OFF 時，返回計數(shù)
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 values(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);

------------------------------------------------------------------------------------------------------------------------------

做題分析：

復(fù)制代碼 代碼如下:www.hl5o.cn

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

結(jié)果如圖所示：

--第一次想到的答案,突然發(fā)現(xiàn)少了一個來自Madrid的FISSA訂單,FISSA訂單數(shù)量為0,所以在Orders表中沒有出現(xiàn),所以上面的寫法會少一個.

--推翻了上面的答案,又想到了用表的連接,而用內(nèi)連接出現(xiàn)的情況會和上面的一樣,所以我選擇了左連接,如下:

復(fù)制代碼 代碼如下:www.hl5o.cn

select C.customerid as 消費者,count(O.customerid) as 訂單數(shù)
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

結(jié)果如圖所示：

--查詢發(fā)現(xiàn)是正確的。

--分析查看不帶條件的左連接

復(fù)制代碼 代碼如下:www.hl5o.cn

select * from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid
復(fù)制代碼 代碼如下:www.hl5o.cn

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

結(jié)果如圖所示：

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

復(fù)制代碼 代碼如下:www.hl5o.cn

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

結(jié)果如圖所示：

--書中給的只是多了一個order by 進行定義了排序方式(以numorders這一列的升序進行排序)

分享：Sql學習第二天——SQL DML與CTE概述
關(guān)于DML即數(shù)據(jù)操縱語言；關(guān)于CTE即公用表表達式，接下來為大家介紹下CTE使用的地方以及優(yōu)點同時提供一個簡單的CTE例題，感興趣的各位可以參考下哈

來源：模板無憂//所屬分類：Mssql數(shù)據(jù)庫教程/更新時間：2013-04-23
相關(guān)Mssql數(shù)據(jù)庫教程：