从两个表中获取不同的值 [英] fetch distinct value from two tables

问题描述

产品



PID（PK）---名称--- ImagUrl



1 - 手机---东西

2 - 笔记本电脑---东西

3 - 时尚---东西







Tabe优惠券



ID PID City



1 - 2 - 班加罗尔

2 - 3 - 浦那

3 - 2 - 孟买

4 - 1 - 班加罗尔

5 - 2 - 孟买





我想选择加入两张桌子的不同城市名称记录

（如果我有五个不同的城市，那么我想从每个城市选择一条记录）。

Pl''z建议我，我怎样才能获取这些记录。

我使用distinct关键字来获取记录，但是我无法找到欲望输出....

able Product

PID(PK) --- Name --- ImagUrl

1 - Mobile --- something
2 - Laptop --- something
3 - Fashion --- something



Tabe Coupons

ID PID City

1 - 2 - Bangalore
2 - 3 - Pune
3 - 2 - Mumbai
4 - 1 - Bangalore
5 - 2 - Mumbai


I want to select distinct City name records joining two tables
(if i have five different city then i want to select one one record from each city).
Pl''z suggest me, how can i fetch this records.
I trired using distinct keyword to fetch records but i am unable to find desire output....

推荐答案

select distinct Sumit.City, Sumit.Name  -- or Sumit.PID, depends on what you want to show
from
(
    select Row_Number() over(Partition By c.ID, c.City Order BY c.ID desc, City) as rowno,
	c.ID, c.City, p.Name, p.PID
    From Coupons  c
    JOIN Product p on c.pid = p.pid
) as Sumit
where Sumit.rowno = 1

1假设您使用Microsoft的Sql Server

1 assumption is that you use Sql Server by Microsoft

这篇关于从两个表中获取不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！