表内容为:
编码 通用名 订货时间
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
编码 通用名 订货时间
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
where 订货时间 between '2006-11-1' and '2007-11-30'
group by 编码
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)
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)
and 订货时间 between '2006-11-1' and '2007-11-30'
--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)
select 编码 from T
where 订货时间 between '2006-11-1' and '2007-11-30'
group by 编码
having count(*)>2
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'
godeclare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2006-10-01'
set @dt2 = '2006-12-31'select * from t where 订货时间 >= @dt1 and 订货时间 <= @dt2 and 通用名 in
(
select 通用名 from t where 订货时间 >= @dt1 and 订货时间 <= @dt2 group by 通用名 having count(*) > 2
)drop table t
编码 通用名 订货时间
----------- -------------------- -----------------------
1 阿卡波糖片 2006-11-23 00:00:00.000
1 阿卡波糖片 2006-11-23 00:00:00.000
1 阿卡波糖片 2006-10-26 00:00:00.000
3 头孢呋辛钠 2006-11-23 00:00:00.000
3 头孢呋辛钠 2006-11-16 00:00:00.000
3 头孢呋辛钠 2006-11-30 00:00:00.000
5 左卡尼汀注射液 2006-10-19 00:00:00.000
5 左卡尼汀注射液 2006-10-26 00:00:00.000
5 左卡尼汀注射液 2006-11-02 00:00:00.000(所影响的行数为 9 行)