使用Row_number()OVER(partition BY ..)以及声明局部变量 [英] Using Row_number() OVER(partition BY..) along with declaring local variables
问题描述
我有一个sql查询,在此sql查询中,我想选择与众不同的列,而不考虑列在前.对于其他SQL查询,我使用Row_number()OVER(partition BY ..),并且我还需要使用内部联接.我要在其中使用row_number和内部联接的查询是-
I have a sql query, in this sql query I want to select distinct columns irrespective of column first. For other sql query I use Row_number() OVER(partition BY..) and I also need to use inner join. The query in which I want to use row_number and inner join is -
DECLARE @columns NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX) = '@columns NVARCHAR(MAX) OUTPUT'
DECLARE @sql NVARCHAR(MAX) = 'SELECT @columns = STUFF(
(
SELECT '',''+ [column_name] FROM information_schema.columns
WHERE (table_schema = ''dbo''
AND table_name = ''main_mps_dqs_analog'')
AND (ordinal_position <= 73) FOR XML PATH('''')),1,1,'''')'
EXEC sp_executesql @sql, @params, @columns OUTPUT
SET @sql = 'SELECT '+ @columns + ' FROM dbo.main_mps_dqs_analog WHERE logtime BETWEEN ''2014-10-10 07:17:00'' AND ''2014-10-10 08:47:00'''
EXEC(@sql)
我想将此表的内部联接与INDUS2_BDS.dbo.ddtable
一起使用,并且我希望此INDUS2
数据库表的beam_current
和logtime
以及如何在此查询中应用分区BY beam_current
.
I want to apply inner join of this table with INDUS2_BDS.dbo.ddtable
and I want beam_current
and logtime
of this INDUS2
database table and how to apply partition BY beam_current
in this query.
推荐答案
SET @sql = 'SELECT ' + @columns + ' ,AnotherTable.beam_current, RowNumber() Over(Partition By SomeColumn Order By SomeColumn) AS Rn
FROM dbo.TableName join AnotherTable on TableName.SomeColumn = AnotherTable.SomeColumn
WHERE logtime BETWEEN ''2014-10-10 07:17:00'' AND ''2014-10-10 08:47:00'''
我在Giorgi的帮助下通过此sql查询解决了该问题
I solved it by this sql query with the help from Giorgi
这篇关于使用Row_number()OVER(partition BY ..)以及声明局部变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!