想要加入他们的第一个组合的每个人,即如果有超过1个缺少记录,那么你构建逻辑。 [英] Want each individual person who joined in their first comp i.e if there were more than 1 lacks records then who u build logic.
本文介绍了想要加入他们的第一个组合的每个人,即如果有超过1个缺少记录,那么你构建逻辑。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
场景:
名称比较DOJ
Kris Aa 2010
Naik Bb 2013
kris Cc 2011
娜娜Dd 2014
输出:想要参加第一次比赛的每个人,即如果有超过1个缺少记录,那么你是谁构建逻辑。
以上示例输出为:
Kris 2010 2011
Naik 2013
Nana 2014
构建上述结果的逻辑。
解决方案
不确定你想要什么,但你可以使用Pivot [ ^ ]表。看一下示例代码:
DECLARE @ tmp 表([名称] VARCHAR ( 30 ),Comp VARCHAR ( 30 ),DOJ INT )
INSERT INTO @ tmp ([Name],Comp,DOJ)
VALUES ( ' Kris',' Aa', 2010 ),
(' Naik',' Bb', 2013 ),
(' Kris',' Cc', 2011 ),
(' Nana',' Dd', 2014 )
SELECT [名称],[ 1 ],[ 2 ]
FROM (
SELECT ROW_NUMBER() OVER ( PARTITION BY [名称] ORDER BY [名称],DOJ) AS RowID,[Name],DOJ
FROM @ tmp
) AS DT
PIVOT(MAX(DOJ) FOR [RowID] IN ([ 1 ],[ 2 ])) AS PT
结果:
姓名 < b> 1 2
Kris 2010 2011
Naik 2013 NULL
娜娜2014年空白
更多:
在SQL查询中使用Pivot的简单方法 [ ^ ]
在SQL Server 2005中使用动态列进行支持 [ ^ ]
Scenario:
Name Comp DOJ
Kris Aa 2010
Naik Bb 2013
kris Cc 2011
Nana Dd 2014
Output: Want each individual person who joined in their first comp i.e if there were more than 1 lacks records then who u build logic.
Above example output is:
Kris 2010 2011
Naik 2013
Nana 2014
Build the logic for the above result.
解决方案
Not sure what you want, but you can use Pivot[^] table. Have a look at sample code:
DECLARE @tmp TABLE([Name] VARCHAR(30), Comp VARCHAR(30), DOJ INT) INSERT INTO @tmp ([Name], Comp, DOJ) VALUES('Kris', 'Aa', 2010), ('Naik', 'Bb', 2013), ('Kris', 'Cc', 2011), ('Nana', 'Dd', 2014) SELECT [Name], [1], [2] FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY [Name], DOJ) AS RowID, [Name], DOJ FROM @tmp ) AS DT PIVOT (MAX(DOJ) FOR [RowID] IN([1], [2])) AS PT
Result:
Name 1 2 Kris 2010 2011 Naik 2013 NULL Nana 2014 NULL
More:
Simple Way To Use Pivot In SQL Query[^]
Pivots with Dynamic Columns in SQL Server 2005[^]
这篇关于想要加入他们的第一个组合的每个人,即如果有超过1个缺少记录,那么你构建逻辑。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文