Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 запрос с условием на макс кол-во  [new]
verano naranjo
Member

Откуда: СПб
Сообщений: 223
Коллеги, туплю нереально. ошибка в условии, подскажите плз как правильно прописать:

запрос: 1. Создать хранимую процедуру, выводящую служащего, входящего в наибольшее количество отделов

if object_id ( 'dbo.proc_maxemp', 'p' ) is not null 
    drop procedure dbo.proc_maxemp
go
create procedure dbo.proc_maxemp
as
select name,surname,count(der.iid) from employees e
join dep_empl_rel der on der.employees_id=e.iid
group by e.name, e.surname
where max(count(der.iid))

exec dbo.proc_maxemp
29 дек 11, 13:45    [11845472]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
Glory
Member

Откуда:
Сообщений: 104751
не where max(count(der.iid))
а order by
29 дек 11, 13:51    [11845538]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
verano naranjo
Member

Откуда: СПб
Сообщений: 223
Glory, ORDER BY это же упорядочить, а мне надо не упорядочить, а выбрать
29 дек 11, 13:52    [11845552]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
verano naranjo,

как то так наверное
select top 1
e.employees_id,
P.C
from employees e
cross apply (
select C = count(*) from dep_empl_rel where employees_id = e.employees_id
)P 
order by C desc
29 дек 11, 13:55    [11845582]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
Miad
Member

Откуда:
Сообщений: 65
verano naranjo,

declare @iid int
set @iid=select max(cmax) from 
(select name,surname,count(der.iid) as cmax 
	from employees e
	join dep_empl_rel der on der.employees_id=e.iid
	group by e.name, e.surname) t1 

select name,surname,der.iid 
	from employees e
	join dep_empl_rel der on der.employees_id=e.iid
	where der.iid=@iid
29 дек 11, 14:00    [11845622]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
verano naranjo
Member

Откуда: СПб
Сообщений: 223
Miad,
да я так и думла, что придется писать в переменную.

Товарищи спасибо, если есть ещё варианты буду рада узнать
29 дек 11, 14:03    [11845633]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
hpv
Member

Откуда:
Сообщений: 153
verano naranjo,

select top 1 e.name, e.surname, count(der.iid) over(partition by e.iid) as Cnt
from employees as e
join dep_empl_rel as der on der.employees_id = e.iid
order by Cnt desc


Если нужно вывести всех таких служащих с "входящих в наибольшее количество отделов",
то вместо
top 1
подставить
distinct top 1 with ties
29 дек 11, 14:04    [11845645]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SELECT TOP(1) WITH TIES e.name,e.surname,[Count]=COUNT(*)OVER(PARTITION BY e.iid)
FROM employees e
JOIN dep_empl_rel der ON e.iid=der.employees_id
ORDER BY [Count] DESC;
???
29 дек 11, 14:07    [11845682]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
hpv,

уууу... Опередил!
29 дек 11, 14:08    [11845694]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
hpv
Member

Откуда:
Сообщений: 153
iap,

top 1 with ties без distinct будет выводить повторы)
29 дек 11, 14:11    [11845723]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Miad
verano naranjo,

declare @iid int
set @iid=select max(cmax) from 
(select name,surname,count(der.iid) as cmax 
	from employees e
	join dep_empl_rel der on der.employees_id=e.iid
	group by e.name, e.surname) t1 

select name,surname,der.iid 
	from employees e
	join dep_empl_rel der on der.employees_id=e.iid
	where der.iid=@iid


вот это изврат О_О
29 дек 11, 14:13    [11845739]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Народ, а чем cross apply не угодил то?
select top 1
e.iid,
name,surname,
P.C
from employees e
	 cross apply (select C = count(*) from dep_empl_rel where employees_id = e.iid)P 
order by P.C desc

А если несколько служащих занимают лидирующее место то надо WITH TIES подставить.
29 дек 11, 14:15    [11845751]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
hpv
Member

Откуда:
Сообщений: 153
gds,

Тоже хороший вариант))
29 дек 11, 14:22    [11845825]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
hpv
iap,

top 1 with ties без distinct будет выводить повторы)
Ну, значит добавить DISTINCT
29 дек 11, 14:27    [11845869]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
hpv
iap,

