表一:test_cat 结构
cat_id cat_name prd_id
1 test_1 1a
2 test_2 1b
3 test_3 1c
表二:test_table 结构
test_id prd_id value_1 value_2 value_3
1 1a 1 1 1
2 1a 1 1 1
3 1a 1 1 1
4 1b 2 2 2
5 1b 2 2 2
6 1c 3 3 3
7 1c 3 3 3希望得到的结果为
cat_id cat_name prd_id value_1 value_2 value_3
1 test_1 1a 3 3 3
2 test_2 1b 4 4 4
3 test_3 1c 6 6 6结果解释
在表一查询的基础上得到表二中属于该表中当前记录的SUM值谢谢!有正确答案马上给分,分不多,重要的是心意
cat_id cat_name prd_id
1 test_1 1a
2 test_2 1b
3 test_3 1c
表二:test_table 结构
test_id prd_id value_1 value_2 value_3
1 1a 1 1 1
2 1a 1 1 1
3 1a 1 1 1
4 1b 2 2 2
5 1b 2 2 2
6 1c 3 3 3
7 1c 3 3 3希望得到的结果为
cat_id cat_name prd_id value_1 value_2 value_3
1 test_1 1a 3 3 3
2 test_2 1b 4 4 4
3 test_3 1c 6 6 6结果解释
在表一查询的基础上得到表二中属于该表中当前记录的SUM值谢谢!有正确答案马上给分,分不多,重要的是心意
value_1=(select sum(value_1) from test_table where prd_id=a.prd_id),
value_2=(select sum(value_2) from test_table where prd_id=a.prd_id),
value_3=(select sum(value_3) from test_table where prd_id=a.prd_id)
from test_cat a
Select
A.*,
SUM(B.value_1) As value_1,
SUM(B.value_2) As value_2,
SUM(B.value_3) As value_3
From
test_cat A
Inner Join
test_table B
On A.prd_id = B.prd_id
Select
A.*,
IsNull(SUM(B.value_1), 0) As value_1,
IsNull(SUM(B.value_2), 0) As value_2,
IsNull(SUM(B.value_3), 0) As value_3
From
test_cat A
Left Join
test_table B
On A.prd_id = B.prd_id
insert test_cat
select 1, 'test_1','1a' union all
select 2, 'test_2', '1b' union all
select 3, 'test_3', '1c'create table test_table (test_id int, prd_id varchar(10), value_1 int, value_2 int, value_3 int)
insert test_table
select 1, '1a' ,1 ,1, 1 union all
select 2, '1a' ,1 ,1, 1 union all
select 3, '1a' ,1 ,1, 1 union all
select 4, '1b' ,2 ,2, 2 union all
select 5, '1b' ,2 ,2, 2 union all
select 6, '1c' ,3 ,3, 3 union all
select 7, '1c' ,3 ,3, 3select a.cat_id,a.cat_name,
value_1=(select sum(value_1) from test_table where prd_id=a.prd_id),
value_2=(select sum(value_2) from test_table where prd_id=a.prd_id),
value_3=(select sum(value_3) from test_table where prd_id=a.prd_id)
from test_cat a
Select
A.cat_id, A.cat_name, A.prd_id,
IsNull(SUM(B.value_1), 0) As value_1,
IsNull(SUM(B.value_2), 0) As value_2,
IsNull(SUM(B.value_3), 0) As value_3
From
test_cat A
Left Join
test_table B
On A.prd_id = B.prd_id
group by A.cat_id, A.cat_name, A.prd_id