1、 select list_no,prod_no,substring(com_db_id,1,2) as cli_no,retail_price from ul_tmp_temp s where md_lx<>'非门店' and list_type='inv' and ul_tmp_temp.prod_no not like '3%' and not exists(select 1 from ul_tmp_temp where list_no=s..list_no and inv_date>t. inv_date) 2、 select * from ( select ul_tmp_temp.list_no,ul_tmp_temp.prod_no,substring(com_db_id,1,2) as cli_no,retail_price,rowid=row_number() over(partition by prod_no,substring(com_db_id,1,2) order by inv_date desc) as row_num from ul_tmp_temp where inv_date>=@begindate and inv_date<=@enddate and md_lx<>'非门店' and list_type='inv' and ul_tmp_temp.prod_no not like '3%') K where row_id=1
select * from (select ul_tmp_temp.list_no,ul_tmp_temp.prod_no,substring(com_db_id,1,2) as cli_no,retail_price, row_number() over(partition by prod_no,substring(com_db_id,1,2) order by list_no desc) as row_num from ul_tmp_temp where inv_date>=@begindate and inv_date<=@enddate and md_lx<>'非门店' and list_type='inv' and ul_tmp_temp.prod_no not like '3%') t where t.row_num=1
举个例子 inv_date=‘2014-04-28 12:00:00.000’ @enddate = ‘2014-04-28’ =‘2014-04-28 00:00:00.000’
inv_date<=@enddate false
改成 convert(varchar(100),inv_date,120)<=@enddate
或 inv_date<=dateadd(day,@enddate,1)
select list_no,prod_no,substring(com_db_id,1,2) as cli_no,retail_price
from ul_tmp_temp s
where md_lx<>'非门店' and list_type='inv' and ul_tmp_temp.prod_no not like '3%'
and not exists(select 1 from ul_tmp_temp where list_no=s..list_no and inv_date>t. inv_date)
2、
select * from
(
select ul_tmp_temp.list_no,ul_tmp_temp.prod_no,substring(com_db_id,1,2) as cli_no,retail_price,rowid=row_number() over(partition by prod_no,substring(com_db_id,1,2) order by inv_date desc) as row_num from ul_tmp_temp where inv_date>=@begindate and inv_date<=@enddate and md_lx<>'非门店' and list_type='inv' and ul_tmp_temp.prod_no not like '3%') K
where row_id=1
select * from
(select ul_tmp_temp.list_no,ul_tmp_temp.prod_no,substring(com_db_id,1,2) as cli_no,retail_price,
row_number() over(partition by prod_no,substring(com_db_id,1,2) order by list_no desc) as row_num
from ul_tmp_temp
where inv_date>=@begindate and inv_date<=@enddate
and md_lx<>'非门店' and list_type='inv' and ul_tmp_temp.prod_no not like '3%') t
where t.row_num=1