显示包含0数量的名称

[diamond_back]

第一个表
名称
A
B
C
D
E
F
G
H
I
J
第二个表
名称 数量
B 30
C 2
E 11
F 1
I 40
J 10
结果
为显示0库存及库存<=10的名称,并显示现存数
名称 数量
A 0
C 2
D 0
F 1
G 0
H 0
J 10

[dawugui]

select * from
(
  select a.名称 , isnull(b.数量,0) 数量 from 第一个表 a , 第二个表 b on a.名称 = b.名称
) t
where 数量 <= 10

[dawugui]

select * from
(
  select a.名称 , isnull(b.数量,0) 数量 from 第一个表 a left 第二个表 b on a.名称 = b.名称
) t
where 数量 <= 10

[dawugui]

喝了点酒造成一楼写错了.

[-狙击手-]

select *
from table2 a
where exists(select 1 from table1 where a.名称 = b.名称) and 数量 <= 10
union all
select 名称 ,0
from table1 b
where not exists(select 1 from table2 where a.名称 = b.名称)

[wzy_love_sly]

declare @a table (name varchar(20))
insert into @a select 'a'
insert into @a select 'b'
insert into @a select 'c'
insert into @a select 'd'
insert into @a select 'e'
insert into @a select 'f'
insert into @a select 'g'
insert into @a select 'h'
insert into @a select 'i'
insert into @a select 'j'

declare @b table (name varchar(20),num int)
insert into @b select 'b',30
insert into @b select 'c',2
insert into @b select 'e',11
insert into @b select 'f',1
insert into @b select 'i',40
insert into @b select 'j',10

select a.name,isnull(b.num,0) as num from @a a left join @b b
on a.name = b.name
where b.num is null or b.num<=10

a 0
c 2
d 0
f 1
g 0
h 0
j 10

[-狙击手-]

[Quote=引用 3 楼 dawugui 的回复:]
喝了点酒造成一楼写错了.
[/Quote]


死性不改

[dawugui]

滚.

[Limpire]

select a.名称, 数量 = isnull(b.数量, 0) from 第一个表 as a left 第二个表 as b on a.名称 = b.名称 where isnull(b.数量, 0) <= 10

[wzy_love_sly]

[Quote=引用 3 楼 dawugui 的回复:]
喝了点酒造成一楼写错了.
[/Quote]
老龟刚刚去喝酒了？刚我散了600分........

[dawugui]

declare @a table (名称 varchar(20))
insert into @a select 'a'
insert into @a select 'b'
insert into @a select 'c'
insert into @a select 'd'
insert into @a select 'e'
insert into @a select 'f'
insert into @a select 'g'
insert into @a select 'h'
insert into @a select 'i'
insert into @a select 'j'

declare @b table (名称 varchar(20),数量 int)
insert into @b select 'b',30
insert into @b select 'c',2
insert into @b select 'e',11
insert into @b select 'f',1
insert into @b select 'i',40
insert into @b select 'j',10
T
select * from
(
  select a.名称 , isnull(b.数量,0) 数量 from @a a left join @b b on a.名称 = b.名称
) t
where 数量 <= 10

/*
名称                   数量          
-------------------- ----------- 
a                    0
c                    2
d                    0
f                    1
g                    0
h                    0
j                    10

（所影响的行数为 7 行）
*/

[carrie_hmz]

SELECT A.NAME,ISNULL(B.QTY,0) FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE QTY<=10 OR QTY IS NULL

[-晴天]

drop table tb,tb1
go
create table tb (n char(1))
create table tb1 (n char(1),c int)
insert into tb select 'a'
insert into tb select 'b'
insert into tb select 'c'
insert into tb select 'd'
insert into tb select 'e'
insert into tb select 'f'
insert into tb select 'g'
insert into tb select 'h'
insert into tb select 'i'
insert into tb select 'j'

insert into tb1 select 'b',30
insert into tb1 select 'c',2
insert into tb1 select 'e',11
insert into tb1 select 'f',1
insert into tb1 select 'i',40
insert into tb1 select 'j',10


select a.n,isnull(b.c,0) from tb a left join tb1 b on b.n=a.n

[cxmcxm]

select 表1.名称,isnull(表2.数量,0) 数量
from 表1 left join 表2 on 表1.名称=表2.名称 
where 表2.数量<=10 or 表2.数量 is null

[-晴天]

drop table tb,tb1 
go 
create table tb (n char(1)) 
create table tb1 (n char(1),c int) 
insert into tb select 'a' 
insert into tb select 'b' 
insert into tb select 'c' 
insert into tb select 'd' 
insert into tb select 'e' 
insert into tb select 'f' 
insert into tb select 'g' 
insert into tb select 'h' 
insert into tb select 'i' 
insert into tb select 'j' 

insert into tb1 select 'b',30 
insert into tb1 select 'c',2 
insert into tb1 select 'e',11 
insert into tb1 select 'f',1 
insert into tb1 select 'i',40 
insert into tb1 select 'j',10 


select a.n,isnull(b.c,0) from tb a left join tb1 b on b.n=a.n where isnull(b.c,0)<=10