3.3w+
社区成员
显示包含0数量的名称
第一个表
名称
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
名称
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
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
-晴天 2008-03-19
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
- 打赏
- 举报
cxmcxm 2008-03-19
select 表1.名称,isnull(表2.数量,0) 数量
from 表1 left join 表2 on 表1.名称=表2.名称
where 表2.数量<=10 or 表2.数量 is null- 打赏
- 举报
-晴天 2008-03-19
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
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
- 打赏
- 举报
carrie_hmz 2008-03-19
SELECT A.NAME,ISNULL(B.QTY,0) FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE QTY<=10 OR QTY IS NULL
- 打赏
- 举报
dawugui 2008-03-19
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 行）
*/
- 打赏
- 举报
wzy_love_sly 2008-03-19
[Quote=引用 3 楼 dawugui 的回复:]
喝了点酒造成一楼写错了.
[/Quote]
老龟刚刚去喝酒了？刚我散了600分........
喝了点酒造成一楼写错了.
[/Quote]
老龟刚刚去喝酒了？刚我散了600分........
- 打赏
- 举报
Limpire 2008-03-19
select a.名称, 数量 = isnull(b.数量, 0) from 第一个表 as a left 第二个表 as b on a.名称 = b.名称 where isnull(b.数量, 0) <= 10- 打赏
- 举报
dawugui 2008-03-19
滚.
- 打赏
- 举报
-狙击手- 2008-03-19
[Quote=引用 3 楼 dawugui 的回复:]
喝了点酒造成一楼写错了.
[/Quote]
死性不改
喝了点酒造成一楼写错了.
[/Quote]
死性不改
- 打赏
- 举报
wzy_love_sly 2008-03-19
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<=10a 0
c 2
d 0
f 1
g 0
h 0
j 10
- 打赏
- 举报
-狙击手- 2008-03-19
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.名称)- 打赏
- 举报
dawugui 2008-03-19
喝了点酒造成一楼写错了.
- 打赏
- 举报
dawugui 2008-03-19
select * from
(
select a.名称 , isnull(b.数量,0) 数量 from 第一个表 a left 第二个表 b on a.名称 = b.名称
) t
where 数量 <= 10- 打赏
- 举报
dawugui 2008-03-19
select * from
(
select a.名称 , isnull(b.数量,0) 数量 from 第一个表 a , 第二个表 b on a.名称 = b.名称
) t
where 数量 <= 10- 打赏
- 举报
