如果用left outer join连接查询以下的两个表？

Question

select a.数量,b.金额
from 表1 a
  left join 表2 b
    on case when a.数量 =1 then 1 
            when a.数量 in (2,4) then 5
            when a.数量 =6 then 3
       end
     = case when b.金额 in(2,4) then 1 
            else b.金额
       end 

Answer 1

if object_id('a') is not null
  drop table a
go
create table a([名称] varchar(10),[数量] varchar(10))
insert a select 'R',1
insert a select 'R',2
insert a select 'R',4
insert a select 'R',6
go
if object_id('b') is not null
  drop table b
go
create table b([名称] varchar(10),[数量] varchar(10),[金额] varchar(10))
insert b select 'R','0','2'
insert b select 'R','0','4'
insert b select 'R','1','5'
insert b select 'R','5','3'
insert b select 'R','8','6'
goselect a.数量,b.金额
from a
  left join b
    on case when a.数量 =1 then 1 
            when a.数量 in (2,4) then 5
            when a.数量 =6 then 3
       end
     = case when b.金额 in(2,4) then 1 
            else b.金额
       end 
/*
数量         金额
---------- ----------
1          2
1          4
2          5
4          5
6          3(5 行受影响)
*/

Answer 2

名称 数量 
R    1 
表2： 
名称 数量 金额 
R    0   2 
当表1数量为1时，可以连接表2中金额的2或4 
            当表1数量为2时，可以连接表2中金额的5 
            当表1数量为4时，可以连接表2中金额的5 
            当表1数量为6时，可以连接表2中金额的3 
规律：根据左表进行连接，且只能连接与表2数量低且最接近数量的金额 select a.数量
        b.金额=case when a.数量=1 then 2 or 4
                   when a.数量=2 then 5
                   when a.数量=4 then 5
                   when a.数量=6 then 3
                   else 0 end
from 表1 a join 表2 on
a.名称=b.名称  

Answer 3

select a.数量
        b.金额=case when a.数量=1 then 2 or 4
                   when a.数量=2 then 5
                   when a.数量=4 then 5
                   when a.数量=6 then 3
                   when b.数量=0 then 2 or 4
                   else 0 end
from 表1 a left join 表2 on
a.名称=b.名称

Answer 4

如表1下面还有很多的数据，如：
表1： 
名称 数量 
R    1 
R    2 
R    4 
R    6 
R    3
..   ..表2不变，如果根据规律得到需要要求的表呢

Answer 5

要根据算法写，不能根据数据来写
---测试数据---
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([名称] varchar(1),[数量] int)
insert [表1]
select 'R',1 union all
select 'R',2 union all
select 'R',4 union all
select 'R',6
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([名称] varchar(1),[数量] int,[金额] int)
insert [表2]
select 'R',0,2 union all
select 'R',0,4 union all
select 'R',1,5 union all
select 'R',5,3 union all
select 'R',8,6
 
---查询---
select 
  a.数量1 as 数量,
  b.金额
from
(
select 
  数量 as 数量1,
  (select max(数量) from [表2] where 数量<t.数量) as 数量2
from [表1] t
) a
left join [表2] b
on a.数量2=b.数量
---结果---
数量          金额          
----------- ----------- 
1           2
1           4
2           5
4           5
6           3（所影响的行数为 5 行）

Answer 6

不满足条件的为null
应该按算法
因为若表1下面还有很多数据，表2下面也有很多数据，所以根据数据不合适，请问还有别的解决方法吗

Answer 7