top 1 with ties без distinct будет выводить повторы)
Ну, значит добавить DISTINCT
Или превратить данный запрос в производную таблицу и подставить его в предикаты IN() или EXISTS() запроса из employees
29 дек 11, 14:31    [11845921]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
iap
пропущено...
Ну, значит добавить DISTINCT
Или превратить данный запрос в производную таблицу и подставить его в предикаты IN() или EXISTS() запроса из employees
Наверно, всё-таки, IN()
Забавно. Нечасто попадаются ситуации, когда IN() затруднительно заменить на аналогичный EXISTS()
29 дек 11, 14:34    [11845944]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
hpv
gds,

Тоже хороший вариант))

кстати посмотрел планы запросов ваш (72% total cost) и мой (28% total cost) в процентном соотношении.
Если интересно вот скрипты для подготовки и сами запросы.
+ sql

create table employees(
iid int not null,
name varchar(32) not null,
surname varchar(32) null)
GO
insert into employees(iid,name,surname)
values (1,'Vasya','Mihalkov'),(2,'Kiril','Matveev')
GO
create table dep_empl_rel(
dep_id int not null,
dep_name varchar(32),
employees_id int not null
)
GO

insert into dep_empl_rel (dep_id,dep_name,employees_id)
values (1,'IT',1),(1,'IT',2),(2,'Buh',2)
GO

select top 1 
e.iid,
name,surname,
P.C
from employees e
	 cross apply (select C = count(*) from dep_empl_rel where employees_id = e.iid)P 
order by P.C desc,e.iid
GO

select top 1 e.name, e.surname, count(der.employees_id) over(partition by e.iid) as Cnt from 
employees  e join dep_empl_rel as der on der.employees_id = e.iid
order by Cnt desc
GO



+ План cross apply


<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.2796.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0180331" StatementText="select top 1 e.iid, name,surname, P.C from employees e cross apply (select C = count(*) from dep_empl_rel where employees_id = e.iid)P order by P.C desc,e.iid " StatementType="SELECT" QueryHash="0x324CD5252D2A84AC" QueryPlanHash="0xC77A8F3D76DDA1A2">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" CachedPlanSize="16" CompileTime="7" CompileCPU="7" CompileMemory="192">
<RelOp AvgRowSize="53" EstimateCPU="0.000103173" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0180331">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1007" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Column="Expr1007" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="53" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0066687">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="CASE WHEN [Expr1007] IS NULL THEN (0) ELSE [Expr1007] END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="53" EstimateCPU="8.36E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0066685">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</OuterReferences>
<RelOp AvgRowSize="49" EstimateCPU="0.0001592" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" IndexKind="Heap" />
</TableScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="1.4E-06" EstimateIO="0" EstimateRebinds="1" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00337594">
<OutputList>
<ColumnReference Column="Expr1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1012],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1012" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="11" EstimateCPU="1.4E-06" EstimateIO="0" EstimateRebinds="1" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="5" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.00337594">
<OutputList>
<ColumnReference Column="Expr1012" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="0" ActualExecutions="2" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1012" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="11" EstimateCPU="8.29E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="1" EstimateRows="1.5" LogicalOp="Table Scan" NodeId="6" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0033693" TableCardinality="4">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="2" ActualExecutions="2" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" IndexKind="Heap" />
<Predicate>
<ScalarOperator ScalarString="[tempdb].[dbo].[dep_empl_rel].[employees_id]=[tempdb].[dbo].[employees].[iid] as [e].[iid]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Column="employees_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</TableScan>
</RelOp>
</StreamAggregate>
</RelOp>
</ComputeScalar>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</TopSort>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.047109" StatementText="select top 1 e.name, e.surname, count(der.employees_id) over(partition by e.iid) as Cnt from employees e join dep_empl_rel as der on der.employees_id = e.iid order by Cnt desc " StatementType="SELECT" QueryHash="0x956C442CD675F471" QueryPlanHash="0xA2E1FD0C665991B2">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" CachedPlanSize="24" CompileTime="5" CompileCPU="5" CompileMemory="136">
<RelOp AvgRowSize="49" EstimateCPU="0.000112529" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.047109">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1006" />
</OutputList>
<MemoryFractions Input="0.111111" Output="0.111111" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Column="Expr1006" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="49" EstimateCPU="7.21644E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0357352">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1006" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="53" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.66667" LogicalOp="Lazy Spool" NodeId="2" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0356602">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Spool>
<RelOp AvgRowSize="53" EstimateCPU="9.372E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Segment" NodeId="3" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="0.0356509">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
<ColumnReference Column="Segment1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Segment>
<GroupBy>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</GroupBy>
<SegmentColumn>
<ColumnReference Column="Segment1007" />
</SegmentColumn>
<RelOp AvgRowSize="53" EstimateCPU="0.00570372" EstimateIO="0.000626" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.0356415">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[tempdb].[dbo].[employees].[iid] as [e].[iid]=[tempdb].[dbo].[dep_empl_rel].[employees_id] as [der].[employees_id]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="11" EstimateCPU="0.000112491" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Sort" NodeId="5" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0146602">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<MemoryFractions Input="0.888889" Output="0.444444" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="11" EstimateCPU="0.0001614" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Table Scan" NodeId="6" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" TableReferenceId="-1" IndexKind="Heap" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="49" EstimateCPU="0.000103169" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Sort" NodeId="7" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0146486">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</OutputList>
<MemoryFractions Input="0.444444" Output="0.444444" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="49" EstimateCPU="0.0001592" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Table Scan" NodeId="8" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" TableReferenceId="-1" IndexKind="Heap" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</Segment>
</RelOp>
</Spool>
</RelOp>
<RelOp AvgRowSize="53" EstimateCPU="9.372E-07" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1.5" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.8744E-06">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1006" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="53" EstimateCPU="9.372E-08" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="11" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.03092E-06">
<OutputList>
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1006" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1008],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1008" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="53" EstimateCPU="9.372E-07" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="12" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="9.372E-07">
<OutputList>
<ColumnReference Column="Expr1008" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="53" EstimateCPU="0" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1.5" LogicalOp="Lazy Spool" NodeId="13" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="3" ActualRewinds="0" ActualRows="4" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<Spool PrimaryNodeId="2" />
</RelOp>
</StreamAggregate>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="53" EstimateCPU="0" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1.5" LogicalOp="Lazy Spool" NodeId="18" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="1" ActualRows="4" ActualEndOfScans="2" ActualExecutions="2" />
</RunTimeInformation>
<Spool PrimaryNodeId="2" />
</RelOp>
</NestedLoops>
</RelOp>
</NestedLoops>
</RelOp>
</TopSort>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

