declare @a table(商品号 varchar(12),店铺号 varchar(10),CRTime datetime)
insert into @a values('100000000001','A店','2008/01/09')
insert into @a values('100000000002','B店','2008/01/09')
insert into @a values('100000000003','C店','2008/01/09')
insert into @a values('100000000004','D店','2008/01/09')
--select * from @adeclare @b table(商品号 varchar(12))
insert into @b values('100000000001')
insert into @b values('100000000004')
--select * from @b select a.*,[有/无]=case when b.商品号 is not null then '有' else '无' end from @a a left join @b b on a.商品号=b.商品号
商品号 店铺号 CRTime 有/无
------------ ---------- ----------------------- ----
100000000001 A店 2008-01-09 00:00:00.000 有
100000000002 B店 2008-01-09 00:00:00.000 无
100000000003 C店 2008-01-09 00:00:00.000 无
100000000004 D店 2008-01-09 00:00:00.000 有(4 行受影响)
insert into @a values('100000000001','A店','2008/01/09')
insert into @a values('100000000002','B店','2008/01/09')
insert into @a values('100000000003','C店','2008/01/09')
insert into @a values('100000000004','D店','2008/01/09')
--select * from @adeclare @b table(商品号 varchar(12))
insert into @b values('100000000001')
insert into @b values('100000000004')
--select * from @b select a.*,[有/无]=case when b.商品号 is not null then '有' else '无' end from @a a left join @b b on a.商品号=b.商品号
商品号 店铺号 CRTime 有/无
------------ ---------- ----------------------- ----
100000000001 A店 2008-01-09 00:00:00.000 有
100000000002 B店 2008-01-09 00:00:00.000 无
100000000003 C店 2008-01-09 00:00:00.000 无
100000000004 D店 2008-01-09 00:00:00.000 有(4 行受影响)
insert into @a values('100000000001','A店','2008/01/09')
insert into @a values('100000000002','B店','2008/01/09')
insert into @a values('100000000003','C店','2008/01/09')
insert into @a values('100000000004','D店','2008/01/09')
SELECT * from @adeclare @b table(商品号 varchar(12))
insert into @b values('100000000001')
insert into @b values('100000000004')
select * from @b select a.*,case when b.商品号 is null then '无' else '有' end as '状态' from @a a left join @b b
on a.商品号=b.商品号商品号 店铺号 CRTime 状态
100000000001 A店 2008-01-09 00:00:00.000 有
100000000002 B店 2008-01-09 00:00:00.000 无
100000000003 C店 2008-01-09 00:00:00.000 无
100000000004 D店 2008-01-09 00:00:00.000 有
insert into @a values('100000000001','A店','2008/01/09')
insert into @a values('100000000002','B店','2008/01/09')
insert into @a values('100000000003','C店','2008/01/09')
insert into @a values('100000000004','D店','2008/01/09')
declare @b table(商品号 varchar(12))
insert into @b values('100000000001')
insert into @b values('100000000004')
select a.*,[状态]=case when b.商品号 is not null then '有' else '没有' end from @a a left join @b b on a.商品号=b.商品号商品号 店铺号 CRTime 状态
------------ ---------- ----------------------- ----
100000000001 A店 2008-01-09 00:00:00.000 有
100000000002 B店 2008-01-09 00:00:00.000 没有
100000000003 C店 2008-01-09 00:00:00.000 没有
100000000004 D店 2008-01-09 00:00:00.000 有(4 行受影响)
insert into @a values('100000000001','A店','2008/01/09')
insert into @a values('100000000002','B店','2008/01/09')
insert into @a values('100000000003','C店','2008/01/09')
insert into @a values('100000000004','D店','2008/01/09')
SELECT * from @a declare @b table(商品号 varchar(12))
insert into @b values('100000000001')
insert into @b values('100000000004')
select * from @bselect a.* ,[状态] = case when exists(select 1 from @b where 商品号 = a.商品号) then '有' else '没有' end
from @a a
/*
(所影响的行数为 2 行)商品号 店铺号 CRTime 状态
------------ ---------- ------------------------------------------------------ ----
100000000001 A店 2008-01-09 00:00:00.000 有
100000000002 B店 2008-01-09 00:00:00.000 没有
100000000003 C店 2008-01-09 00:00:00.000 没有
100000000004 D店 2008-01-09 00:00:00.000 有(所影响的行数为 4 行)
*/