如何在没有order by的情况下在SQL Server中使用distinct

时间：2022-02-17 07:08:06

如何在没有order by的情况下在SQL Server中使用distinct

I have a table #temprajesh with columns idtask, taskname. I am getting duplicate task id but I need a single value with same order of idtask.

我有一个表#temprajesh,列idtask,taskname。我正在获取重复的任务ID,但我需要一个具有相同idtask顺序的值。

When I tried below query

当我尝试下面的查询

select idtask 
from #temprajesh 
group by  idtask,taskname

Using group by or distinct also am able to eliminate the duplicate values but the order of idtask column is changed to ascending.

使用group by或distinct也可以消除重复值,但idtask列的顺序更改为升序。

I also have tried the below query using rownumber when I use rownumber am not able to eliminate the duplicate records but the order of idtask remains same

当我使用rownumber时我也尝试使用rownumber进行下面的查询我不能消除重复记录,但idtask的顺序保持不变

select distinct idtask, taskname, ROW_NUMBER() OVER (order by idtask) 
from #temprajesh

I need idtask column to be in same order which was before group by statement. I have the table data as

我需要idtask列与group by语句之前的顺序相同。我把表数据作为

159480  power_10sites_5minhybrid_Multi_Site
159480  power_10sites_5minhybrid_Multi_Site
159480  power_10sites_5minhybrid_Multi_Site
159480  power_10sites_5minhybrid_Multi_Site
159480  power_10sites_5minhybrid_Multi_Site
159480  power_10sites_5minhybrid_Multi_Site
159480  power_10sites_5minhybrid_Multi_Site
159480  power_10sites_5minhybrid_Multi_Site
159480  power_10sites_5minhybrid_Multi_Site
159480  power_10sites_5minhybrid_Multi_Site
159481  123_8_Multi_Site
159481  123_8_Multi_Site
159481  123_8_Multi_Site
159481  123_8_Multi_Site
159481  123_8_Multi_Site
159481  123_8_Multi_Site
159481  123_8_Multi_Site
159481  123_8_Multi_Site
159481  123_8_Multi_Site
159481  123_8_Multi_Site
159482  _Multi_Site
159483  _Multi_Site
159484  _Multi_Site
159484  _Multi_Site
159485  123_10_Multi_Site
159485  123_10_Multi_Site
159485  123_10_Multi_Site
159485  123_10_Multi_Site
159485  123_10_Multi_Site
159485  123_10_Multi_Site
159485  123_10_Multi_Site
159485  123_10_Multi_Site
159485  123_10_Multi_Site
159485  123_10_Multi_Site
159486  123_11_Multi_Site
159486  123_11_Multi_Site
159486  123_11_Multi_Site
159486  123_11_Multi_Site
159486  123_11_Multi_Site
159486  123_11_Multi_Site
159486  123_11_Multi_Site
159486  123_11_Multi_Site
159486  123_11_Multi_Site
159486  123_11_Multi_Site
159487  300_Multi_Site
159487  300_Multi_Site
159487  300_Multi_Site
159487  300_Multi_Site
159487  300_Multi_Site
159487  300_Multi_Site
159487  300_Multi_Site
159487  300_Multi_Site
159487  300_Multi_Site
159487  300_Multi_Site
159488  301_Multi_Site
159488  301_Multi_Site
159489  123_12_Multi_Site
159489  123_12_Multi_Site
159410  gh_Multi_Site
159416  ujh_Multi_Site
159353  uh_Multi_Site
159402  g_Multi_Site
159347  sd_Multi_Site
159396  dfg_Multi_Site
159413  try_Multi_Site
159390  fgd_Multi_Site
159382  ghj_Multi_Site
159356  ertdsf_Multi_Site
159393  eg_Multi_Site
159350  jh_Multi_Site
159399  sdf_Multi_Site
159414  uhy_Multi_Site
159391  fg_Multi_Site
159385  R1_Multi_Site
159408  xcv_Multi_Site
159394  fgd_Multi_Site
159351  jh_Multi_Site
159388  fdg_Multi_Site
159411  gh_Multi_Site
159417  ui_Multi_Site
159354  ert_Multi_Site
159348  sd_Multi_Site
159405  gtr_Multi_Site
159397  tyu_Multi_Site
159389  tyu_Multi_Site
159412  tf_Multi_Site
159383  ergt_Multi_Site
159380  sdfsdf_Multi_Site
159406  gtr_Multi_Site
159400  sdf_Multi_Site
159349  jh_Multi_Site
159392  fg_Multi_Site
159409  gh_Multi_Site
159386  fdg_Multi_Site
159346  sd_Multi_Site
159352  uh_Multi_Site
159387  fdg_Multi_Site
159418  ty_Multi_Site
159404  gtr_Multi_Site
159335  gh_Multi_Site
159398  gr_Multi_Site
159415  fdg_Multi_Site
159384  R1_Multi_Site
159421  r_Multi_Site
159401  sdf_Multi_Site
159381  sdfsdf_Multi_Site
159395  sfgd_Multi_Site
159407  xcv_Multi_Site

