本文介绍了如何编写精确查询distinct和rownumber的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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



表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.

推荐答案

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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-21 10:40