若这这样的算法：
select a.数量1 as 数量,
b.金额=(case ...)
from 表1 a left outer 表2 b on a.名称=b.名称 and a.数量>b.数量 and (exists(select 1 from 表2 c where c.名称=b.名称 and b.数量=0）and not exists(select 1 from 表2 d where d.名称=b.名称 and d.数量>b.数量 and b.数量>0))在b.金额中设置什么样的条件也能得到如图的结果？

Answer 8

若这这样的算法： 
select a.数量1 as 数量, 
b.金额=(case ...) 
from 表1 a left outer 表2 b on a.名称=b.名称 and a.数量>b.数量 and (exists(select 1 from 表2 c where c.名称=b.名称 and b.数量=0）or not exists(select 1 from 表2 d where d.名称=b.名称 and d.数量>b.数量 and b.数量>0)) 在b.金额中设置什么样的条件也能得到如图的结果？

Answer 9

就是说表1为基准，设定左连接。
当exists(select 1 from 表2 c where c.名称=b.名称 and b.数量=0 返回表2数量为0的情况
not exists(select 1 from 表2 d where d.名称=b.名称 and d.数量>b.数量 and b.数量>0) 返回表2数量大于0的情况
其中他们共同的限制条件为表1数量>表2数量但是我又想在 显示的表2.金额设置条件
若表1数量为1时，表2数量为0，可以带出表2的金额2或4
若表1数量大于1时，表2数量>0，可以带出符合的情况，但是却又会带出前一种情况
所以我想问下在case如何设置

Answer 10

或者能不能以这样形式：
select a.数量1 as 数量, 
b.金额=(case ...) 
from 表1 a left outer 表2 b
.......因为我想以表1为准，连接很多的表，如果变成子查询，很麻烦你

Answer 11

select a.数量,b.金额
from 表1 a
  left join 表2 b
    on case when a.数量 =1 then 1 
            when a.数量 in (2,4) then 5
            when a.数量 =6 then 3
       end
     = case when b.金额 in(2,4) then 1 
            else b.金额
       end 
如果表1和表2 不限制数据的数据，该如何设置查询的 我要设计报表 就和你说的差不多，但是我的那个综合报表不限制数据，所以你提供的只是很简单的
谢谢你的忠告 

Answer 12

select a.数量,b.金额
from 表1 a
  left join 表2 b
    on case when a.数量 =1 then 1 
            when a.数量 in (2,4) then 5
            when a.数量 =6 then 3
       end
     = case when b.金额 in(2,4) then 1 
            else b.金额
       end 

Answer 13

if object_id('a') is not null
  drop table a
go
create table a([名称] varchar(10),[数量] varchar(10))
insert a select 'R',1
insert a select 'R',2
insert a select 'R',4
insert a select 'R',6
go
if object_id('b') is not null
  drop table b
go
create table b([名称] varchar(10),[数量] varchar(10),[金额] varchar(10))
insert b select 'R','0','2'
insert b select 'R','0','4'
insert b select 'R','1','5'
insert b select 'R','5','3'
insert b select 'R','8','6'
goselect a.[名称],a.数量,b.[金额]
from 
(
select [名称],数量2,min(数量1) as 数量1
from
(
select a.数量 as 数量1,b.数量 as 数量2,a.[名称]
from a
  left join b
on a.数量>b.数量 and a.[名称]=b.[名称]
) c
group by [名称],数量2
) d
left join a on d.[名称]=a.[名称]  and d.数量1=a.数量 
left join b on d.[名称]=b.[名称]  and d.数量2=b.数量 
要先比较数量大小的

Answer 14

不好意思上面有个地方写反了
if object_id('a') is not null
  drop table a
go
create table a([名称] varchar(10),[数量] int)
delete from a
insert a select 'R',1
insert a select 'R',2
insert a select 'R',4
insert a select 'R',6
insert a select 'R',-1
go
if object_id('b') is not null
  drop table b
go
create table b([名称] varchar(10),[数量] int,[金额] int)
insert b select 'R','0','2'
insert b select 'R','0','4'
insert b select 'R','1','5'
insert b select 'R','5','3'
insert b select 'R','8','6'
goselect a.[名称],a.数量,b.[金额]
from 
(
select [名称],数量1,max(数量2) as 数量2
from
(
select a.数量 as 数量1,b.数量 as 数量2,a.[名称]
from a
  left join b
on a.数量>b.数量 and a.[名称]=b.[名称]
) c
group by [名称],数量1
) d
left join a on d.[名称]=a.[名称]  and d.数量1=a.数量 
left join b on d.[名称]=b.[名称]  and d.数量2=b.数量 

Answer 15

select 
    [表1].数量
    ,[表2].金额
from
    [表1] left join 
    (select 
       ,(select min([表1].数量) from [表1] where [表1].数量>[表2].数量) as 数量
        ,[表2].金额
     from [表2]
    ) as [表2]
    on [表1].数量=[表2].数量

Answer 16

select [表1].数量, [表2].金额 
from [表1] left jion [表2]
on  [表1].[名称] = [表2].[名称] 
    and [表2].数量 = (select max(数量) from [表2] where 数量 < [表1].数量)
比较容易被理解