sql-server - 如何只显示一个值结果（全外连接）

Question

表和字段信息如下。

Item ID (Column : Item ID / Desc)
=====================
Item ID      Desc
0001 --------  A
0002 --------  B
0003 -------- null

Price Table (Column : Item ID  / Price Level(lvl)  / Price)
=========================================
Item ID      Price Level           Price
0001  -----------  1   -------------3.99
0001  -----------  2   -------------1.99
0002  -----------  1   -------------2.99
0003  -----------  1   -------------5.99
0003  -----------  2   -------------3.99

（默认价格等级为“1”）因此，每个商品的价格等级为 1，某些商品的价格等级为 1 和 2

我使用完整的外连接“项目”和“价格”表。

select trs_itm.id, item.desc, price.lvl, price.price from trs_itm
full outer join item on trs_itm.id = item.id
full outer join price on price.id = item.id
group by trs_itm.id, item.desc, price.lvl, price.price 

如何显示查询结果如下。

如果某个项目的价格级别为“2”，则只显示结果价格级别 2。但该项目没有价格级别“2”，则仅显示默认价格级别 1。

请帮我。

==============================================
ID        Desc    lvl       price
0001 ------ A ---- 2 -------1.99
0002 ------ B ---- 1 -------2.99
0003 -----null --- 2 -------3.99

标签： sql-servervb.net

Answer 1

在这种情况下，我不知道您为什么要进行完全外部联接。
加入 2 个表和返回每个项目的最高价格水平的查询：

select i.*, p.pricelevel, p.price
from item i 
inner join price p on p.itemid = i.itemid
inner join (
  select itemid, max(pricelevel) pricelevel
  from price
  group by itemid
) g on g.itemid = p.itemid and g.pricelevel = p.pricelevel
order by i.itemid

或与not exists：

select i.*, p.pricelevel, p.price
from item i inner join (
  select p.* from price p
  where not exists (
    select 1 from price
    where itemid = p.itemid and pricelevel > p.pricelevel 
  )
) p on p.itemid = i.itemid
order by i.itemid

请参阅演示。
结果：

> itemid | desc | pricelevel | price
> -----: | :--- | ---------: | ----:
>      1 | A    |          2 |  1.99
>      2 | B    |          1 |  2.99
>      3 | null |          2 |  3.99