“独特” SQL 查询中的列

发布于 2024-09-10 14:27:47 字数 680 浏览 5 评论 0原文

选择 ID、员工编号来自员工表

EmpNo 可以相同。我现在想添加另一个从 EmpNo 派生的列（让我们称之为 EmpNo2），但只返回 EmpNo 的不同值。

例如，如果上述查询返回 100 条记录，但有 69 个不同的 EmpNo 值，并且我将查询修改为

SELECT id, EmpNo, Distinct EmpNo2
FROM  EmployeesTable EmpNo

，我希望返回所有 100 行，但最后一列 EmpNo2 应返回 EmpNo 字段的 69 个不同值。

但正如已经知道的那样，以这种方式使用不同的结果会导致错误，但我想实现这样的功能 - 并且子查询没有帮助。

所需结果样本

   ID  EmpNo   EmpNo2

    1  0T4/HR 0T4/HR
    1  0T4/HR 2VP/E
    1  0T4/HR xT9/67
    1  0T4/HR 
    1  0T4/HR 
    2  2VP/E 
    2  2VP/E
    2  2VP/E 
    2  2VP/E 
    2  2VP/E 
    3  XT9/67 
    3  XT9/67 
    3  xT9/67 
    3  XT9/67

原文

SELECT id, EmpNo
FROM EmployeesTable

EmpNo can be the same for 1 or more records in the results of the above query. I now want to add another column derived from EmpNo(lets call it EmpNo2) but only returning distinct values of EmpNo.

For example if the above query returns 100 records but there are 69 distinct EmpNo values and i modify the query to

SELECT id, EmpNo, Distinct EmpNo2
FROM  EmployeesTable EmpNo

,
i want all the 100 rows to be returned but the last column EmpNo2 should return 69 distinct values of EmpNo field.

But as already know, using distinct in that way results into an error but i want to implement such functionality - and a subquery is not helping.

SAMPLE REQUIRED RESULTS

   ID  EmpNo   EmpNo2

    1  0T4/HR 0T4/HR
    1  0T4/HR 2VP/E
    1  0T4/HR xT9/67
    1  0T4/HR 
    1  0T4/HR 
    2  2VP/E 
    2  2VP/E
    2  2VP/E 
    2  2VP/E 
    2  2VP/E 
    3  XT9/67 
    3  XT9/67 
    3  xT9/67 
    3  XT9/67

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

多彩岁月 2024-09-17 14:27:47

怎么样：

Select id, empno, empno2
from employeestable left outer join (
 SELECT min([id]) as minid
      ,[empno] empno2
  FROM [EmployeesTable]
group by empno) etab2 on employeestable.id = etab2.minid

你说子查询不起作用，但为什么不呢？

How about:

Select id, empno, empno2
from employeestable left outer join (
 SELECT min([id]) as minid
      ,[empno] empno2
  FROM [EmployeesTable]
group by empno) etab2 on employeestable.id = etab2.minid

You're saying a subquery won't work, though - why not?

回复收藏 0 原文

时光磨忆 2024-09-17 14:27:47

你的要求不明确，我的信息也很少。以下是您所需要的。这可能会更好，但这只是一种尝试。

declare @temp table
(
    uniqueid int identity(1, 1),
    id int,
    empno varchar(50),
    empno2 varchar(50)
)

insert into @temp  select   1,  '0T4/HR', null
insert into @temp select     1,  '0T4/HR' , null
insert into @temp select     1 , '0T4/HR' , null
insert into @temp select     1,  '0T4/HR' , null
insert into @temp select     1,  '0T4/HR'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E' , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     3,  'XT9/67'  , null
insert into @temp select     3,  'XT9/67'  , null
insert into @temp select     3,  'xT9/67'  , null
insert into @temp select     3,  'XT9/67'  , null

SELECT ROW_NUMBER() OVER (ORDER BY id) AS id, empno into #temp FROM @temp group by empno, id

update @temp set empno2 = t2.empno
from @temp t inner join #temp t2 on t.uniqueid = t2.id

select * from @temp

drop table #temp

Your requirement is not clear and I also have very little information. Following is what you need. This can be even better but it is just a try.

declare @temp table
(
    uniqueid int identity(1, 1),
    id int,
    empno varchar(50),
    empno2 varchar(50)
)

insert into @temp  select   1,  '0T4/HR', null
insert into @temp select     1,  '0T4/HR' , null
insert into @temp select     1 , '0T4/HR' , null
insert into @temp select     1,  '0T4/HR' , null
insert into @temp select     1,  '0T4/HR'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E' , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     3,  'XT9/67'  , null
insert into @temp select     3,  'XT9/67'  , null
insert into @temp select     3,  'xT9/67'  , null
insert into @temp select     3,  'XT9/67'  , null

SELECT ROW_NUMBER() OVER (ORDER BY id) AS id, empno into #temp FROM @temp group by empno, id

update @temp set empno2 = t2.empno
from @temp t inner join #temp t2 on t.uniqueid = t2.id

select * from @temp

drop table #temp

回复收藏 0 原文

~没有更多了~