本文介绍了如何在sql中对具有相同值的行进行分组?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在sql中对具有相同值的行进行分组?
data1 123 12/03/2009 124 15 / 09/2009 data2 333 02/09/2010 323 02/11/2010 673 02/09/2014 444 05/01/2010解决方案
这个
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ('data1',123,'12 / 03/2009'),('data1',124,'15 / 09/2009'),('data2',333,'02 / ('data2',323,'02 / 11/2010'),('data2',673,'02 / 09/2014'),('data2',444' 05/01/2010') SELECT (CASE rno WHEN 1 THEN col1 ELSE''END)as col1, col2, col3 FROM ( SELECT ROW_NUMBER()OVER(由COL1 ORDER BY COL2分区)AS rno, col1,col2,col3 FROM @temp )作为临时这给出以下输出:
col1 col2 col3 ------------------------ --------- data1 123 12/03/2009 124 15/09/2009 data2 323 02/11/2010 333 02/09 / 2010 444 05/01/2010 673 02/09/2014PARTITION BY 将数据与给定的列名分组,并根据顺序在该组中生成一个行号。
以下是 SQL小提琴
我根据提供的模式创建了另一个小提琴 fiddle2
How to group rows with same value in sql?
data1 123 12/03/2009 124 15/09/2009 data2 333 02/09/2010 323 02/11/2010 673 02/09/2014 444 05/01/2010 解决方案Try this
DECLARE @temp TABLE(col1 varchar(20),col2 int, col3 varchar(20)) insert into @temp values ('data1', 123 , '12/03/2009'),('data1', 124 , '15/09/2009'), ('data2 ',333 ,'02/09/2010'),('data2 ',323 , '02/11/2010'), ('data2 ',673 , '02/09/2014'),('data2',444 , '05/01/2010') SELECT (CASE rno WHEN 1 THEN col1 ELSE '' END )AS col1, col2, col3 FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY col2) AS rno, col1,col2,col3 FROM @temp ) As tempThis gives the following output
col1 col2 col3 --------------------------------- data1 123 12/03/2009 124 15/09/2009 data2 323 02/11/2010 333 02/09/2010 444 05/01/2010 673 02/09/2014PARTITION BY is grouping the data with the given column name, and a row number is generated in that group based on the order by.
Here is the SQL Fiddle
I have created another fiddle based on the schema provided .fiddle2
更多推荐
如何在sql中对具有相同值的行进行分组?
发布评论