求一简单语句。
表tableA有数据如下：
日 期 商品编码 进价
2012-01-01 AA01 5
2012-02-05 AA03 4
2012-03-02 AA01 6
我要得出的数据是，某时间段范围内，相同品种出现不同进价的商品编码
2012-01-01 AA01 5
2012-03-02 AA01 6
[最优解释]
----------------------------
-- Author :TravyLee
-- Date :2012-11-05 16:12:01
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test](
[日期] datetime,[商品编码] varchar(10),[进价] int)
insert [test]
select '2012-01-01','AA01',5 union all
select '2012-02-05','AA03',4 union all
select '2012-03-02','AA01',6
--------------开始查询--------------------------
select
*
from
test a
where
exists(select 1 from test b where a.商品编码=b.商品编码 and a.进价<>b.进价)
--and自己加上时间范围
/*
日期商品编码进价
2012-01-01 00:00:00.000AA015
2012-03-02 00:00:00.000AA016
*/
SELECT *
FROM tableA a
WHERE EXISTS ( SELECT 1
FROM ( SELECT 商品编码 ,
进价
FROM tableA
GROUP BY 商品编码 ,
进价
HAVING COUNT(1) > 1
) b
WHERE a.商品编码 = b.商品编码
AND a.进价 = b.进价 )
select * from 表 where 商品编码 in(
select 商品编码 from 表
where 日期>'开始日期' and 日期<'结束日期'
group by 商品编码
having count(*)>1
)
