--求助SQL问题--写对给分
表内容为:
编码 通用名                   订货时间
1 阿卡波糖片 2006-11-23  
1 阿卡波糖片 2006-11-23  
1 阿卡波糖片 2006-10-26  
3 头孢呋辛钠 2006-11-23  
3 头孢呋辛钠 2006-11-16  
3 头孢呋辛钠 2006-11-30  
4 头孢他啶                   2006-12-21  
4 头孢他啶                   2006-12-21  
5 左卡尼汀注射液 2006-10-19  
5 左卡尼汀注射液 2006-10-26  
5 左卡尼汀注射液 2006-11-2  
查询结果要求显示在某个时间段内某个药品超过两次以上.
为什么我写的这个语句不对.
select   *   from   a   where   编码   in   (select   编码   from   a   group   by   编码   having   count(编码)> 2)   and   订货时间   between   '2006-11-1 '   and   '2007-11-30 '
这个句查询结果是:
编码 通用名                   订货时间
1 阿卡波糖片 2006-11-23   11:27:00
1 阿卡波糖片 2006-11-23   11:27:00
3 注射用头孢呋辛钠 2006-11-23   11:28:00
3 注射用头孢呋辛钠 2006-11-16   15:52:00
3 注射用头孢呋辛钠 2006-11-30   16:34:00
5 左卡尼汀注射液 2006-11-02   15:47:00




------解决方案--------------------
select 编码 from a group by 编码 having count(编码)> 2
没有加入对日期段的判断
select 编码 from a group by 编码 having count(编码)> 2
where 订货时间 between '2006-11-1 ' and '2007-11-30 '
------解决方案--------------------
select * from (select * from tablename where 订货时间 between '2006-11-1 ' and '2007-11-30 ')a group by 编码 having count(*)> =2
------解决方案--------------------
select 编码 from a where 编码 in (select 编码 from a group by 编码 having count(编码)> 2) and 订货时间 between '2006-11-1 ' and '2007-11-30 '
group by 编码
------解决方案--------------------
create table T(编码 int, 通用名 nvarchar(20), 订货时间 datetime)
insert T select 1, '阿卡波糖片 ', '2006-11-23 '
union all select 1, '阿卡波糖片 ', '2006-11-23 '
union all select 1, '阿卡波糖片 ', '2006-10-26 '
union all select 3, '头孢呋辛钠 ', '2006-11-23 '
union all select 3, '头孢呋辛钠 ', '2006-11-16 '
union all select 3, '头孢呋辛钠 ', '2006-11-30 '
union all select 4, '头孢他啶 ', '2006-12-21 '
union all select 4, '头孢他啶 ', '2006-12-21 '
union all select 5, '左卡尼汀注射液 ', '2006-10-19 '
union all select 5, '左卡尼汀注射液 ', '2006-10-26 '
union all select 5, '左卡尼汀注射液 ', '2006-11-2 '

select * from T
where 编码 in
(select 编码 from T where 订货时间 between '2006-11-1 ' and '2007-11-30 ' group by 编码 having count(编码)> 2)

--result
编码 通用名 订货时间
----------- -------------------- ------------------------------------------------------
3 头孢呋辛钠 2006-11-23 00:00:00.000
3 头孢呋辛钠 2006-11-16 00:00:00.000
3 头孢呋辛钠 2006-11-30 00:00:00.000

(3 row(s) affected)
------解决方案--------------------
create table T(编码 int, 通用名 nvarchar(20), 订货时间 datetime)
insert T select 1, '阿卡波糖片 ', '2006-11-23 '
union all select 1, '阿卡波糖片 ', '2006-11-23 '
union all select 1, '阿卡波糖片 ', '2006-10-26 '