表A aid
--------------------
1
2
3
4
表B aid bdate
--------------------
1 2009-6-1
1 2009-6-2
1 2009-6-3
2 2009-5-20
2 2009-5-25
2 2009-6-3
3 2009-4-1
3 2009-6-2
3 2009-6-4
4 2008-11-1
4 2009-4-15
4 2009-7-1 表A的 aid 对应表B的 bid
输入两个条件,条件一是一段时间,比如2009-6-1,2009-7-1;条件二是指定的数据范围,比如1
查询得到如下结果: aid count
------------------
1 3 也就是说,当数据范围指定为 aid=1 时有3条记录的日期在2009-6-1,2009-7-1之间。 PS:这里指定数据范围不一定是一个aid,也有可能是N个,比如aid有 1、101、10、102,如果我指定aid like '1%',就有4个数据符合这一条件,然后分别计算它们在一段时间内的条数。扩展条件:查询结果中count如果为 0 或 null,就不查询出来。
--------------------
1
2
3
4
表B aid bdate
--------------------
1 2009-6-1
1 2009-6-2
1 2009-6-3
2 2009-5-20
2 2009-5-25
2 2009-6-3
3 2009-4-1
3 2009-6-2
3 2009-6-4
4 2008-11-1
4 2009-4-15
4 2009-7-1 表A的 aid 对应表B的 bid
输入两个条件,条件一是一段时间,比如2009-6-1,2009-7-1;条件二是指定的数据范围,比如1
查询得到如下结果: aid count
------------------
1 3 也就是说,当数据范围指定为 aid=1 时有3条记录的日期在2009-6-1,2009-7-1之间。 PS:这里指定数据范围不一定是一个aid,也有可能是N个,比如aid有 1、101、10、102,如果我指定aid like '1%',就有4个数据符合这一条件,然后分别计算它们在一段时间内的条数。扩展条件:查询结果中count如果为 0 或 null,就不查询出来。
解决方案 »
- c#连接access,update and rename问题 (c#论坛没人睬),
- 关于周次的头痛的问题
- 如何將普通的資料表放在某个文件組下面?
- 求tsql语句:按班级分组查询出语数外三科平均成绩(分数为0的不作为分母的一部分)
- 怎么区分2NF和3NF 还有几个问题大家帮帮忙 谢了
- SQL Server 配置失败 不能完成安装
- 紧急求助SQL连接字符串的问题
- VC中怎么用“存储过程”来访问SQL server
- ACCESS 和SQL SERVER 的SQL语句有区别么??比如UPDATE这句话!!
- [求组]db2中的with ... as (...) 在mssql中应该如何表示
- 为什么不能在VFP中向SQL2000中创建数据表?
- 求SQL2005初級教程
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 23:38:56
---------------------------------
--> 生成测试数据表:bIf not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([aid] int,[bdate] Datetime)
Insert b
Select 1,'2009-6-1' union all
Select 1,'2009-6-2' union all
Select 1,'2009-6-3' union all
Select 2,'2009-5-20' union all
Select 2,'2009-5-25' union all
Select 2,'2009-6-3' union all
Select 3,'2009-4-1' union all
Select 3,'2009-6-2' union all
Select 3,'2009-6-4' union all
Select 4,'2008-11-1' union all
Select 4,'2009-4-15' union all
Select 4,'2009-7-1'
Go
--Select * from b-->SQL查询如下:
If not object_id('[sp_test]') is null
Drop proc [sp_test]
Go
Create proc sp_test
@bt varchar(10),
@et varchar(10),
@aid int
as
select aid,count(1) [count]
from b
where aid=@aid
and [bdate] between @bt and @et
group by aid
go-->调用查询exec sp_test '2009-6-1','2009-7-1',1-->结果
/*
aid count
----------- -----------
1 3(1 行受影响)
*/
Drop table [b]
Go
Create table [b]([aid] int,[bdate] Datetime)
Insert b
Select 1,'2009-6-1' union all
Select 1,'2009-6-2' union all
Select 1,'2009-6-3' union all
Select 2,'2009-5-20' union all
Select 2,'2009-5-25' union all
Select 2,'2009-6-3' union all
Select 3,'2009-4-1' union all
Select 3,'2009-6-2' union all
Select 3,'2009-6-4' union all
Select 4,'2008-11-1' union all
Select 4,'2009-4-15' union all
Select 4,'2009-7-1'
Go
declare @s varchar(100),@begintime datetime,@endtime datetime
set @s='1,2,3,4' set @begintime='2009-5-1' set @endtime='2009-12-11'
if(@begintime<@endtime)
begin
select aid,
[count]=COUNT(*)
from b
where charindex(','+convert(varchar,aid)+',',','+@s+',')>0 and bdate>=@begintime and bdate<=@endtime
group by aid
end
/*
1 3
2 3
3 2
4 1
*/
Drop table [b]
Go
Create table [b]([aid] int,[bdate] Datetime)
Insert b
Select 1,'2009-6-1' union all
Select 1,'2009-6-2' union all
Select 1,'2009-6-3' union all
Select 2,'2009-5-20' union all
Select 2,'2009-5-25' union all
Select 2,'2009-6-3' union all
Select 3,'2009-4-1' union all
Select 3,'2009-6-2' union all
Select 3,'2009-6-4' union all
Select 4,'2008-11-1' union all
Select 4,'2009-4-15' union all
Select 4,'2009-7-1'
Go
declare @s varchar(100),@begintime datetime,@endtime datetime
set @s='1,2,4' --aid的条件
set @begintime='2009-6-6' set @endtime='2009-10-11'--时间的范围
if(@begintime<@endtime)
begin
select aid,
[count]=COUNT(*)
from b
where charindex(','+convert(varchar,aid)+',',','+@s+',')>0 and bdate>=@begintime and bdate<=@endtime
group by aid
end
/*
4 1
*/
select a.aid,isnull(b.fc,0) fc
from (select aid from a where aid like '1%') a
left join (
select aid,count(*) fc
from b
group by aid
where aid like '1%' and bdate bewteen '2009-1-1','2009-7-1'
) b on a.aid=b.aid
因为你下面表 上面的AID都有 所以上面没用 这里用了 你看下
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(aid int )
go
insert into tb
select 1 union all
select 2 union all
select 3
go
If not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([aid] int,[bdate] Datetime)
Insert b
Select 1,'2009-6-1' union all
Select 1,'2009-6-2' union all
Select 1,'2009-6-3' union all
Select 2,'2009-5-20' union all
Select 2,'2009-5-25' union all
Select 2,'2009-6-3' union all
Select 3,'2009-4-1' union all
Select 3,'2009-6-2' union all
Select 3,'2009-6-4' union all
Select 4,'2008-11-1' union all
Select 4,'2009-4-15' union all
Select 4,'2009-7-1'
Go
declare @s varchar(100),@begintime datetime,@endtime datetime
set @s='1,2,4' set @begintime='2009-4-6' set @endtime='2009-12-11'
if(@begintime<@endtime)
begin
select b.aid,
[count]=COUNT(*)
from tb join b on tb.aid=b.aid --这里是关联了TB表的
where charindex(','+convert(varchar,b.aid)+',',','+@s+',')>0 and bdate>=@begintime and bdate<=@endtime
group by b.aid
end/*------------
1 3
2 3
-------*/