促销代码 款号 条形码 原价钱 促销价钱 促销开始日期促销结束日期
061108WSF 82201267 822012670102 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670103 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670402 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670403 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670502 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670503 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670602 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670603 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671102 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671103 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671802 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671803 139 79 2006-11-9 2007-3-31
070126WSF 82201267 822012670102 139 49 2007-1-26 2007-6-30
070110WSF 82201267 822012670102 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670103 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670402 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670403 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670502 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670503 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670602 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670603 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671102 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671103 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671802 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671803 139 59 2007-1-10 2007-6-30
070126WSF 82201267 822012670103 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670402 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670403 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670502 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670503 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670602 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670603 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671102 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671103 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671802 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671803 139 49 2007-1-26 2007-6-30
070206WSF 82201267 822012670102 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670103 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670402 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670403 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670502 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670503 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670602 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670603 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671102 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671103 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671802 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671803 139 39 2007-2-6 2007-9-30
070215WSF 82201267 822012670102 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670103 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670402 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670403 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670502 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670503 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670602 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670603 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671102 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671103 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671802 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671803 139 29 2007-2-15 2007-9-30
请问用SQL怎样实现查询最后促销的价钱
例如上面的数据,我想查找出82201267 这个款号最后促销价钱为29??急
061108WSF 82201267 822012670102 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670103 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670402 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670403 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670502 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670503 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670602 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670603 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671102 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671103 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671802 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671803 139 79 2006-11-9 2007-3-31
070126WSF 82201267 822012670102 139 49 2007-1-26 2007-6-30
070110WSF 82201267 822012670102 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670103 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670402 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670403 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670502 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670503 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670602 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670603 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671102 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671103 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671802 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671803 139 59 2007-1-10 2007-6-30
070126WSF 82201267 822012670103 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670402 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670403 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670502 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670503 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670602 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670603 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671102 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671103 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671802 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671803 139 49 2007-1-26 2007-6-30
070206WSF 82201267 822012670102 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670103 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670402 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670403 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670502 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670503 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670602 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670603 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671102 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671103 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671802 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671803 139 39 2007-2-6 2007-9-30
070215WSF 82201267 822012670102 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670103 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670402 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670403 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670502 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670503 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670602 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670603 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671102 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671103 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671802 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671803 139 29 2007-2-15 2007-9-30
请问用SQL怎样实现查询最后促销的价钱
例如上面的数据,我想查找出82201267 这个款号最后促销价钱为29??急
not exists
(select 1 from tb where 款号 = a.款号 and 促销结束日期<a.促销结束日期)
款号='82201267'
and
not exists
(select 1 from tb where 款号 = a.款号 and 促销结束日期<a.促销结束日期)
你数据格式是不是excel?
不然我不好调试
82201267 39
82201267 39
82201267 39
82201267 39
82201267 39
82201267 39
82201267 39
82201267 39
82201267 39
82201267 39
82201267 39
82201267 29
82201267 29
82201267 29
82201267 29
82201267 29
82201267 29
82201267 29
82201267 29
82201267 29
82201267 29
82201267 29
82201267 29
但是我想的结果是82201267 29
麻烦大家给我想想办法源数据我再贴一下
np_code code barcode y_price h_price begtime endtime
061108WSF 82201267 822012670102 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670103 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670402 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670403 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670502 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670503 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670602 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012670603 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671102 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671103 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671802 139 79 2006-11-9 2007-3-31
061108WSF 82201267 822012671803 139 79 2006-11-9 2007-3-31
070126WSF 82201267 822012670102 139 49 2007-1-26 2007-6-30
070110WSF 82201267 822012670102 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670103 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670402 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670403 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670502 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670503 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670602 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012670603 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671102 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671103 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671802 139 59 2007-1-10 2007-6-30
070110WSF 82201267 822012671803 139 59 2007-1-10 2007-6-30
070126WSF 82201267 822012670103 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670402 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670403 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670502 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670503 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670602 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012670603 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671102 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671103 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671802 139 49 2007-1-26 2007-6-30
070126WSF 82201267 822012671803 139 49 2007-1-26 2007-6-30
070206WSF 82201267 822012670102 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670103 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670402 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670403 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670502 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670503 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670602 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012670603 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671102 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671103 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671802 139 39 2007-2-6 2007-9-30
070206WSF 82201267 822012671803 139 39 2007-2-6 2007-9-30
070215WSF 82201267 822012670102 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670103 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670402 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670403 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670502 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670503 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670602 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012670603 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671102 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671103 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671802 139 29 2007-2-15 2007-9-30
070215WSF 82201267 822012671803 139 29 2007-2-15 2007-9-30
贴子转到这里来,因为上图数据没有空格
FROM test L JOIN test R
ON L.barcode = R.barcode
GROUP BY L.Code, L.h_price,l.endtime
HAVING L.endtime = max(R.endtime)结果
Code h_price endtime
---------- ----------- ------------------------------------------------------
82201267 29 2007-09-30 00:00:00.000
82201267 39 2007-09-30 00:00:00.000(所影响的行数为 2 行)
相同的原因是因为你这两条数据时间完全一样..
FROM test L JOIN test R
ON L.barcode = R.barcode
GROUP BY L.Code, L.h_price,l.begtime,l.endtime
HAVING L.endtime = max(R.endtime)
and l.begtime = max(r.begtime)结果
Code h_price begtime endtime
---------- ----------- ------------------------------------------------------ ------------------------------------------------------
82201267 29 2007-02-15 00:00:00.000 2007-09-30 00:00:00.000(所影响的行数为 1 行)
select np_itcode_it,max(np_sourceprc) as y_price,max(np_stdprc) as h_price ,max(np_enddate) as end_time from arz ,
(select np_itcode_it as aa ,max(np_begdate) as bb from arz group by np_itcode_it) ab where
np_begdate =bb and np_itcode_it=aa group by np_itcode_it