+ оконные функции

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.2796.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0180331" StatementText="select top 1 e.iid, name,surname, P.C from employees e cross apply (select C = count(*) from dep_empl_rel where employees_id = e.iid)P order by P.C desc,e.iid " StatementType="SELECT" QueryHash="0x324CD5252D2A84AC" QueryPlanHash="0xC77A8F3D76DDA1A2">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" CachedPlanSize="16" CompileTime="7" CompileCPU="7" CompileMemory="192">
<RelOp AvgRowSize="53" EstimateCPU="0.000103173" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0180331">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1007" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Column="Expr1007" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="53" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0066687">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="CASE WHEN [Expr1007] IS NULL THEN (0) ELSE [Expr1007] END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="53" EstimateCPU="8.36E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0066685">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</OuterReferences>
<RelOp AvgRowSize="49" EstimateCPU="0.0001592" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" IndexKind="Heap" />
</TableScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="1.4E-06" EstimateIO="0" EstimateRebinds="1" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00337594">
<OutputList>
<ColumnReference Column="Expr1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1012],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1012" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="11" EstimateCPU="1.4E-06" EstimateIO="0" EstimateRebinds="1" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="5" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.00337594">
<OutputList>
<ColumnReference Column="Expr1012" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="0" ActualExecutions="2" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1012" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="11" EstimateCPU="8.29E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="1" EstimateRows="1.5" LogicalOp="Table Scan" NodeId="6" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0033693" TableCardinality="4">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="2" ActualExecutions="2" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" IndexKind="Heap" />
<Predicate>
<ScalarOperator ScalarString="[tempdb].[dbo].[dep_empl_rel].[employees_id]=[tempdb].[dbo].[employees].[iid] as [e].[iid]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Column="employees_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</TableScan>
</RelOp>
</StreamAggregate>
</RelOp>
</ComputeScalar>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</TopSort>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.047109" StatementText="select top 1 e.name, e.surname, count(der.employees_id) over(partition by e.iid) as Cnt from employees e join dep_empl_rel as der on der.employees_id = e.iid order by Cnt desc " StatementType="SELECT" QueryHash="0x956C442CD675F471" QueryPlanHash="0xA2E1FD0C665991B2">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" CachedPlanSize="24" CompileTime="5" CompileCPU="5" CompileMemory="136">
<RelOp AvgRowSize="49" EstimateCPU="0.000112529" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.047109">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1006" />
</OutputList>
<MemoryFractions Input="0.111111" Output="0.111111" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Column="Expr1006" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="49" EstimateCPU="7.21644E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0357352">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1006" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="53" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.66667" LogicalOp="Lazy Spool" NodeId="2" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0356602">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Spool>
<RelOp AvgRowSize="53" EstimateCPU="9.372E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Segment" NodeId="3" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="0.0356509">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
<ColumnReference Column="Segment1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Segment>
<GroupBy>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</GroupBy>
<SegmentColumn>
<ColumnReference Column="Segment1007" />
</SegmentColumn>
<RelOp AvgRowSize="53" EstimateCPU="0.00570372" EstimateIO="0.000626" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.0356415">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[tempdb].[dbo].[employees].[iid] as [e].[iid]=[tempdb].[dbo].[dep_empl_rel].[employees_id] as [der].[employees_id]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="11" EstimateCPU="0.000112491" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Sort" NodeId="5" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0146602">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<MemoryFractions Input="0.888889" Output="0.444444" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="11" EstimateCPU="0.0001614" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Table Scan" NodeId="6" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" TableReferenceId="-1" IndexKind="Heap" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="49" EstimateCPU="0.000103169" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Sort" NodeId="7" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0146486">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</OutputList>
<MemoryFractions Input="0.444444" Output="0.444444" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="49" EstimateCPU="0.0001592" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Table Scan" NodeId="8" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" TableReferenceId="-1" IndexKind="Heap" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</Segment>
</RelOp>
</Spool>
</RelOp>
<RelOp AvgRowSize="53" EstimateCPU="9.372E-07" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1.5" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.8744E-06">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Column="Expr1006" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="53" EstimateCPU="9.372E-08" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="11" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.03092E-06">
<OutputList>
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1006" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1008],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1008" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="53" EstimateCPU="9.372E-07" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="12" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="9.372E-07">
<OutputList>
<ColumnReference Column="Expr1008" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="53" EstimateCPU="0" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1.5" LogicalOp="Lazy Spool" NodeId="13" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="3" ActualRewinds="0" ActualRows="4" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<Spool PrimaryNodeId="2" />
</RelOp>
</StreamAggregate>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="53" EstimateCPU="0" EstimateIO="0" EstimateRebinds="2.66667" EstimateRewinds="0" EstimateRows="1.5" LogicalOp="Lazy Spool" NodeId="18" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="iid" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="name" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[employees]" Alias="[e]" Column="surname" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[dep_empl_rel]" Alias="[der]" Column="employees_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="1" ActualRows="4" ActualEndOfScans="2" ActualExecutions="2" />
</RunTimeInformation>
<Spool PrimaryNodeId="2" />
</RelOp>
</NestedLoops>
</RelOp>
</NestedLoops>
</RelOp>
</TopSort>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
29 дек 11, 14:39    [11846003]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
verano naranjo
Member

