WHERE子句基于用户定义

Question

我有一个变量date_order，我在SELECT子句中定义它

CCD_ 2。

我希望仅将观察结果纳入date_order=1。

但是，如果在下面的语句and date_order=1中的WHERE子句中包含条件date_order=1，则会得到一个错误。我在这方面的问题是：

1. 是否可以在SELECT子句中定义的date_order上设置条件？如果是，我该如何正确地执行此操作
2. 如果不可能，你有没有建议我应该如何进行

我使用以下代码：

select to_char(a.from_date, 'dd-mm-yyyy') as fromdate, a.mother, a.mother_NAME,a.mother_GROUP, a.child_WEIGHT,
a.NODE_NO, a.NODE_NO_PARENT,
rank() over (partition by a.mother order by to_char(a.from_date, 'dd-mm-yyyy') desc) as date_order
,c.child_WEIGHT as PARENT_child_WEIGHT
from table_a a
left join table_b b
on a.mother_ref=b.mot_ref
left join table_c c
on a.NODE_NO_PARENT=c.NODE_NO and a.mother=c.mother
where 1=1
and a. NODE_TYPE in ('Model mother') 
and  a.child_WEIGHT <> 0
and  c.child_WEIGHT <> 0 
;

提前谢谢。如果我对SQL语法的使用有误，请随时纠正我，但请记住，这只是我第三次使用SQL。

致问候，

Answer 1

尝试使用select而非select。

select * from (
select to_char(a.from_date, 'dd-mm-yyyy') as fromdate, a.mother, a.mother_NAME,a.mother_GROUP, a.child_WEIGHT,
a.NODE_NO, a.NODE_NO_PARENT,
rank() over (partition by a.mother order by to_char(a.from_date, 'dd-mm-yyyy') desc) as date_order
,c.child_WEIGHT as PARENT_child_WEIGHT
from table_a a
left join table_b b
on a.mother_ref=b.mot_ref
left join table_c c
on a.NODE_NO_PARENT=c.NODE_NO and a.mother=c.mother
where 1=1
and a. NODE_TYPE in ('Model mother') 
and  a.child_WEIGHT <> 0
and  c.child_WEIGHT <> 0
)t
where t.date_order = 1

正如我所知，你不能在同一SELECT语句的WHERE块中使用别名(在你的情况下是date_order(

Answer 2

将其放入一些CTE中(请参阅https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html)，然后您将能够根据date_order列进行筛选。

它可能看起来像这样：

WITH cte as
(
select 
to_char(a.from_date, 'dd-mm-yyyy') as fromdate, a.mother, a.mother_NAME,a.mother_GROUP, a.child_WEIGHT,
a.NODE_NO, a.NODE_NO_PARENT,
rank() over (partition by a.mother order by to_char(a.from_date, 'dd-mm-yyyy') desc) as date_order
,c.child_WEIGHT as PARENT_child_WEIGHT
from table_a a
left join table_b b on a.mother_ref=b.mot_ref
left join table_c c on a.NODE_NO_PARENT=c.NODE_NO and a.mother=c.mother
where 1=1
and a. NODE_TYPE in ('Model mother') 
and a.child_WEIGHT <> 0
and c.child_WEIGHT <> 0 
)
select * from cte
where date_order = 1;