--求助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 '
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)
[解决办法]
--try:
select 编码 from T
where 订货时间 between '2006-11-1 ' and '2007-11-30 '
group by 编码
having count(*)> 2
[解决办法]
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 '
go
declare @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 行）
[解决办法]
select * from a where 编码 in (select 编码 from a group by 编码 having count(编码)> 2 and 订货时间 between '2006-11-1 ' and '2007-11-30 ') and 订货时间 between '2006-11-1 ' and '2007-11-30 '
