我在SQL Server数据库中建了table1和table2 table1表记录的是一些顾客去酒店的时间,名字,次数,table2 表记录的是一些顾客去酒店吃饭的时间和顾客名字表:table1
id1 data Name1 visit
1 2006-4-24 zhang 1
2 2006-4-25 zhang 3
3 2006-4-25 wang 2
4 2006-4-26 wang 1
5 2006-4-25 zhang 2
6 2006-4-26
7 2006-4-26 liu 9
8 2006-4-27 wang 3
9 2006-4-27 zhang 2
10 2006-4-26 liu 1表:table2
id2 datatime Name2
1 2006-4-25 12:19:39 zhang
2 2006-4-25 19:19:39 zhang
3 2006-4-25 18:19:39 wang
4 2006-4-26 20:19:39 wang
5 2006-4-27 17:19:39 zhang 因为顾客去酒店后不一定会吃饭,所以table1 的记录条数会多于表table2的记录数我想从这两表中统计出从20060501到20060514这段时间内所有人去该酒店的次数及吃饭的次数 ,得到的结果绑定到datagrid
显示的结果如下:
Name 去的次数(a) 吃饭的次数(b)
liu 10 0
wang 6 2
zhang 8 3请问我给统计的结果绑定到datagrid如上表所示, 该SQL语句怎么写? 请能用代码详细的帮我一下,谢谢! 我在线等待,有不明白题干的,欢迎来问,谢谢!!!
id1 data Name1 visit
1 2006-4-24 zhang 1
2 2006-4-25 zhang 3
3 2006-4-25 wang 2
4 2006-4-26 wang 1
5 2006-4-25 zhang 2
6 2006-4-26
7 2006-4-26 liu 9
8 2006-4-27 wang 3
9 2006-4-27 zhang 2
10 2006-4-26 liu 1表:table2
id2 datatime Name2
1 2006-4-25 12:19:39 zhang
2 2006-4-25 19:19:39 zhang
3 2006-4-25 18:19:39 wang
4 2006-4-26 20:19:39 wang
5 2006-4-27 17:19:39 zhang 因为顾客去酒店后不一定会吃饭,所以table1 的记录条数会多于表table2的记录数我想从这两表中统计出从20060501到20060514这段时间内所有人去该酒店的次数及吃饭的次数 ,得到的结果绑定到datagrid
显示的结果如下:
Name 去的次数(a) 吃饭的次数(b)
liu 10 0
wang 6 2
zhang 8 3请问我给统计的结果绑定到datagrid如上表所示, 该SQL语句怎么写? 请能用代码详细的帮我一下,谢谢! 我在线等待,有不明白题干的,欢迎来问,谢谢!!!
1. select name1,sum(visit) from table1 where date>20060501 and date<20060514 group by name1
2. select name2,count(*) from table2 where datetime>20060501 and datetime<20060514 group by name2
insert table1 select 1,'2006-4-24','zhang',1
union all select 2,'2006-4-25','zhang',3
union all select 3,'2006-4-25','wang',2
union all select 4,'2006-4-26','wang',1
union all select 5,'2006-4-25','zhang',2
union all select 6,'2006-4-26','',0
union all select 7,'2006-4-26','liu',9
union all select 8,'2006-4-27','wang',3
union all select 9,'2006-4-27','zhang',2
union all select 10,'2006-4-26','liu',1
create table table2(id2 int,datatime datetime,Name2 varchar(10))
insert table2 select 1,'2006-4-25 12:19:39','zhang'
union all select 2,'2006-4-25 19:19:39','zhang'
union all select 3,'2006-4-25 18:19:39','wang'
union all select 4,'2006-4-26 20:19:39','wang'
union all select 5,'2006-4-27 17:19:39','zhang'
select t.*,count(name2) as 吃饭的次数 from
(select name=name1,sum(visit) as 去的次数 from table1 group by name1) t
left join table2 x on name=name2 group by name,去的次数
---
create table tbl1(id1 int identity(1,1),data nvarchar(15),name1 nvarchar(10),visit int)
insert into tbl1 select '2006-4-24','zhang',1
union all select '2006-4-25','zhang',3
union all select '2006-4-25','wang',2
union all select '2006-4-26','wang',1
union all select '2006-4-25','zhang',2
union all select '2006-4-26',NULL,NULL
union all select '2006-4-26','liu',9
union all select '2006-4-27','wang',3
union all select '2006-4-27','zang',2
union all select '2006-4-26','liu',1create table tbl2(id2 int identity(1,1),[datetime] datetime,name2 nvarchar(10))
insert into tbl2 select '2006-4-25 12:19:39 ','zhang'
union all select '2006-4-25 19:19:39','zhang'
union all select '2006-4-25 18:19:39 ','wang'
union all select '2006-4-26 20:19:39','wang'
union all select '2006-4-27 17:19:39','zhang'
------------
declare @t1 table(name nvarchar(10),visit int)
declare @t2 table(name nvarchar(10),visit int)
insert into @t1
select name1,sum(visit) as visit from tbl1 where (data>'2006-4-1' and data<'2006-4-28') and name1 is not null group by name1insert into @t2 select name2,count(name2) as visit from tbl2 where ([datetime]>'2006-4-1' and [datetime]<'2006-4-28') group by name2
select a.name, a.visit,b.visit from @t1 a
left outer join @t2 b on a.name=b.name
drop table tbl1,tbl2----------------------
liu 10 NULL
wang 6 2
zang 2 NULL
zhang 6 3
感觉你的结果不太正确
这句select t.*,count(name2) as 吃饭的次数 from
(select name=name1,sum(visit) as 去的次数 from table1 group by name1) t
left join table2 x on name=name2 group by name,去的次数应该改成
select t.*,count(name2) as 吃饭的次数 from
(select name=name1,sum(visit) as 去的次数 from table1 group by name1) t
left outer join table2 x on name=name2 group by name,去的次数
declare @t1 table(id2 int, datetime1 datetime , name2 varchar(10))
insert into @t
select 1, '2006-4-24', 'zhang', 1 union
select 2, '2006-4-25', 'zhang', 3 union
select 3, '2006-4-25', 'wang' ,2 union
select 4, '2006-4-26', 'wang' ,1 union
select 5, '2006-4-25', 'zhang', 2 union
--select 6, '2006-4-26', ' ',
select 7, '2006-4-26', 'liu' ,9 union
select 8, '2006-4-27', 'wang' ,3 union
select 9, '2006-4-27', 'zhang', 2 union
select 10,'2006-4-26', 'liu', 1insert into @t1
select 1, '2006-4-25 12:19:39', 'zhang' union
select 2, '2006-4-25 19:19:39', 'zhang' union
select 3, '2006-4-25 18:19:39', 'wang' union
select 4, '2006-4-26 20:19:39', 'wang' union
select 5, '2006-4-27 17:19:39', 'zhang'select a.name1 ,
isnull(sum(visit),0),
(
select isnull(count(*),0) from @t1 b where a.name1 = b.name2 and b.datetime1 between '20060501' and '20060514'
)
from @t a
where a.date1 between '20060501' and '20060514'
group by a.name1
得不到你那结果阿!create table table1(id1 int,data datetime,Name1 varchar(10),visit int)
insert table1 select 1,'2006-4-24','zhang',1
union all select 2,'2006-4-25','zhang',3
union all select 3,'2006-4-25','wang',2
union all select 4,'2006-4-26','wang',1
union all select 5,'2006-4-25','zhang',2
union all select 6,'2006-4-26','',0
union all select 7,'2006-4-26','liu',9
union all select 8,'2006-4-27','wang',3
union all select 9,'2006-4-27','zhang',2
union all select 10,'2006-4-26','liu',1
create table table2(id2 int,datatime datetime,Name2 varchar(10))
insert table2 select 1,'2006-4-25 12:19:39','zhang'
union all select 2,'2006-4-25 19:19:39','zhang'
union all select 3,'2006-4-25 18:19:39','wang'
union all select 4,'2006-4-26 20:19:39','wang'
union all select 5,'2006-4-27 17:19:39','zhang'
select T2.Name1,T2.visit,T1.num
from
(select Name2,count(1) as num
from table2
group by Name2) T1 right join(select Name1,sum(visit) as visit
from (select data,Name1,sum(visit) as visit from table1 where Name1<>'' group by data,Name1) T
group by Name1) T2 on T1.name2=T2.Name1
条件你自己加一下吧!!