1.有一表T1(ID,A,B),求一个语句得到如下对应结果(ID,C),
对应的含义为:有一个房间,T1(ID,A,B)表记录房间的每个时段进出情况,A:进,B:出,C:是室内剩余,怎么样用select语句求出C?T1: 结果:
ID A B ID C
1 10 0 1 10
2 20 10 2 20
3 20 10 3 30
4 30 20 4 40
5 30 30 5 40
6 20 30 6 30
7 10 40 7 0
2.有一进场表:T2(ID,A,D) ID:流水号 A:入场号 D:时间 如下表
怎样用select求出到某个时段为止一共进入了多少人? 得出结果如T3显示:
T2:
ID A D
1 A001 2006-12-5 9:02:23
2 A002 2006-12-5 9:12:23
3 A003 2006-12-5 10:03:23
4 A004 2006-12-5 10:42:23
5 A005 2006-12-5 10:52:23
6 A006 2006-12-5 11:02:23
7 A007 2006-12-5 12:02:23
8 A008 2006-12-5 13:02:23
9 A009 2006-12-5 14:02:23
10 A010 2006-12-5 14:12:23
11 A011 2006-12-5 15:02:23
12 A012 2006-12-5 16:02:23
13 A013 2006-12-5 17:02:23
14 A014 2006-12-5 17:22:23
结查:T3
时段(小时) 已入场人数
9 2
10 5
11 6
12 7
13 8
14 10
15 11
16 12
17 14
对应的含义为:有一个房间,T1(ID,A,B)表记录房间的每个时段进出情况,A:进,B:出,C:是室内剩余,怎么样用select语句求出C?T1: 结果:
ID A B ID C
1 10 0 1 10
2 20 10 2 20
3 20 10 3 30
4 30 20 4 40
5 30 30 5 40
6 20 30 6 30
7 10 40 7 0
2.有一进场表:T2(ID,A,D) ID:流水号 A:入场号 D:时间 如下表
怎样用select求出到某个时段为止一共进入了多少人? 得出结果如T3显示:
T2:
ID A D
1 A001 2006-12-5 9:02:23
2 A002 2006-12-5 9:12:23
3 A003 2006-12-5 10:03:23
4 A004 2006-12-5 10:42:23
5 A005 2006-12-5 10:52:23
6 A006 2006-12-5 11:02:23
7 A007 2006-12-5 12:02:23
8 A008 2006-12-5 13:02:23
9 A009 2006-12-5 14:02:23
10 A010 2006-12-5 14:12:23
11 A011 2006-12-5 15:02:23
12 A012 2006-12-5 16:02:23
13 A013 2006-12-5 17:02:23
14 A014 2006-12-5 17:22:23
结查:T3
时段(小时) 已入场人数
9 2
10 5
11 6
12 7
13 8
14 10
15 11
16 12
17 14
解决方案 »
- 察看用户历史登陆信息
- SQL 字符串问题
- 考场分配问题2
- 请高手帮忙解决?
- 添加smalldatetime类型数据的问题
- CharINdex大问题,请教各位高手!!
- SQL 多表查询问题求大神指点
- 将一个表中记录迁到另一个表,如记录存在则更新,不存在则插入如何实现
- 如何将"2000-01-21T09:37:31"中的时间替换为"2004-02-20T09:37:31",date部分不同,time部分相同
- 这个SQL语句让我头痛了好久,本人水平实在有限,无法实现这个语句了,请高手帮忙,小弟在此感激不尽,谢谢!!!
- 数据库中判断email格式
- sql 2005的游标效率好像太低了,比2000慢了十倍
insert into @t1 select 1,10,0
insert into @t1 select 2,20,10
insert into @t1 select 3,20,10
insert into @t1 select 4,30,20
insert into @t1 select 5,30,30
insert into @t1 select 6,20,30
insert into @t1 select 7,10,40select
a.ID,sum(b.A-b.B)
from
@T1 a,@T1 b
where
a.ID>=b.ID
group by
a.ID
insert into @t1 select 1,10,0
insert into @t1 select 2,20,10
insert into @t1 select 3,20,10
insert into @t1 select 4,30,20
insert into @t1 select 5,30,30
insert into @t1 select 6,20,30
insert into @t1 select 7,10,40select
a.ID,sum(b.A-b.B) as C
from
@T1 a,@T1 b
where
a.ID>=b.ID
group by
a.ID/*
ID C
----------- -----------
1 10
2 20
3 30
4 40
5 40
6 30
7 0
*/
select id,c=(select sum(a-b) from 表 where id<=a.id)
from 表 a
from t1 a
from t1 a
-----------
改一下:
select ID ,A+ isnull((select sum(A)-sum(B) from t1 t where t.id <a.id ),0) as c
from t1 a
insert into @T2 select 1 ,'A001','2006-12-5 09:02:23'
insert into @T2 select 2 ,'A002','2006-12-5 09:12:23'
insert into @T2 select 3 ,'A003','2006-12-5 10:03:23'
insert into @T2 select 4 ,'A004','2006-12-5 10:42:23'
insert into @T2 select 5 ,'A005','2006-12-5 10:52:23'
insert into @T2 select 6 ,'A006','2006-12-5 11:02:23'
insert into @T2 select 7 ,'A007','2006-12-5 12:02:23'
insert into @T2 select 8 ,'A008','2006-12-5 13:02:23'
insert into @T2 select 9 ,'A009','2006-12-5 14:02:23'
insert into @T2 select 10,'A010','2006-12-5 14:12:23'
insert into @T2 select 11,'A011','2006-12-5 15:02:23'
insert into @T2 select 12,'A012','2006-12-5 16:02:23'
insert into @T2 select 13,'A013','2006-12-5 17:02:23'
insert into @T2 select 14,'A014','2006-12-5 17:22:23'
select
a.hh,count(b.ID) as [num]
from
(select distinct datepart(Hour,D) as hh from @T2) a,
@T2 b
where
a.hh>=datepart(Hour,b.D)
group by
a.hh/*hh num
----------- -----------
9 2
10 5
11 6
12 7
13 8
14 10
15 11
16 12
17 14
*/
---这个保存每天不同时段的,入场人数
select convert(varchar(13),cast(时段 as datetime ),120) as 时段
,count(*) as 已入场人数
from t2
group by convert(varchar(13),cast(时段 as datetime ),120
--生成测试数据
declare @d table(id int,a int,b int)
insert into @d select 1, 10, 0
insert into @d select 2, 20, 10
insert into @d select 3, 20, 10
insert into @d select 4, 30, 20
insert into @d select 5, 30, 30
insert into @d select 6, 20, 30
insert into @d select 7, 10, 40
--解决方法
select id,(select sum(a-b) from @d a where a.id<=b.id) from @d b第二个问题
--生成测试数据
declare @d1 table(id int,a varchar(10),D datetime)
insert into @d1 select 1, 'A001', '2006-12-5 9:02:23'
insert into @d1 select 2, 'A002', '2006-12-5 9:12:23'
insert into @d1 select 3, 'A003', '2006-12-5 10:03:23'
insert into @d1 select 4, 'A004', '2006-12-5 10:42:23'
insert into @d1 select 5, 'A005', '2006-12-5 10:52:23'
insert into @d1 select 6, 'A006', '2006-12-5 11:02:23'
insert into @d1 select 7, 'A007', '2006-12-5 12:02:23'
insert into @d1 select 8, 'A008', '2006-12-5 13:02:23'
insert into @d1 select 9, 'A009', '2006-12-5 14:02:23'
insert into @d1 select 10, 'A010', '2006-12-5 14:12:23'
insert into @d1 select 11, 'A011', '2006-12-5 15:02:23'
insert into @d1 select 12, 'A012', '2006-12-5 16:02:23'
insert into @d1 select 13, 'A013', '2006-12-5 17:02:23'
insert into @d1 select 14, 'A014', '2006-12-5 17:22:23'
--解决方法
select distinct datepart(hour,d),(select count(*) from @d1 a where datepart(hour,a.d)<=datepart(hour,b.d)) from @d1 b
insert into @t1 select 1,10,0
insert into @t1 select 2,20,40
insert into @t1 select 3,20,10
insert into @t1 select 4,30,20
insert into @t1 select 5,30,30
insert into @t1 select 6,20,30
insert into @t1 select 7,10,40select
a.ID,sum(b.A-b.B) as C
from
@T1 a,@T1 b
where
a.ID>=b.ID
group by
a.ID
这样的话就不对了
select cast(right(时段,2) as int) 时段 ,已入场人数
from
(
select convert(varchar(13),cast(时段 as datetime ),120) as 时段
,count(*) as 已入场人数
from t2
group by convert(varchar(13),cast(时段 as datetime ),120
) a
insert T1 select 1, 10, 0
union all select 2, 20, 10
union all select 3, 20, 10
union all select 4, 30, 20
union all select 5, 30, 30
union all select 6, 20, 30
union all select 7, 10, 40 select ID,
C=(select sum(A)-sum(B) from T1 where ID<=A.ID)
from T1 as A--result
ID C
----------- -----------
1 10
2 20
3 30
4 40
5 40
6 30
7 0
SELECT 日期=CONVERT(VARCHAR(10),D,120),[时段(小时)]=DATEPART(hour,D),
已入场人数=(SELECT SUM(1) FROM 表 WHERE CONVERT(VARCHAR(10),D,120)<=CONVERT(VARCHAR(10),A.D,120) AND DATEPART(hour,D)<=DATEPART(hour,A.D) )
FROM 表 a
GROUP BY CONVERT(VARCHAR(10),D,120),DATEPART(hour,D)
http://community.csdn.net/Expert/topic/5205/5205301.xml?temp=.5687372
insert T2 select 1, 'A001', '2006-12-5 9:02:23'
union all select 2, 'A002', '2006-12-5 9:12:23'
union all select 3, 'A003', '2006-12-5 10:03:23'
union all select 4, 'A004', '2006-12-5 10:42:23'
union all select 5, 'A005', '2006-12-5 10:52:23'
union all select 6, 'A006', '2006-12-5 11:02:23'
union all select 7, 'A007', '2006-12-5 12:02:23'
union all select 8, 'A008', '2006-12-5 13:02:23'
union all select 9, 'A009', '2006-12-5 14:02:23'
union all select 10, 'A010', '2006-12-5 14:12:23'
union all select 11, 'A011', '2006-12-5 15:02:23'
union all select 12, 'A012', '2006-12-5 16:02:23'
union all select 13, 'A013', '2006-12-5 17:02:23'
union all select 14, 'A014', '2006-12-5 17:22:23'select [时段(小时)]= datepart(hour, D), count(*) as 已入场人数
from T2
group by datepart(hour, D)--result
时段(小时) 已入场人数
----------- -----------
9 2
10 3
11 1
12 1
13 1
14 2
15 1
16 1
17 2(9 row(s) affected)