select b.cardid, a.hotelid from b join a on b.cardid >= startcardno and b.cardid <= endcardno
---测试数据--- if object_id('[A]') is not null drop table [A] go create table [A]([hotelid] int,[startcardno] int,[endcardno] int) insert [A] select -1,1000000000,1000000100 union all select 10101,1000000101,1000000200if object_id('[B]') is not null drop table [B] go create table [B]([cardid] int) insert [B] select 1000000002 union all select 1000000003 union all select 1000000004 union all select 1000000006 union all select 1000000007 union all select 1000000120 union all select 1000000121---查询--- select B.cardid, A.hotelid from A,B where B.cardid between A.startcardno and A.endcardno---结果--- cardid hotelid ----------- ----------- 1000000002 -1 1000000003 -1 1000000004 -1 1000000006 -1 1000000007 -1 1000000120 10101 1000000121 10101(所影响的行数为 7 行)
select B.cardid, A.hotelid from A inner join B on B.cardid between A.startcardno and A.endcardno
---测试数据--- if object_id('[A]') is not null drop table [A] go create table [A]([hotelid] int,[startcardno] int,[endcardno] int) insert [A] select -1,1000000000,1000000100 union all select 10101,1000000101,1000000200if object_id('[B]') is not null drop table [B] go create table [B]([cardid] int,[p] int) insert [B] select 1000000002,0 union all select 1000000003,0 union all select 1000000004,1 union all select 1000000006,0 union all select 1000000007,0 union all select 1000000120,1 union all select 1000000121,0---查询--- select B.cardid, A.hotelid from A,B where B.cardid between A.startcardno and A.endcardno and B.p=0---结果--- cardid hotelid ----------- ----------- 1000000002 -1 1000000003 -1 1000000006 -1 1000000007 -1 1000000121 10101(所影响的行数为 5 行)
select b.CardId,a. HotelId from b inner join a on b.cardId>=a.startcardno and b.cardId<=a.endcardno
from b
join a on b.cardid >= startcardno and b.cardid <= endcardno
if object_id('[A]') is not null drop table [A]
go
create table [A]([hotelid] int,[startcardno] int,[endcardno] int)
insert [A]
select -1,1000000000,1000000100 union all
select 10101,1000000101,1000000200if object_id('[B]') is not null drop table [B]
go
create table [B]([cardid] int)
insert [B]
select 1000000002 union all
select 1000000003 union all
select 1000000004 union all
select 1000000006 union all
select 1000000007 union all
select 1000000120 union all
select 1000000121---查询---
select
B.cardid,
A.hotelid
from A,B
where B.cardid between A.startcardno and A.endcardno---结果---
cardid hotelid
----------- -----------
1000000002 -1
1000000003 -1
1000000004 -1
1000000006 -1
1000000007 -1
1000000120 10101
1000000121 10101(所影响的行数为 7 行)
B.cardid,
A.hotelid
from A
inner join B
on B.cardid between A.startcardno and A.endcardno
卡ID
cardid p
1000000002 0
1000000003 0
1000000004 0
1000000006 1
1000000007 0
1000000120 1
1000000121 0我想要的结果是:通过B表的cardid从A表中查出他所在的hotelid 并且p=0 的!谢谢!
---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([hotelid] int,[startcardno] int,[endcardno] int)
insert [A]
select -1,1000000000,1000000100 union all
select 10101,1000000101,1000000200if object_id('[B]') is not null drop table [B]
go
create table [B]([cardid] int,[p] int)
insert [B]
select 1000000002,0 union all
select 1000000003,0 union all
select 1000000004,1 union all
select 1000000006,0 union all
select 1000000007,0 union all
select 1000000120,1 union all
select 1000000121,0---查询---
select
B.cardid,
A.hotelid
from A,B
where B.cardid between A.startcardno and A.endcardno
and B.p=0---结果---
cardid hotelid
----------- -----------
1000000002 -1
1000000003 -1
1000000006 -1
1000000007 -1
1000000121 10101(所影响的行数为 5 行)
select b.CardId,a. HotelId
from b
inner join a on b.cardId>=a.startcardno and b.cardId<=a.endcardno