Откуда: СПб
Сообщений: 223
gds,
большое спасибо))
29 дек 11, 14:51    [11846135]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
gds
кстати посмотрел планы запросов ваш (72% total cost) и мой (28% total cost) в процентном соотношении.

Если построить первичные ключи (кластерные)
1. на таблице employees поле iid
2. на таблице dep_empl_rel поле dep_id, employees_id
то отношение меняется 62%/38%. А вот если первичный ключ на таблице dep_empl_rel поле employees_id,dep_id то соотношение будет 50%/50%. Это конечно все хорошо, но данных уж больно мало. Все они расположены на 1 страницы. Можно для примера потестить в базе AdventureWorks, думаю найдется там похожая связка. Да и данных поболее.
29 дек 11, 14:56    [11846174]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
hpv
Member

Откуда:
Сообщений: 153
gds,

я думаю в каждом конкретном случае нужно смотреть планы и выбирать, что быстрее.
29 дек 11, 14:58    [11846198]     Ответить | Цитировать Сообщить модератору
 Re: запрос с условием на макс кол-во  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
hpv
gds,

я думаю в каждом конкретном случае нужно смотреть планы и выбирать, что быстрее.

+100500. Это точно. Ваша правда. ;)
29 дек 11, 15:00    [11846213]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить