create table production_receive ( branchcode char(6), --分店 businessdate datetime, --送货日期 itemcode char(10), --货品编号 supplier_code char(6) --供应商编号)insert into production_receive select '6101' , '2012-05-20' , 'I10001' ,'s01' union select '6102' , '2012-05-20' , 'I10001' ,'s01' union select '6103' , '2012-05-20' , 'I10001' ,'s01' union select '6101' , '2012-05-20' , 'I20001' ,'s01' union select '6102' , '2012-05-20' , 'I20001' ,'s01' union select '6101' , '2012-05-20' , 'I10001' ,'s02' union select '6101' , '2012-05-20' , 'I40001' ,'s02' union select '6102' , '2012-05-20' , 'I10001' ,'s02' union select '6101' , '2012-05-21' , 'I10001' ,'s01' union select '6102' , '2012-05-21' , 'I10001' ,'s01' union select '6103' , '2012-05-21' , 'I10001' ,'s01' union select '6101' , '2012-05-21' , 'I20001' ,'s01' union select '6102' , '2012-05-21' , 'I20001' ,'s01' union select '6101' , '2012-05-21' , 'I10001' ,'s02' union select '6101' , '2012-05-21' , 'I40001' ,'s02' union select '6102' , '2012-05-21' , 'I10001' ,'s02'--首先对表production_receive的supplier_code字段上创建聚集索引 create clustered index supplier_code_index on production_receive(supplier_code) godeclare @supplier_code char(6) set @supplier_code='s01' --把子查询或取得数据放到表b select max(businessdate) mbd , max(itemcode) mic , supplier_code into #b from production_receive(nolock) where businessdate>= dateadd(dd,-30,getdate()) and (@supplier_code = '' or supplier_code = @supplier_code ) group by itemcode , supplier_code --然后再这样查询 select distinct a.supplier_code ,a.itemcode from production_receive a (nolock),b where a.businessdate = b.mbd and a.itemcode = b.mic and a.supplier_code = b.supplier_code
select max(businessdate) mbd , (itemcode) mic , supplier_code from production_receive(nolock) where businessdate>= getdate() - 30 and (@supplier_code = '' or supplier_code = @supplier_code ) group by itemcode , supplier_code 这样就是查询出来了supplier_code,itemcode 相同,businessdate 最大的。 哪还用再关联一次DISTINT?难道理解错了?
create table production_receive
(
branchcode char(6), --分店
businessdate datetime, --送货日期
itemcode char(10), --货品编号
supplier_code char(6) --供应商编号)insert into production_receive
select '6101' , '2012-05-20' , 'I10001' ,'s01'
union
select '6102' , '2012-05-20' , 'I10001' ,'s01'
union
select '6103' , '2012-05-20' , 'I10001' ,'s01'
union
select '6101' , '2012-05-20' , 'I20001' ,'s01'
union
select '6102' , '2012-05-20' , 'I20001' ,'s01'
union
select '6101' , '2012-05-20' , 'I10001' ,'s02'
union
select '6101' , '2012-05-20' , 'I40001' ,'s02'
union
select '6102' , '2012-05-20' , 'I10001' ,'s02'
union
select '6101' , '2012-05-21' , 'I10001' ,'s01'
union
select '6102' , '2012-05-21' , 'I10001' ,'s01'
union
select '6103' , '2012-05-21' , 'I10001' ,'s01'
union
select '6101' , '2012-05-21' , 'I20001' ,'s01'
union
select '6102' , '2012-05-21' , 'I20001' ,'s01'
union
select '6101' , '2012-05-21' , 'I10001' ,'s02'
union
select '6101' , '2012-05-21' , 'I40001' ,'s02'
union
select '6102' , '2012-05-21' , 'I10001' ,'s02'--首先对表production_receive的supplier_code字段上创建聚集索引
create clustered index supplier_code_index on production_receive(supplier_code)
godeclare @supplier_code char(6)
set @supplier_code='s01'
--把子查询或取得数据放到表b
select max(businessdate) mbd ,
max(itemcode) mic , supplier_code into #b
from production_receive(nolock) where businessdate>= dateadd(dd,-30,getdate())
and (@supplier_code = '' or supplier_code = @supplier_code )
group by itemcode , supplier_code
--然后再这样查询
select distinct
a.supplier_code ,a.itemcode
from
production_receive a (nolock),b
where a.businessdate = b.mbd
and a.itemcode = b.mic
and a.supplier_code = b.supplier_code
from production_receive(nolock) where businessdate>= getdate() - 30 and (@supplier_code = '' or supplier_code = @supplier_code )
group by itemcode , supplier_code
这样就是查询出来了supplier_code,itemcode 相同,businessdate 最大的。
哪还用再关联一次DISTINT?难道理解错了?
2012-05-21 00:00:00.000 I10001 s01
2012-05-21 00:00:00.000 I20001 s01 这个数据查出来的不就是每个供应商的不同产品吗?