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
1 洗衣粉
2 肥皂
3 牙膏
B表
Bid membername Aid count
1 张三 1 10
2 张三 3 15
3 李四 1 5
需求:通过一条sql查询张三领了哪些东西,分别领了多少个,没有领的算0,按Aid排序,得到如下结果:张三 洗衣粉 10
张三 肥皂 15
张三 牙膏 0
b.membername,a.name,isnull(a.[count],0)
from
b left join a
on
b.aid=a.aid
where
b..membername='张三'
from A left join B on A.Aid =B.Aid
from A left join B on A.Aid =B.Aid
where b.membername='张三'
order by a.Aid
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
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
from A right join B on (A.Aid =B.Aid)
where B.membername='张三'
group by B.membername,A.name
(
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 cSelect *
from @tableB b
join @tableA a on b.aid=a.aid
where b.membername='ZhangSan'
order by b.aid
虽然稍微麻烦了一点,我觉得可以用这样来实现
(
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 cSelect *
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'牙膏'
Goif 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
*/
-- 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 行受影响)*/