例如:
A(member,product,other)
B(member,product,price)A表记录:会员甲,20ml装飘柔洗发水,其他信息
会员甲,40ml装飘柔洗发水,其他信息会员乙,20ml装飘柔洗发水,其他信息
会员乙,40ml装飘柔洗发水,其他信息B表记录:
,20ml装飘柔洗发水,20.00
,40ml装飘柔洗发水,40.00
甲 ,20ml装飘柔洗发水,5.00那么希望查出来的最后结果是:会员甲,20ml装飘柔洗发水,其他信息, 5.00
会员甲,40ml装飘柔洗发水,其他信息,40.00
会员乙,20ml装飘柔洗发水,其他信息,20.00
会员乙,40ml装飘柔洗发水,其他信息,40.00
THANKS!
A(member,product,other)
B(member,product,price)A表记录:会员甲,20ml装飘柔洗发水,其他信息
会员甲,40ml装飘柔洗发水,其他信息会员乙,20ml装飘柔洗发水,其他信息
会员乙,40ml装飘柔洗发水,其他信息B表记录:
,20ml装飘柔洗发水,20.00
,40ml装飘柔洗发水,40.00
甲 ,20ml装飘柔洗发水,5.00那么希望查出来的最后结果是:会员甲,20ml装飘柔洗发水,其他信息, 5.00
会员甲,40ml装飘柔洗发水,其他信息,40.00
会员乙,20ml装飘柔洗发水,其他信息,20.00
会员乙,40ml装飘柔洗发水,其他信息,40.00
THANKS!
解决方案 »
- 一SQL语句错误,请帮忙修改一下,谢谢
- 【求sql语句】两表间有重复数据,求两表总数(去掉重复)。 PS:guid相同代表两条数据相同
- 新手提问
- SQL中删除记录,用Delete有没有删除记录条数的限制呢?比如上限是100W条?
- 如何实现 每一项的实际长度不够额定长度就用空格填充
- 备分还原问题
- sql用触发器存到另一台数据服务器中碰到的问题(分布式服务器)
- 我做了个传入表名输出XML的过程
- sql语句问题?
- 100求一个Distinct的问题,项目急,,,,救求俺,,,,大侠们
- 求教一条update语句!
- 为什么我登录SQL Server服务器时使用计算机名可以登录成功,而使用IP却登录不成功。
(
select A.member,
A.product,
(select price from B where A.member = B.member and A.product = B.product) as temp
from A
) tab_1 where temp is not nullunion
select member,product,(select price from B where product = tab_2.product and B.member is null) from
(
select A.member,
A.product,
(select price from B where A.member = B.member and A.product = B.product) as temp
from A
) tab_2 where temp is null
会员甲,20ml装飘柔洗发水, 5.00
会员甲,40ml装飘柔洗发水, NULL
会员乙,20ml装飘柔洗发水, NULL
会员乙,40ml装飘柔洗发水, NULL也就是说,价格部分出了点问题.
create table #aa (member varchar(10),product varchar(50),other varchar(20),price varchar(10))
insert into #aa
select a.* ,b.price from a inner join (select price,product from b where member = '' )b on a.product=b.productif exists(select a.member,a.product,a.other,b.price from a inner join b on a.member=b.member and a.product=b.product )
begin
declare @member varchar(10),@product varchar(50),@price varchar(10)
declare cur cursor for (select a.member,a.product,b.price from a inner join b on a.member=b.member and a.product=b.product)
open cur
fetch next from cur into @member,@product,@price
while @@fetch_status=0
begin
update #aa set price=@price where member =@member and product=@product
fetch next from cur into @member,@product,@price
end
close cur
deallocate cur
endselect * from #aa
select a.*,b.price from a left join b on a.member=b.member and a.product=b.product
也可以得到。
from a t1 inner join b t2 on t1.member=t2.member and t1.product=t2.product
union
select member,product,(select price from B where product = tab_2.product and B.member is null) from
(
select A.member,
A.product,
(select price from B where A.member = B.member and A.product = B.product) as temp
from A
) tab_2 where temp is null
(
select A.member,
A.product,
(select price from B where A.member = B.member and A.product = B.product) as temp
from A
) tab_1 where temp is not nullunion
select member,product,(select price from B where product = tab_2.product and B.member is null) from
(
select A.member,
A.product,
(select price from B where A.member = B.member and A.product = B.product) as temp
from A
) tab_2 where temp is null以上所有的SQL是一个语句,
member product
1 1
1 2
1 3
2 1
2 2
2 3
表b
member product price
1 1 10
1 2 5
2 1 13
NULL 2 9
NULL 3 12结果真
member product temp
---------- ---------- --------------------
1 1 10
1 2 5
1 3 12
2 1 13
2 2 9
2 3 12
谢谢你!!!
union
select a.mem ,a.product,a.other,b.price from a,b where a.product =b.product and len(isnull(b.mem,'')) =0
and a.product not in(select a.product from a,b where a.mem =b.mem and a.product =b.product)
union
select a.mem ,a.product,a.other,b.price from a,b where a.product =b.product and b.mem is null
and a.mem not in(select a.mem from a,b where a.mem =b.mem and a.product =b.product)