SQL将具有相同值的行分组，并将该值放入标头？

小编典典

SQL将具有相同值的行分组，并将该值放入标头？

sql

我想用SQL对行进行分组，我的结果集如下

name size date
data1 123 12/03/2009
data1 124 15/09/2009
data2 333 02/09/2010
data2 323 02/11/2010 2010
data2 673 2014/02/09
data2 444 05/01/2010

我想对结果集进行这样的分组：

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

用纯SQL可以做到这一点吗？

干杯。

阅读 160

2021-05-05

共1个答案

小编典典

GROUP BY WITH ROLLUP（您并不是真正地分组-因此您将切实地在GROUP BY每一列中进行分组）

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://databases.about.com/od/sql/l/aacuberollup.htm

http://www.adp-gmbh.ch/ora/sql/group_by/group_by_rollup.html

http://msdn.microsoft.com/en-
us/library/bb522495.aspx

根据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

2021-05-05