本当にSQLができますか?冷たいTopとApply
13649 ワード
本住所:http://blog.csdn.net/shanglianlm/article/details/49942869
この章ではTopとApplyの基本的な使い方を書いておきたいのですが、あまり意味がないようなので、無駄なものを削除し、いくつかの例だけを残してシリーズの完全性を保証します.
TopとApplyが解決する一般的な問題は、従業員1人当たりの最新の注文を3つ返すほど、注文の時間が新しいほど優先度が高くなりますが、時間フレームの注文の優先度、例えばidを決勝属性として使用できるように、決勝属性を導入する必要があります.ここで提供されるソリューションは、他のソリューションよりもずっと簡単で、実行速度が速い.
各従業員の最新の注文を3つ返します.
APPLYで解決:
まずemployeesをスキャンしてempidを取得し、各empid値に対してordersテーブルクエリに対して従業員の3つの最新注文を返します.ここでは、複数のプロパティを返すことができます.
もう1つの解決策は、特定の状況でAPPLY演算子を使用する方法よりも速く、ROW_を使用することです.NUMBER関数.まず、受注ごとに行番号を計算し、empidでパーティション化し、orderdate desc、orderid descの順にソートします.次に、外部クエリーでは、行番号が3以下の行のみがフィルタされます.
次のようになります.
練習:
学生表から対応するクラスの上位num名の学生成績を選択する
参照SQL:
この文書は次のとおりです.http://blog.jobbole.com/94919/
この章ではTopとApplyの基本的な使い方を書いておきたいのですが、あまり意味がないようなので、無駄なものを削除し、いくつかの例だけを残してシリーズの完全性を保証します.
TopとApplyが解決する一般的な問題は、従業員1人当たりの最新の注文を3つ返すほど、注文の時間が新しいほど優先度が高くなりますが、時間フレームの注文の優先度、例えばidを決勝属性として使用できるように、決勝属性を導入する必要があります.ここで提供されるソリューションは、他のソリューションよりもずっと簡単で、実行速度が速い.
各従業員の最新の注文を3つ返します.
SELECT empid ,
orderid ,
custid ,
orderdate ,
requireddate
FROM sales.orders AS o1
WHERE orderid IN ( SELECT TOP 3
orderid
FROM sales.orders AS o2
WHERE o2.empid = o1.empid
ORDER BY orderdate DESC ,
orderid DESC )
APPLYで解決:
SELECT e.empid ,
a.orderid ,
a.custid ,
a.orderdate ,
a.requireddate
FROM hr.employees AS e
CROSS APPLY ( SELECT TOP 3
orderid ,
custid ,
orderdate ,
requireddate
FROM sales.orders AS o
WHERE o.empid = e.empid
ORDER BY orderdate DESC ,
orderid DESC
) AS a
まずemployeesをスキャンしてempidを取得し、各empid値に対してordersテーブルクエリに対して従業員の3つの最新注文を返します.ここでは、複数のプロパティを返すことができます.
もう1つの解決策は、特定の状況でAPPLY演算子を使用する方法よりも速く、ROW_を使用することです.NUMBER関数.まず、受注ごとに行番号を計算し、empidでパーティション化し、orderdate desc、orderid descの順にソートします.次に、外部クエリーでは、行番号が3以下の行のみがフィルタされます.
次のようになります.
SELECT orderid ,
custid ,
orderdate ,
requireddate
FROM ( SELECT orderid ,
custid ,
orderdate ,
requireddate ,
ROW_NUMBER() OVER ( PARTITION BY empid ORDER BY orderdate DESC , orderid DESC ) AS rownum
FROM sales.orders
) AS d
WHERE rownum 3
練習:
学生表から対応するクラスの上位num名の学生成績を選択する
--
/*
bj xh name cj
---------- ---- ---------- -----------
A006 A6 100
A005 A5 99
A001 A1 89
A002 A2 89
B001 B7 100
B001 B6 99
B001 B9 97
B001 B8 90
B001 B5 88
*/
--
declare @student table(
---
bj varchar(10),
--
xh char(4),
--
name varchar(10),
--
cj int)
--
declare @tj table(
---
bj varchar(10),
--
num int)
-- : num
set nocount on
--
insert @student select ' ' ,'A001','A1',89
insert @student select ' ' ,'A002','A2',89
insert @student select ' ' ,'A003','A3',59
insert @student select ' ' ,'A004','A4',80
insert @student select ' ' ,'A005','A5',99
insert @student select ' ' ,'A006','A6',100
insert @student select ' ' ,'A007','A7',82
insert @student select ' ' ,'B001','B1',19
insert @student select ' ' ,'B001','B2',81
insert @student select ' ' ,'B001','B3',69
insert @student select ' ' ,'B001','B4',86
insert @student select ' ' ,'B001','B5',88
insert @student select ' ' ,'B001','B6',99
insert @student select ' ' ,'B001','B7',100
insert @student select ' ' ,'B001','B8',90
insert @student select ' ' ,'B001','B9',97
insert @tj select ' ',3
insert @tj select ' ',5
参照SQL:
-- 2005.T-SQL
select t.bj,s.xh,s.name,s.cj
from @tj t
cross apply (
SELECT TOP(t.num)
with ties
-- with ties, 4 (2 )
xh,name,cj
from @student
where t.bj=bj
-- where inner join ; cross join
order by cj desc
)s
order by case when t.bj=' ' then 1 else 2 end asc,s.cj desc,s.xh asc
---
この文書は次のとおりです.http://blog.jobbole.com/94919/