两个表关联按条件查询的问题

[luolie]

A表：

Aid name
1 洗衣粉
2 肥皂
3 牙膏


B表
Bid membername Aid count
1 张三 1 10
2 张三 3 15
3 李四 1 5


需求：通过一条sql查询张三领了哪些东西，分别领了多少个，没有领的算0，按Aid排序，得到如下结果：

张三 洗衣粉 10
张三 肥皂 15
张三 牙膏 0

[--小F--]

select
  b.membername,a.name,isnull(a.[count],0)
from
  b left join a
on
  b.aid=a.aid
where
  b..membername='张三'

[xuam]

select B.membername ,A.name,sum(isnull(B.count,0)) 
 from A left join B on A.Aid =B.Aid 

[xuam]

select B.membername ,A.name,sum(isnull(B.count,0)) 
 from A left join B on A.Aid =B.Aid 
where b.membername='张三'
order by a.Aid

[geniuswjt]

select B.membername,A.name,sum(isnull(B.count,0)) 
from A right join B on (A.Aid =B.Aid) 
group by B.membername,A.name

[geniuswjt]

select B.membername,A.name,sum(isnull(B.count,0)) 
from A right join B on (A.Aid =B.Aid) 
where B.membername='张三'
group by B.membername,A.name

[Quote=引用楼主 luolie 的回复:]
A表：

Aid name
1 洗衣粉
2 肥皂
3 牙膏


B表
Bid membername Aid count
1 张三 1 10
2 张三 3 15
3 李四 1 5


需求：通过一条sql查询张三领了哪些东西，分别领了多少个，没有领的算0，按Aid排序，得到如下结果：

张三 洗衣粉 10
张三 肥皂 15
张三 牙膏 0
[/Quote]

[luolie]

以上都不对哦，都不能得到 张三 牙膏 0 这条结果。

[Rotel-刘志东]

select B.membername,A.name,sum(isnull(B.count,0)) 
from A right join B on (A.Aid =B.Aid) 
where B.membername='张三'
group by B.membername,A.name

[luolie]

楼上的还是不对，查询结果只有：

张三 洗衣粉 10
张三 肥皂 15

没有

张三 牙膏 0

[luolie]

大家再帮帮忙呢。。

[kensouterry]

那你不如先把张三未领的内容先用0标记出来 然后再连接了

[luolie]

楼上的详细点呢？只想用一条SQL。

[kensouterry]

declare @tableA table
(
aid int,
name nvarchar(10)
);
insert into @tableA values(1,'powder')
insert into @tableA values(2,'soap')
insert into @tableA values(3,'toothpaste')

declare @tableB table
(
bid int identity(1,1),
membername varchar(50),
aid int,
[count] int
);
insert into @tableB values('ZhangSan',1,10)
insert into @tableB values('ZhangSan',3,15)
insert into @tableB values('Lisi',1,5)


INSERT INTO @tableB
select 'ZhangSan',c.aid,0
from (Select aid
from @tableA
except
Select aid
from @tableB
where membername='ZhangSan') as c

Select *
from @tableB b
join @tableA a on b.aid=a.aid
where b.membername='ZhangSan'
order by b.aid


虽然稍微麻烦了一点，我觉得可以用这样来实现

[kensouterry]

declare @tableA table
(
aid int,
name nvarchar(10)
);
insert into @tableA values(1,'powder')
insert into @tableA values(2,'soap')
insert into @tableA values(3,'toothpaste')

declare @tableB table
(
bid int identity(1,1),
membername varchar(50),
aid int,
[count] int
);
insert into @tableB values('ZhangSan',1,10)
insert into @tableB values('ZhangSan',3,15)
insert into @tableB values('Lisi',1,5)


INSERT INTO @tableB
  select 'ZhangSan',c.aid,0
from (Select aid
from @tableA
except
Select aid
from @tableB
where membername='ZhangSan') as c

Select *
from @tableB b
join @tableA a on b.aid=a.aid
where b.membername='ZhangSan'
order by b.aid

一条SQL？

[中国风]

這樣?

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([Aid] int,[name] nvarchar(3))
Insert #A
select 1,N'洗衣粉' union all
select 2,N'肥皂' union all
select 3,N'牙膏'
Go

if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([Bid] int,[membername] nvarchar(2),[Aid] int,[count] int)
Insert #B
select 1,N'张三',1,10 union all
select 2,N'张三',3,15 union all
select 3,N'李四',1,5
Go
SELECT 
	x.membername,x.[name],ISNULL(y.[count],0) AS [count]
FROM 
	(SELECT * FROM (Select DISTINCT [membername] from #B) AS b,#A AS a) AS x
LEFT JOIN #B AS y ON x.[Aid]=y.[Aid] AND x.[membername]=y.[membername]
ORDER BY x.membername desc

/*
membername	name	count
张三	洗衣粉	10
张三	肥皂	0
张三	牙膏	15
李四	洗衣粉	5
李四	肥皂	0
李四	牙膏	0
*/

[kensouterry]

交叉连接取0？ 也是一个方法

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-10-13 16:16:32
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([Aid] int,[name] varchar(6))
insert [A]
select 1,'洗衣粉' union all
select 2,'肥皂' union all
select 3,'牙膏'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([Bid] int,[membername] varchar(4),[Aid] int,[count] int)
insert [b]
select 1,'张三',1,10 union all
select 2,'张三',3,15 union all
select 3,'李四',1,5
--------------开始查询--------------------------
select
  a.membername as name, a.name ,ISNULL(b.count,0)
from
(
select
  distinct a.Aid,a.name,b.membername 
from
  A,b)a
left join
  b
on
  a.Aid=b.Aid 
and
  a.membername=b.membername
where
  a.membername='张三' 
----------------结果----------------------------
/* name name   
---- ------ -----------
张三   洗衣粉    10
张三   肥皂     0
张三   牙膏     15

(3 行受影响)

*/