如何编写精确查询distinct和rownumber [英] How to write exact query distinct and rownumber comes into role

问题描述

我的项目中有一个场景。让我举一个简单的例子。请帮助



表1

I have one scenario in my project. Let me give a simple example of it. Kindly help

table 1

address_id    hub_id           name
100             20           anurag
100             20          abhishek
100             20           rakesh
200             50           nitesh
200             50            neha

table 2

table 2

address_id
100
200

现在
我必须选择如下

now i have to select as below

address_id       hub_id        
100              200

select distinct(t1.address_id),t1.hub_id from #temp2 t2
join #temp1 t1 on t2.address_id=t1.address_id 
where t1.hub_id=20  

现在给出了答案，但是呃ave如下所示使用

now that gives the answer but i have to use it as below

insert into #temp3(address_id,hub_id,row)
select distinct(t1.address_id),t1.hub_id, ROW_NUMBER() over (order by t1.address_id) as rownumber from #temp2 t2
join #temp1 t1 on t2.address_id=t1.address_id 
where t1.hub_id=20

但这给了我3行

but this gives me 3 rows

row	address_id	hub_id
1	100	        20
2	100	        20
3	100	        20

但可以这样做....使用rownumber将其插入一个单独的表中并仅获取一个行像

but can it be done....inserting it in a separate table using rownumber and fetching only one row like

row   address_id   hub_id
1       100         20

------我有这样的方式......

------i got this way ...

with cte as(select distinct(t1.address_id),t1.hub_id from #temp2 t2
join #temp1 t1 on t2.address_id=t1.address_id
where t1.hub_id=20),
cte1 as
(select address_id, row_number() over (order by address_id) as row from cte)

select * from cte1

但是还有其他方法（在单个查询中） 。请帮助。

But is there any other approach(in single query). Kindly help.

推荐答案

我不知道你为什么要加入table2，如果它存储与table1相同的值（ hub_id ）？



看看这里：

I don't know why are you trying to join table2 if it stores the same values as table1 (hub_id)?

Have a look here:

DECLARE @table1 TABLE (address_id INT, hub_id INT, [name] VARCHAR(30))
INSERT INTO @table1 (address_id, hub_id, [name])
SELECT 100, 20, 'anurag'
UNION ALL SELECT 100, 20, 'abhishek'
UNION ALL SELECT 100, 20, 'rakesh'
UNION ALL SELECT 200, 50, 'nitesh'
UNION ALL SELECT 200, 50, 'neha'

SELECT ROW_NUMBER() OVER (ORDER BY address_id) AS RowNo, *
FROM (
    SELECT DISTINCT address_id, hub_id
    FROM @table1
) AS T

结果：

Result:

RowNo   address_id      hub_id
1	100		20
2	200		50

这篇关于如何编写精确查询distinct和rownumber的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！