我试图在下面的第一个查询中获取所有数据,但从下面的第二个查询中获取数据除外。
在这里我首先尝试通过使用with cte
和选择唯一数据/不同数据partition by
。
我尝试使用除外,但出现此错误:
关键字“ with”附近的语法不正确。如果此语句是公用表表达式,xmlnamespaces子句或更改跟踪上下文子句,则前一条语句必须以分号结尾。
First query:
With cte as
(
select
*,
row_number() over (partition by [Employee ID] order by [QTR] DESC, Wk desc) rownumber
from
tbl_HC
)
select *
from cte
where rownumber = 1
and QTR = (Select max(QTR) from tbl_HC)
Except
--2nd query
With cte as
(
select
*,
row_number() over (partition by [Employee ID] order by [QTR] DESC, Wk desc) rownumber
from
tbl_HC
)
select *
from cte
where rownumber = 1
and Wk= (
Select max(Wk) from tbl_HC
where QTR = (Select max(QTR) from tbl_HC))`
您的查询如下
With cte as
(select *, row_number()
over(partition by [Employee ID] order by [QTR] DESC,Wk DESC) rownumber
from tbl_HC
), cte1 as
(
select *, row_number()
over(partition by [Employee ID] order by [QTR] DESC,Wk DESC) rownumber
from tbl_HC
)
select * from cte
where rownumber =1
and QTR= (Select max(QTR) from tbl_SDS_Headcount_Manageby)
except
select * from cte1
where rownumber =1
and Wk= (
Select max(Wk) from tbl_HC
where QTR = (Select max(QTR) from tbl_HC))
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句