如何在-TSQL 2008中实现以下输出? [英] how to achieve the below output in -TSQL 2008?
本文介绍了如何在-TSQL 2008中实现以下输出?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
新年快乐,
i有一个要求,为此我发送我的表格结构,并提到预期的输出。
请看下面的表格结构
创建 表 [dbo]。[ACTVCODE](
[actv_code_id] [< span class =code-keyword> int ] NOT NULL ,
[actv_code_type_id] [ int ] NOT NULL ,
[短名称] [ varchar ]( 60 ) NOT NULL ,
[actv_code_name] [ varchar ]( 120 ) NULL ,
)
INSERT INTO [ACTVCODE] VALUES ( 8855 , 2760 ,' CCPS_T',' Miletones ')
INSERT INTO [ACTVCODE] VALUES ( 8856 , 2761 ,' CPP',' 2 * 70MW发电厂')
SELECT * FROM ACTVCODE
CREATE TABLE [dbo]。[TASKACTV](
[task_id] [< span class =code-keywor d> int ] NOT NULL ,
[actv_code_type_id] [ int ] NOT NULL ,
[ actv_code_id] [ int ] NOT NULL ,
[proj_id] [ int ] NOT NULL ,
)
INSERT INTO [TASKACTV] VALUES ( 571647 , 2760 ,' 8855', 11346 )
INSERT INTO [TASKACTV] VALUES ( 571647 , 2761 ,' 8856', 11346 )
SELECT * FROM TASKACTV
SELECT TASKACTV.actv_code_id,ACTVCODE.short_name,ACTVCODE .actv_code_name,TASKACTV.task_id
FROM ACTVCODE INNER JOIN
TASKACTV ON ACTVCODE.actv_code_id = TASKACTV.actv_code_id
- 必需的输出-----------
task_id short_name short_name1(别名)
571647 CCPS_T CPP
请提供解决方案。
先谢谢
解决方案
尝试
SELECT T.Taskid,A1.Short_name,A2.Short_name AS [短名称]
FROM TASKACTIVE T
INNER JOIN ACTVCODE A1 ON T.Activity_code_id = A1.activitycode_Type_id
INNER JOIN ACTVCODE A2 ON T.Activity_code_id = A2.activitycode_Type_id
终于我解决了这个问题。如需指导,请参考。
; cte as (
选择 task_id,short_name,ROW_NUMBER() over ( partition by task_id order by a.actv_code_id) as rid
来自 ACTVCODE a
inner join [TASKACTV] b on a.actv_code_id = b.actv_code_id
)
select a.task_id,a.short_name,b .short_name as short_name1
来自 cte a
内部 joi n cte b a.task_id = b.task_id 和 a.rid = b。 RID-1
Hi All ,
Happy new year to all ,
i got one requirement, for this i am sending my tables structure and also mentioned expected output .
kindly have a look on the below tables structure
CREATE TABLE [dbo].[ACTVCODE](
[actv_code_id] [int] NOT NULL,
[actv_code_type_id] [int] NOT NULL,
[short_name] [varchar](60) NOT NULL,
[actv_code_name] [varchar](120) NULL,
)
INSERT INTO [ACTVCODE] VALUES (8855,2760,'CCPS_T','Miletones')
INSERT INTO [ACTVCODE] VALUES (8856,2761,'CPP','2*70MW power plant')
SELECT * FROM ACTVCODE
CREATE TABLE [dbo].[TASKACTV](
[task_id] [int] NOT NULL,
[actv_code_type_id] [int] NOT NULL,
[actv_code_id] [int] NOT NULL,
[proj_id] [int] NOT NULL,
)
INSERT INTO [TASKACTV] VALUES (571647,2760,'8855',11346)
INSERT INTO [TASKACTV] VALUES (571647,2761,'8856',11346)
SELECT * FROM TASKACTV
SELECT TASKACTV.actv_code_id, ACTVCODE.short_name, ACTVCODE.actv_code_name, TASKACTV.task_id
FROM ACTVCODE INNER JOIN
TASKACTV ON ACTVCODE.actv_code_id = TASKACTV.actv_code_id
--REQUIRED OUTPUT-----------
task_id short_name short_name1(alias name)
571647 CCPS_T CPP
kindly provide the solution.
Thanks in Advance
解决方案
Try
SELECT T.Taskid, A1.Short_name, A2.Short_name AS [Short-Names] FROM TASKACTIVE T INNER JOIN ACTVCODE A1 ON T.Activity_code_id = A1.activitycode_Type_id INNER JOIN ACTVCODE A2 ON T.Activity_code_id = A2.activitycode_Type_id
Hi ,
finally i resolved the issue my self. for guidance please refer it.
;with cte as ( select task_id, short_name, ROW_NUMBER() over(partition by task_id order by a.actv_code_id) as rid from ACTVCODE a inner join [TASKACTV] b on a.actv_code_id = b.actv_code_id ) select a.task_id, a.short_name, b.short_name as short_name1 from cte a inner join cte b on a.task_id = b.task_id and a.rid = b.rid-1
这篇关于如何在-TSQL 2008中实现以下输出?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文