表A有100W多行数据,不好改啊。 补充一下表A的字段: Aid, Bid, a, b, c, d, e, f, g, ... x, y, z .................. Aid 为编号,Bid与表B对应
select b.bid,case b.n1 when 'a' then a.a when 'b' then a.b ... when 'z' then a.z end as n1, case b.n2 when 'a' then a.a when 'b' then a.b ... when 'z' then a.z end as n2, ... case b.n5 when 'a' then a.a when 'b' then a.b ... when 'z' then a.z end as n5 from b left join a on a.bid=b.bid--要求:b和a表是一对一关系,如果一对多,数据就很乱了
不是很明白,不过我觉得恰恰是表A需要改变,实际表A是个字典表,字典表如何存放应该是把方便查询放在第一位。表A改为: Aid(自动), Bid(对应B表的Bid), n(对应B表的n1-n5), value 查询就简单了:select b.bid, a1.value as n1, a2.value as n2, a3.value as n3, a4.value as n4, a5.value as n5 from b left join a a1 on b.bid=a1.bid and b.n1=a1.n left join a a2 on b.bid=a2.bid and b.n1=a2.n left join a a3 on b.bid=a3.bid and b.n1=a3.n left join a a4 on b.bid=a4.bid and b.n1=a4.n left join a a5 on b.bid=a5.bid and b.n1=a5.n
写错了:select b.bid, a1.value as n1, a2.value as n2, a3.value as n3, a4.value as n4, a5.value as n5 from b left join a a1 on b.bid=a1.bid and b.n1=a1.n left join a a2 on b.bid=a2.bid and b.n2=a2.n left join a a3 on b.bid=a3.bid and b.n3=a3.n left join a a4 on b.bid=a4.bid and b.n4=a4.n left join a a5 on b.bid=a5.bid and b.n5=a5.n
b left join a a1 on xx=xx a1 是哪来的?这个join 怎么可以连接 a a1 呢?
表A改为: Aid(自动), Bid(对应B表的Bid), n(对应B表的n1-n5), value 原来的BID已经对应的了,现在要增加的字段“n”吗?
补充一下表A的字段:
Aid, Bid, a, b, c, d, e, f, g, ... x, y, z
..................
Aid 为编号,Bid与表B对应
when 'a' then a.a
when 'b' then a.b
...
when 'z' then a.z
end as n1,
case b.n2
when 'a' then a.a
when 'b' then a.b
...
when 'z' then a.z
end as n2,
...
case b.n5
when 'a' then a.a
when 'b' then a.b
...
when 'z' then a.z
end as n5
from b left join a
on a.bid=b.bid--要求:b和a表是一对一关系,如果一对多,数据就很乱了
看看这样行吗?
declare @sql varchar(8000)
select @sql = 'select '+ a.n1 +','
+ a.n2 + ','
+ a.n3 + ','
+ a.n4 + ','
+ a.n5 +
'from 表a'
from 表B a
exec @sql
举个例子:
Bid, n1, n2, n3, n4, n5 (n1~n5 表示周一到周五)
用户通过设置所有关心的表A的内容,就可以正确的收阅到5天的内容了而表A的 a,b,c...每一列就是一种内容,这样说不知道是否清楚,呵呵
现请问表B的结构该如何改?
Aid(自动), Bid(对应B表的Bid), n(对应B表的n1-n5), value 查询就简单了:select b.bid,
a1.value as n1,
a2.value as n2,
a3.value as n3,
a4.value as n4,
a5.value as n5
from b left join a a1 on b.bid=a1.bid and b.n1=a1.n
left join a a2 on b.bid=a2.bid and b.n1=a2.n
left join a a3 on b.bid=a3.bid and b.n1=a3.n
left join a a4 on b.bid=a4.bid and b.n1=a4.n
left join a a5 on b.bid=a5.bid and b.n1=a5.n
a1.value as n1,
a2.value as n2,
a3.value as n3,
a4.value as n4,
a5.value as n5
from b left join a a1 on b.bid=a1.bid and b.n1=a1.n
left join a a2 on b.bid=a2.bid and b.n2=a2.n
left join a a3 on b.bid=a3.bid and b.n3=a3.n
left join a a4 on b.bid=a4.bid and b.n4=a4.n
left join a a5 on b.bid=a5.bid and b.n5=a5.n
a1 是哪来的?这个join 怎么可以连接 a a1 呢?
Aid(自动), Bid(对应B表的Bid), n(对应B表的n1-n5), value
原来的BID已经对应的了,现在要增加的字段“n”吗?