表1:(主键是id )id datatime visitname
1 2006-4-21 8:49:39 XiaoLi
2 2006-4-22 18:19:59 XiaoLi
3 2006-4-22 16:51:32 XiaoLi
4 2006-4-22 8:19:19 XiaoLi
5 2006-4-22 14:29:37 XiaoLi表2: (主键是id ,data)
id data count visitname(访问名)
1 2006-4-20 4 XiaoLi
2 2006-4-21 6 XiaoLi
3 2006-4-22 4 XiaoLi
4 2006-4-23 8 XiaoLi
5 2006-4-24 0 XiaoLi
6 2006-4-20 6 XiaoHong
7 2006-4-21 4 XiaoHong
8 2006-4-22 6 XiaoHong
9 2006-4-23 0 XiaoHong
10 2006-4-24 0 XiaoHong
上序两张表,我想读取名为XiaoLi 从 2006-4-20 到 2006-4-23 这四天的数据信息绑定到datagrid,请问怎么实现
要求在datagrid中显示3列,分别是: data、count、cishu(次数,每天对应表1的记录条数,取的是年月日,省去时分秒) 在datagrid中显示的结果是:
data count cishu(次数,每天对应表1的条数)
2006-4-20 4 0
2006-4-21 6 1
2006-4-22 4 4
2006-4-23 8 0
提示:可用表1的datatime与表2的data相关连来解决,请详细的用代码帮解决下,谢谢,特别是这个SQL语句怎么写才能梆定到datagrid?谢谢
用C# 和SQL Server实现
1 2006-4-21 8:49:39 XiaoLi
2 2006-4-22 18:19:59 XiaoLi
3 2006-4-22 16:51:32 XiaoLi
4 2006-4-22 8:19:19 XiaoLi
5 2006-4-22 14:29:37 XiaoLi表2: (主键是id ,data)
id data count visitname(访问名)
1 2006-4-20 4 XiaoLi
2 2006-4-21 6 XiaoLi
3 2006-4-22 4 XiaoLi
4 2006-4-23 8 XiaoLi
5 2006-4-24 0 XiaoLi
6 2006-4-20 6 XiaoHong
7 2006-4-21 4 XiaoHong
8 2006-4-22 6 XiaoHong
9 2006-4-23 0 XiaoHong
10 2006-4-24 0 XiaoHong
上序两张表,我想读取名为XiaoLi 从 2006-4-20 到 2006-4-23 这四天的数据信息绑定到datagrid,请问怎么实现
要求在datagrid中显示3列,分别是: data、count、cishu(次数,每天对应表1的记录条数,取的是年月日,省去时分秒) 在datagrid中显示的结果是:
data count cishu(次数,每天对应表1的条数)
2006-4-20 4 0
2006-4-21 6 1
2006-4-22 4 4
2006-4-23 8 0
提示:可用表1的datatime与表2的data相关连来解决,请详细的用代码帮解决下,谢谢,特别是这个SQL语句怎么写才能梆定到datagrid?谢谢
用C# 和SQL Server实现
大概是这样的:
select table2.data,count(table1.data)as cishu from table1,table2 where table2.data between '2006-4-20' and '2006-4-23' and table1.data=table2.data and table2.visitname='11' group by table2.data
这样好象只能检索出两条记录.剩余的你自己想想看吧.
--表1
create table aa(
id int not null,
datatime datetime ,
visitname varchar(20))
insert into aaselect 1,'2006-4-21 8:49:39','XiaoLi'
union select 2,'2006-4-22 18:19:59','XiaoLi'
union select 3,'2006-4-22 16:51:32','XiaoLi'
union select 4,'2006-4-22 8:19:19','XiaoLi'
union select 5,'2006-4-22 14:29:37','XiaoLi'--表2
create table bb(
id int not null,
data datetime ,
count int,
visitname varchar(20))
insert into bbselect 1,'2006-4-20',4,'XiaoLi'
union select 2,'2006-4-21',6,'XiaoLi'
union select 3,'2006-4-22',4,'XiaoLi'
union select 4,'2006-4-23',8,'XiaoLi'
union select 5,'2006-4-24',0,'XiaoLi'
union select 6,'2006-4-20',6,'XiaoHong'
union select 7,'2006-4-21',4,'XiaoHong'
union select 8,'2006-4-22',6,'XiaoHong'
union select 9,'2006-4-23',0,'XiaoHong'
union select 10,'2006-4-24',0,'XiaoHong'
--查询
select a.[data],a.[count],
(select count(1) from aa where datediff(dd,[datatime],a.[data])=0 and visitname='XiaoLi')cishu
from bb a where visitname='XiaoLi' and [data] between '2006-4-20' and'2006-4-23'
from table1,table2
where table1.names=table2.names and table1.data=table2.data
group by table2.data,table2.counts
order by table2.data
--表1
create table aa(
id int not null,
datetime datetime ,
visitname varchar(20))
insert into aaselect 1,'2006-4-21 8:49:39','XiaoLi'
union select 2,'2006-4-22 18:19:59','XiaoLi'
union select 3,'2006-4-22 16:51:32','XiaoLi'
union select 4,'2006-4-22 8:19:19','XiaoLi'
union select 5,'2006-4-22 14:29:37','XiaoLi'--表2
create table bb(
id int not null,
date datetime ,
count int,
visitname varchar(20))
insert into bbselect 1,'2006-4-20',4,'XiaoLi'
union select 2,'2006-4-21',6,'XiaoLi'
union select 3,'2006-4-22',4,'XiaoLi'
union select 4,'2006-4-23',8,'XiaoLi'
union select 5,'2006-4-24',0,'XiaoLi'
union select 6,'2006-4-20',6,'XiaoHong'
union select 7,'2006-4-21',4,'XiaoHong'
union select 8,'2006-4-22',6,'XiaoHong'
union select 9,'2006-4-23',0,'XiaoHong'
union select 10,'2006-4-24',0,'XiaoHong'
--查询
select a.[date],a.[count],
(select count(1) from aa where datediff(dd,[datetime],a.[date])=0 and visitname='XiaoLi')cishu
from bb a where visitname='XiaoLi' and [date] between '2006-4-20' and '2006-4-23'
select table2.data,table2.count,
(select count(*) from table1 where convert(datetime,datatime,120)=convert(datetime,table2.data,120)) as cishu
from table2
where data between '2006-4-20' and '2006-4-23'
from table1,table2
where table1.names=table2.names and table1.data=table2.data
group by table2.data,table2.counts
order by table2.data