I need the output as

我需要输出为

159480  power_10sites_5minhybrid_Multi_Site
159481  123_8_Multi_Site
159482  _Multi_Site
159485  123_10_Multi_Site
159486  123_11_Multi_Site
159487  300_Multi_Site
159488  301_Multi_Site
159489  123_12_Multi_Site
159410  gh_Multi_Site
159416  ujh_Multi_Site
159353  uh_Multi_Site
159402  g_Multi_Site
159347  sd_Multi_Site
159396  dfg_Multi_Site
159413  try_Multi_Site
159390  fgd_Multi_Site
159382  ghj_Multi_Site
159356  ertdsf_Multi_Site
159393  eg_Multi_Site
159350  jh_Multi_Site
159399  sdf_Multi_Site
159414  uhy_Multi_Site
159391  fg_Multi_Site
159385  R1_Multi_Site
159408  xcv_Multi_Site
159394  fgd_Multi_Site
159351  jh_Multi_Site
159388  fdg_Multi_Site
159411  gh_Multi_Site
159417  ui_Multi_Site
159354  ert_Multi_Site
159348  sd_Multi_Site
159405  gtr_Multi_Site
159397  tyu_Multi_Site
159389  tyu_Multi_Site
159412  tf_Multi_Site
159383  ergt_Multi_Site
159380  sdfsdf_Multi_Site
159406  gtr_Multi_Site
159400  sdf_Multi_Site
159349  jh_Multi_Site
159392  fg_Multi_Site
159409  gh_Multi_Site
159386  fdg_Multi_Site
159346  sd_Multi_Site
159352  uh_Multi_Site
159387  fdg_Multi_Site
159418  ty_Multi_Site
159404  gtr_Multi_Site
159335  gh_Multi_Site
159398  gr_Multi_Site
159415  fdg_Multi_Site
159384  R1_Multi_Site
159421  r_Multi_Site
159401  sdf_Multi_Site
159381  sdfsdf_Multi_Site
159395  sfgd_Multi_Site
159407  xcv_Multi_Site

1 个解决方案

#1

6  

Data in a table is not inherently ordered you have to provide some way via a value or some logic to create a specific order. There is no way to guaranteed order unless you specify an ORDER BY.

表中的数据本身并不是有序的,您必须通过值或某些逻辑来提供某种方式来创建特定的订单。除非您指定ORDER BY,否则无法保证订单。

But you should be able to return a single row for each idtask with row_number(). The problem with your original version is you aren't filtering the data to return only a single row and the row_number() isn't partitioned by the idtask values:

但是你应该能够使用row_number()为每个idtask返回一行。您的原始版本的问题是您没有过滤数据只返回一行而row_number()没有被idtask值分区:

select idtask, 
    taskname
from 
(
  select idtask, 
    taskname, 
    rn = ROW_NUMBER() OVER (partition by idtask order by idtask) 
  from #temprajesh
) d
where rn = 1;

See Demo. The subquery will create your unique value for each row in the idtask but then you'll only return one row.

见演示。子查询将为idtask中的每一行创建唯一值,但之后您只返回一行。

#1

6  

Data in a table is not inherently ordered you have to provide some way via a value or some logic to create a specific order. There is no way to guaranteed order unless you specify an ORDER BY.

表中的数据本身并不是有序的,您必须通过值或某些逻辑来提供某种方式来创建特定的订单。除非您指定ORDER BY,否则无法保证订单。

But you should be able to return a single row for each idtask with row_number(). The problem with your original version is you aren't filtering the data to return only a single row and the row_number() isn't partitioned by the idtask values:

但是你应该能够使用row_number()为每个idtask返回一行。您的原始版本的问题是您没有过滤数据只返回一行而row_number()没有被idtask值分区:

select idtask, 
    taskname
from 
(
  select idtask, 
    taskname, 
    rn = ROW_NUMBER() OVER (partition by idtask order by idtask) 
  from #temprajesh
) d
where rn = 1;

See Demo. The subquery will create your unique value for each row in the idtask but then you'll only return one row.

见演示。子查询将为idtask中的每一行创建唯一值,但之后您只返回一行。

标签：

相关文章