表a id 选项1 选项2 选项3
1 1 2 3
2 3 1 2
表b item subitem
选项1 1 10
选项1 2 20
选项1 3 30
选项2 1 5
选项2 2 10
选项2 3 15
选项3 1 1
选项3 2 2
选项3 3 3
求表a里啊每条记录的 的总分比如 id 1 总分为10+10+3=23
id 2 总分为30+5+ 2=37
1 1 2 3
2 3 1 2
表b item subitem
选项1 1 10
选项1 2 20
选项1 3 30
选项2 1 5
选项2 2 10
选项2 3 15
选项3 1 1
选项3 2 2
选项3 3 3
求表a里啊每条记录的 的总分比如 id 1 总分为10+10+3=23
id 2 总分为30+5+ 2=37
+isnull((select from b where item='选项2' and subitem=a.选项2),0)
+isnull((select from b where item='选项3' and subitem=a.选项3),0)
from a
from a
left join b b1 on a.选项1 = b1.subitem and b1.item='选项1'
left join b b2 on a.选项2 = b2.subitem and b2.item='选项2'
left join b b3 on a.选项3 = b3.subitem and b3.item='选项3'
(
SELECT ID,
CASE WHEN EXISTS(SELECT 1 FROM TBB WHERE 选项1=item ) THEN MARK ELSE 0 END AS C1,
CASE WHEN EXISTS(SELECT 1 FROM TBB WHERE 选项2=item ) THEN MARK ELSE 0 END AS C2,
CASE WHEN EXISTS(SELECT 1 FROM TBB WHERE 选项3=item ) THEN MARK ELSE 0 END AS C3
FROM TBA )AS T
GROUP BY ID???
insert into a select 1,1,2,3 union select 2,3,1,2
go
create table b(item varchar(8),subitem int, int)
insert into b select '选项1',1,10
insert into b select '选项1',2,20
insert into b select '选项1',3,30
insert into b select '选项2',1,5
insert into b select '选项2',2,10
insert into b select '选项2',3,15
insert into b select '选项3',1,1
insert into b select '选项3',2,2
insert into b select '选项3',3,3
go
select id,isnull((select from b where item='选项1' and subitem=a.选项1),0)
+isnull((select from b where item='选项2' and subitem=a.选项2),0)
+isnull((select from b where item='选项3' and subitem=a.选项3),0)
from a
go
drop table a,b;
go--结果
id
----------- -----------
1 23
2 37
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-12 17:10:01
----------------------------------------------------------------
--> 测试数据:[表a]
if object_id('[表a]') is not null drop table [表a]
go
create table [表a]([id] int,[选项1] int,[选项2] int,[选项3] int)
insert [表a]
select 1,1,2,3 union all
select 2,3,1,2
--> 测试数据:[表b]
if object_id('[表b]') is not null drop table [表b]
go
create table [表b]([item] varchar(5),[subitem] int,[] int)
insert [表b]
select '选项1',1,10 union all
select '选项1',2,20 union all
select '选项1',3,30 union all
select '选项2',1,5 union all
select '选项2',2,10 union all
select '选项2',3,15 union all
select '选项3',1,1 union all
select '选项3',2,2 union all
select '选项3',3,3
--------------开始查询--------------------------
select
id,
isnull((select from 表b where item='选项1' and subitem=a.选项1),0)
+isnull((select from 表b where item='选项2' and subitem=a.选项2),0)
+isnull((select from 表b where item='选项3' and subitem=a.选项3),0) as 总分
from
表a a
----------------结果----------------------------
/*id 总分
----------- -----------
1 23
2 37(所影响的行数为 2 行)
*/