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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 02:20:30  来源:igfitidea点击:

Sql group rows with same value, and put that value into header?

sql

提问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 BYevery 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