Sql 将具有相同值的行分组,并将该值放入标题中?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/956065/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql group rows with same value, and put that value into header?
提问by vaske
I want to group rows with SQL, my result set is following
我想用 SQL 对行进行分组,我的结果集如下
name size date
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
名称 大小 日期
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/4014/201
I want to group result set like this one:
我想像这样对结果集进行分组:
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
124 15/09/2009
DATA2
333 02/09/2010
323 02/11/2010
673 2014年2月9日
444 05/01/2010
is it possible to do this with pure SQL?
是否可以使用纯 SQL 来做到这一点?
Cheers.
干杯。
回答by Cade Roux
GROUP BY WITH ROLLUP
(you're not really grouping - so you would actaully GROUP BY
every column)
GROUP BY WITH ROLLUP
(你并没有真正分组 - 所以你实际上会GROUP BY
每一列)
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
http://chiragrdarji.wordpress.com/2008/09/09/group-by-cube-rollup-and-sql-server-2005/
http://chiragrdarji.wordpress.com/2008/09/09/group-by-cube-rollup-and-sql-server-2005/
http://databases.about.com/od/sql/l/aacuberollup.htm
http://databases.about.com/od/sql/l/aacuberollup.htm
http://www.adp-gmbh.ch/ora/sql/group_by/group_by_rollup.html
http://www.adp-gmbh.ch/ora/sql/group_by/group_by_rollup.html
http://msdn.microsoft.com/en-us/library/bb522495.aspx
http://msdn.microsoft.com/en-us/library/bb522495.aspx
Based on Lieven's code:
基于 Lieven 的代码:
DECLARE @Table TABLE (
name varchar(32)
,Size integer
,Date datetime
)
INSERT INTO @Table
VALUES ('data1', 123, GETDATE())
INSERT INTO @Table
VALUES ('data1', 124, GETDATE())
INSERT INTO @Table
VALUES ('data2', 333, GETDATE())
INSERT INTO @Table
VALUES ('data2', 323, GETDATE())
INSERT INTO @Table
VALUES ('data2', 673, GETDATE())
INSERT INTO @Table
VALUES ('data2', 444, GETDATE())
SELECT *
FROM (
SELECT *
FROM @Table
GROUP BY NAME
,size
,date
WITH ROLLUP
) AS X
WHERE NAME IS NOT NULL
AND (
(
Size IS NOT NULL
AND Date IS NOT NULL
)
OR (
Size IS NULL
AND date IS NULL
)
)
ORDER BY NAME
,size
,date
回答by Jules Bartow
Shaping the data ahead of time simplifies things for the application developer, especially if all they're doing is displaying a static report with no interactive editing, sorting, or paging --a common enough occurrence.
提前对数据进行整形为应用程序开发人员简化了工作,尤其是当他们所做的只是显示没有交互式编辑、排序或分页的静态报告时——这种情况很常见。
Lieven's solution (selecting the columns distinctively while inserting nulls for other columns, then checking for the nulls of the other columns) is the only solution here that actually works. Kind of.
Lieven 的解决方案(在为其他列插入空值时有区别地选择列,然后检查其他列的空值)是这里唯一真正有效的解决方案。的种类。
data1 NULL NULL
NULL 123 2011-05-24 19:42:29.577
NULL 124 2011-05-24 19:42:29.577
data2 NULL NULL
NULL 323 2011-05-24 19:42:29.577
NULL 333 2011-05-24 19:42:29.577
NULL 444 2011-05-24 19:42:29.577
NULL 673 2011-05-24 19:42:29.577
--unless the column you're nullalizing already has nulls, in which case we're back to square 0. Add one more row.
--除非您要归零的列已经有空值,在这种情况下,我们将返回到第 0 方格。再添加一行。
INSERT INTO @Table VALUES ('data2', NULL, getdate())
Now run the query again. Bummer dude.
现在再次运行查询。无赖的家伙。
data1 NULL NULL
NULL 123 2011-05-24 19:53:36.437
NULL 124 2011-05-24 19:53:36.437
data2 NULL NULL
data2 NULL 2011-05-24 19:53:36.440
NULL 323 2011-05-24 19:53:36.440
NULL 333 2011-05-24 19:53:36.440
NULL 444 2011-05-24 19:53:36.440
Roux's proposed ROLLUP solution doesn't work at all, at least not in SQL Server. Actually, it makes things more worserer.
Roux 提出的 ROLLUP 解决方案根本不起作用,至少在 SQL Server 中不起作用。实际上,它使事情变得更糟。
data1 NULL NULL
data1 NULL NULL
data1 NULL NULL
data1 123 2011-05-24 20:16:26.693
data1 124 2011-05-24 20:16:26.693
data2 NULL NULL
data2 NULL NULL
data2 NULL NULL
data2 323 2011-05-24 20:16:26.693
data2 333 2011-05-24 20:16:26.693
data2 444 2011-05-24 20:16:26.693
data2 673 2011-05-24 20:16:26.693
Piti?' ROWNUM solution may work in Oracle (I haven't tried it and there appears to a missing beginning parentheses), but the equivalent SQL Server code using ROW_NUMBER() OVER definitely taint working no good neither --about as well as my grammer and speling.
皮蒂?ROWNUM 解决方案可能在 Oracle 中工作(我还没有尝试过,并且似乎缺少开头括号),但是使用 ROW_NUMBER() OVER 的等效 SQL Server 代码肯定不起作用 --about 以及我的语法和拼写.
SELECT
ROW_NUMBER() OVER(ORDER BY [name]) AS [rown]
, name
, ''
, ''
FROM @Table
GROUP BY name
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY [name]) + 1 AS [rown] , name, size, date
FROM @Table
produces
产生
data1 NULL NULL
data1 NULL NULL
data1 NULL NULL
data1 123 2011-05-24 20:16:26.693
data1 124 2011-05-24 20:16:26.693
data2 NULL NULL
data2 NULL NULL
data2 NULL NULL
data2 323 2011-05-24 20:16:26.693
data2 333 2011-05-24 20:16:26.693
data2 444 2011-05-24 20:16:26.693
data2 673 2011-05-24 20:16:26.693
As a software professional your job is to ensure billions of 1s and 0s line up at the right place, in the right order, at the right time. You know the details, often times down to an individual bit, are important.
作为一名软件专业人士,您的工作是确保数十亿个 1 和 0 在正确的时间以正确的顺序排列在正确的位置。您知道细节(通常是单个位)很重要。
A half vast answer is worse than no answer at all because it wastes everyone's time. So... no offense intended because intentions are good, but please at least test your "solution" before posting it as a "solution".
半个大的答案总比没有答案更糟糕,因为它浪费了每个人的时间。所以...无意冒犯,因为意图是好的,但请至少在将其发布为“解决方案”之前测试您的“解决方案”。
I would be perfect if I weren't so darned humble. And even I test.
如果我不是那么谦虚,我就完美了。甚至我测试。
回答by Lieven Keersmaekers
Michael Todd is definitly right when he says this should be done on the client side but for the fun of it, this is one option
当迈克尔托德说这应该在客户端完成时,他绝对是正确的,但为了它的乐趣,这是一种选择
DECLARE @Table TABLE (name VARCHAR(32), Size INTEGER, Date DATETIME)
INSERT INTO @Table VALUES ('data1', 123, getdate())
INSERT INTO @Table VALUES ('data1', 124, getdate())
INSERT INTO @Table VALUES ('data2', 333, getdate())
INSERT INTO @Table VALUES ('data2', 323, getdate())
INSERT INTO @Table VALUES ('data2', 673, getdate())
INSERT INTO @Table VALUES ('data2', 444, getdate())
INSERT INTO @Table
SELECT DISTINCT name, NULL, NULL
FROM @Table
SELECT
CASE WHEN Size IS NULL THEN Name ELSE NULL END
, Size
, Date
FROM @Table
ORDER BY Name, Size
回答by C?t?lin Piti?
There might be something like:
可能有这样的事情:
select name, size, date from
(
-- select only distinct rows, and empty values for size and date (header rows)
select ROWNUM rown, name, '', ''
from T
group by name
order by name
union all
-- select all data (non-header records)
select ROWNUM+1 rown, name, size, date
from T
order by name
)
order by name, rown
Explanation: First select from the union selects the records for the group header. It sorts the results by name. The row number gives the order. Second select from the union selects all the records for the group header. It sorts the results by name, and the row number gives the order. The union put all the information together. ROWNUM+1 for the second select ensures that records for the header (from the first select) are ordered before the detailed records.
说明:首先从联合中选择为组头选择记录。它按名称对结果进行排序。行号给出了顺序。联合中的第二个选择选择组标题的所有记录。它按名称对结果进行排序,行号给出顺序。工会把所有的信息放在一起。第二个选择的 ROWNUM+1 确保标题的记录(来自第一个选择)在详细记录之前排序。
Now... what you need to do and I don't recover so much SQL to know how to do it... is to put '' for name when size or date are '', in the main select (with a case/swich operation). Some help is needed here :).
现在......你需要做的是,我没有恢复太多的SQL来知道如何去做......当大小或日期为''时,在主选择中放置''作为名称(有一个案例/切换操作)。这里需要一些帮助:)。
Just as an observation, in the provided SQL, ROWNUM is a special column that provides the row number for a select (see Oracle for example).
作为观察,在提供的 SQL 中,ROWNUM 是一个特殊的列,它为选择提供行号(例如,参见 Oracle)。
The query is displayed just as principle, I am not 100% sure it works.
该查询显示为原则,我不是 100% 确定它有效。
Update:... thats a solution sketch. But I still believe this is a formatting problem, and not an SQL problem.
更新:...这是一个解决方案草图。但我仍然相信这是一个格式问题,而不是一个 SQL 问题。
回答by Umair Anwar
You can use two queries and then group/format in the application code.
您可以使用两个查询,然后在应用程序代码中分组/格式化。
First query
第一次查询
SELECT DISTINCT(name) AS group_name FROM TABLE ORDER BY name LIMIT 5;
Second query
第二次查询
SELECT size, date FROM TABLE WHERE name IN ('comma separated group_name values from firstQueryResult');
Application code
应用代码
for every row in firstQueryResult{
group_array[group_name] = secondQueryResult;
}
The resulting group_array will be like;
结果 group_array 将是这样的;
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