连接上一贴地址:http://topic.csdn.net/u/20090813/16/a57339a8-d268-4ee0-9956-2c815179d153.html
我写出了下面的sql。这里面有个效率问题,就是(select count(*) from t1 where t1.customer_id=t.customer_id) as ctnum ,因为需要这个,我来判断t_customer_id的order的数量值,好确定t_customer_id的个数有多少?select a.*,
case when num=1 and ctnum=1 then 1
when num=1 and ctnum>1 then 2
when num>1 and ctnum>1 then 3 else 4 end as flagNum,
case when num=1 and ctnum>1 then '+' else '' end as flagChar
from
(
select t.*,
(row_number() over (partition by t.customer_id order by t.customer_id,t.is_fee,t.is_extention,t.ownership )) as num,
(select count(*) from t1 where t1.customer_id=t.customer_id) as ctnum
from t1 t
)a这个红色的地方,大家有没有办法用别的方式替代掉啊,我感觉这里用这个很费时间。就是说只要判断出来t_customer_id的个数是否>1即可。
我写出了下面的sql。这里面有个效率问题,就是(select count(*) from t1 where t1.customer_id=t.customer_id) as ctnum ,因为需要这个,我来判断t_customer_id的order的数量值,好确定t_customer_id的个数有多少?select a.*,
case when num=1 and ctnum=1 then 1
when num=1 and ctnum>1 then 2
when num>1 and ctnum>1 then 3 else 4 end as flagNum,
case when num=1 and ctnum>1 then '+' else '' end as flagChar
from
(
select t.*,
(row_number() over (partition by t.customer_id order by t.customer_id,t.is_fee,t.is_extention,t.ownership )) as num,
(select count(*) from t1 where t1.customer_id=t.customer_id) as ctnum
from t1 t
)a这个红色的地方,大家有没有办法用别的方式替代掉啊,我感觉这里用这个很费时间。就是说只要判断出来t_customer_id的个数是否>1即可。
(row_number() over (partition by t.customer_id order by t.customer_id,t.is_fee,t.is_extention,t.ownership )) as num,
count(customer_id)over(partition by customer_id) as ctnum
from t1 t
case when num=1 then 1
when num=1 then 2
when num>1 then 3 else 4 end as flagNum,
case when num=1 then '+' else '' end as flagChar
from
(
select t.*,
(row_number() over (partition by t.customer_id order by t.customer_id,t.is_fee,t.is_extention,t.ownership )) as num,
from t1 t
)a