数据表 table_A
字段有 A_ID 、A_Name ,表结构如下:
A_ID A_Name
1 A
2 AA
3 AAA
4 AAAA
数据表 table_B
字段有 ID 、B_ID(B_ID与A_ID对应) 、B_Time、B_Num ,表结构如下:
ID B_ID B_Time B_Num
1 1 2010-05-25 1000
2 1 2010-06-25 1000
3 2 2010-05-25 2000
4 2 2010-06-25 2000
5 3 2010-05-25 3000
6 3 2010-06-25 3000
7 4 2010-05-25 4000
8 4 2010-06-25 4000想筛选出符合B_Time等于 2010-06-25 和对 A_ID 的 B_Num求和,想得到查询结果数据如下:A_ID A_Name B_Time B_Num Sum_B_Num
1 A 2010-06-25 1000 2000
2 AA 2010-06-25 2000 4000
3 AAA 2010-06-25 3000 6000
4 AAAA 2010-06-25 4000 8000用了查询语句如下,但得不出求和的结果,请大家帮忙指正,谢谢!
select A_ID, A_Name, B_Time, B_Num,sum(B_Num) as Sum_B_Num from table_A left join
table_B on B_ID= A_ID and B_Time=’ 2010-06-25 ’
字段有 A_ID 、A_Name ,表结构如下:
A_ID A_Name
1 A
2 AA
3 AAA
4 AAAA
数据表 table_B
字段有 ID 、B_ID(B_ID与A_ID对应) 、B_Time、B_Num ,表结构如下:
ID B_ID B_Time B_Num
1 1 2010-05-25 1000
2 1 2010-06-25 1000
3 2 2010-05-25 2000
4 2 2010-06-25 2000
5 3 2010-05-25 3000
6 3 2010-06-25 3000
7 4 2010-05-25 4000
8 4 2010-06-25 4000想筛选出符合B_Time等于 2010-06-25 和对 A_ID 的 B_Num求和,想得到查询结果数据如下:A_ID A_Name B_Time B_Num Sum_B_Num
1 A 2010-06-25 1000 2000
2 AA 2010-06-25 2000 4000
3 AAA 2010-06-25 3000 6000
4 AAAA 2010-06-25 4000 8000用了查询语句如下,但得不出求和的结果,请大家帮忙指正,谢谢!
select A_ID, A_Name, B_Time, B_Num,sum(B_Num) as Sum_B_Num from table_A left join
table_B on B_ID= A_ID and B_Time=’ 2010-06-25 ’
解决方案 »
- 帮忙写个sql!!!!
- 【All】求如何判断字段是否是外键?
- 存储过程 OUTPUT参数问题,请大哥们帮忙.
- 没写过存储过程,请教这样的asp程序如何写成sqlserver的存储过程
- 有没有(select field1 as fieldtemp from tablea).fieldtemp这样的句子
- 关于sqlserver数据备份的问题
- 一句SQL语句的写法,请指教!在线等待!
- 关于一个sql语句的问题
- 请教创建的视图里怎样使用ORDER BY排序?
- 在SQL server 数据库中的一个表的smalldatetime字段的默认值中用什么函数才能使一项纪录写入该表时该日期字段的值为写入时主机上的时间?给分--50
- 请给个随便的SQL语句
- SQL执行
from table_A left join
table_B on B_ID= A_ID and B_Time='2010-06-25'group by A_ID, A_Name, B_Time, B_Num
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(A_ID int, A_Name varchar(8))
insert into #a
select 1, 'A' union all
select 2, 'AA' union all
select 3, 'AAA' union all
select 4, 'AAAA'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(ID int, B_ID int, B_Time datetime, B_Num int)
insert into #b
select 1, 1, '2010-05-25', 1000 union all
select 2, 1, '2010-06-25', 1000 union all
select 3, 2, '2010-05-25', 2000 union all
select 4, 2, '2010-06-25', 2000 union all
select 5, 3, '2010-05-25', 3000 union all
select 6, 3, '2010-06-25', 3000 union all
select 7, 4, '2010-05-25', 4000 union all
select 8, 4, '2010-06-25', 4000select
a.A_ID, min(a.A_Name)A_Name, '2010-06-25' B_Time, min(B_Num) B_Num, sum(B_Num) Sum_B_Num
from
#a a inner join #b b on a.A_ID = b.B_ID
where
b.B_Time = '2010-06-25'
group by
a.A_ID/*
A_ID A_Name B_Time B_Num Sum_B_Num
----------- -------- ---------- ----------- -----------
1 A 2010-06-25 1000 1000
2 AA 2010-06-25 2000 2000
3 AAA 2010-06-25 3000 3000
4 AAAA 2010-06-25 4000 4000
*/
insert into #t
select 1 ,'A'union all
select 2 ,'AA'union all
select 3 ,'AAA' union all
select 4 ,'AAAA'
create table #t2( ID int, B_ID int,B_Time varchar(10), B_Num int)insert into #t2
select 1, 1 ,'2010-05-25', 1000 union all
select 2, 1 ,'2010-06-25', 1000 union all
select 3 ,2 ,'2010-05-25', 2000 union all
select 4 ,2 ,'2010-06-25', 2000 union all
select 5, 3, '2010-05-25', 3000 union all
select 6, 3 ,'2010-06-25', 3000union all
select 7, 4 ,'2010-05-25', 4000union all
select 8, 4 ,'2010-06-25', 4000 select A_ID, A_Name, B_Time, B_Num,(select sum(B_Num) from #t2 a where a.B_ID=#t.A_ID) as Sum_B_Num
from #t left join
#t2 on B_ID= A_ID WHERE B_Time='2010-06-25'
A_ID A_Name B_Time B_Num Sum_B_Num
----------- ---------- ---------- ----------- -----------
1 A 2010-06-25 1000 2000
2 AA 2010-06-25 2000 4000
3 AAA 2010-06-25 3000 6000
4 AAAA 2010-06-25 4000 8000
FROM table_A a INNER JOIN
table_B b ON a.A_ID = b.B_ID AND b.B_Time = '2010-06-25'
GROUP BY b.B_ID, a.A_ID, a.A_Name, b.B_Time, b.B_Num
select a.A_id,a.a_name,b.b_time,b.b_num,c.sum_b_num
from table_a a,table_b b,(select b_id,sum(b_num) sum_b_num from #b group by b_id) c
where a.a_id=b.b_id and b.b_time='2010-06-25'and b.b_id=c.b_id
insert into #t
select 1 ,'A'union all
select 2 ,'AA'union all
select 3 ,'AAA' union all
select 4 ,'AAAA'
create table #t2( ID int, B_ID int,B_Time varchar(10), B_Num int)insert into #t2
select 1, 1 ,'2010-05-25', 1000 union all
select 2, 1 ,'2010-06-25', 1000 union all
select 3 ,2 ,'2010-05-25', 2000 union all
select 4 ,2 ,'2010-06-25', 2000 union all
select 5, 3, '2010-05-25', 3000 union all
select 6, 3 ,'2010-06-25', 3000union all
select 7, 4 ,'2010-05-25', 4000union all
select 8, 4 ,'2010-06-25', 4000 select A_ID, A_Name, B_Time, B_Num,(select sum(B_Num) from #t2 a where a.B_ID=#t.A_ID) as Sum_B_Num
from #t left join
#t2 on B_ID= A_ID WHERE B_Time='2010-06-25'select a.A_id,a.a_name,b.b_time,b.b_num,c.sum_b_num
from #t a,#t2 b,(select b_id,sum(b_num) sum_b_num from #t2 group by b_id) c
where a.a_id=b.b_id and b.b_time='2010-06-25'and b.b_id=c.b_id
DROP TABLE #tDROP TABLE #t2(所影响的行数为 4 行)
(所影响的行数为 8 行)A_ID A_Name B_Time B_Num Sum_B_Num
----------- ---------- ---------- ----------- -----------
1 A 2010-06-25 1000 2000
2 AA 2010-06-25 2000 4000
3 AAA 2010-06-25 3000 6000
4 AAAA 2010-06-25 4000 8000(所影响的行数为 4 行)A_id a_name b_time b_num sum_b_num
----------- ---------- ---------- ----------- -----------
1 A 2010-06-25 1000 2000
2 AA 2010-06-25 2000 4000
3 AAA 2010-06-25 3000 6000
4 AAAA 2010-06-25 4000 8000(所影响的行数为 4 行)
FROM #A a
LEFT JOIN #B b
ON B_ID=A_ID AND B_TIME='2010-06-25'
GROUP BY A_ID,A_Name ,B_Time, B_Num
FROM #A a
LEFT JOIN #B b
ON B_ID=A_ID AND B_TIME='2010-06-25'
GROUP BY A_ID,A_Name ,B_Time, B_NumA_ID A_Name B_Time B_Num Sum_B_Num
----------- -------- ----------------------- ----------- -----------
1 A 2010-06-25 00:00:00.000 1000 1000
2 AA 2010-06-25 00:00:00.000 2000 2000
3 AAA 2010-06-25 00:00:00.000 3000 3000
4 AAAA 2010-06-25 00:00:00.000 4000 4000(4 row(s) affected)
FROM #A a
LEFT JOIN #B b
ON B_ID=A_ID AND B_TIME='2010-06-25'
GROUP BY A_ID,A_Name ,B_Time, B_NumA_ID A_Name B_Time B_Num Sum_B_Num
----------- -------- ----------------------- ----------- -----------
1 A 2010-06-25 00:00:00.000 1000 2000
2 AA 2010-06-25 00:00:00.000 2000 4000
3 AAA 2010-06-25 00:00:00.000 3000 6000
4 AAAA 2010-06-25 00:00:00.000 4000 8000(4 row(s) affected)