合并求取分组记录的第一条数据

有朋友问，

关于电影记录的查询SQL，突然不知道如何下手，给个提示参考一下啊，谢谢。

比如，table_mv 表记录电影的基础信息，包括三个字段 名称，链接，归类

变形金刚第一部  电影链接1  归类(变形金刚)。
变形金刚第二部  电影链接2  归类(变形金刚)。
变形金刚第三部  电影链接3  归类(变形金刚)。
终结者  电影链接A  归类。

在查询时规则如下，如果归类为空，则正常，如果归类有值，则汇总，
名称为归类名称，连接默认为第一个，
查询结果为：

变形金刚  电影链接1  归类(变形金刚)。
终结者  电影链接A  归类。

---------------------------------

当时初略的想了想，可以用分组取出来，但写了下又不行，于是按照需求，创建一个表并且插入数据来测试：

CREATE TABLE MovieInfo
(
  Name varchar(50) not null,
  Link varchar(250) not null,
  MovieType varchar(20)
)

GO

INSERT INTO MovieInfo VALUES ('变形金刚第一部','电影链接1','变形金刚');
INSERT INTO MovieInfo VALUES ('变形金刚第二部','电影链接2','变形金刚');
INSERT INTO MovieInfo VALUES ('变形金刚第三部','电影链接3','变形金刚');
INSERT INTO MovieInfo VALUES ('终结者','电影链接A',NULL);
INSERT INTO MovieInfo VALUES ('英雄','电影链接A1',NULL);

INSERT INTO MovieInfo VALUES ('007第一部','电影链接11','007');
INSERT INTO MovieInfo VALUES ('007第二部','电影链接12','007');
INSERT INTO MovieInfo VALUES ('007第三部','电影链接13','007');

GO

SELECT * FROM MovieInfo;

先写一个分组并求分组后的记录大于1的查询：

select MovieType from MovieInfo
   where MovieType is not null 
      group by MovieType
         having  count(*) >1 

结果是：

MovieType
007
变形金刚

根据这个查询可以进一步取到对应的名字：

select top 1 MovieType, Name  from  MovieInfo where MovieType  in
(
select MovieType from MovieInfo
   where MovieType is not null 
      group by MovieType
         having  count(*) >1 
)
group by MovieType,Name

无奈结果不是预期的那样，只有1条记录：

MovieType    Name
007    007第二部

在同事的指导下，说ROW_NUMBER() 可以在给记录编号的同时指定分组，然后我们取分组中编号为1的记录即可，先来分组编号，看看记录情况：

 

SELECT ROW_NUMBER() OVER(partition by MovieType ORDER BY Name) as row, * FROM MovieInfo   WHERE MovieType is not null

结果是：

row    Name    Link    MovieType
1    007第二部    电影链接12    007
2    007第三部    电影链接13    007
3    007第一部    电影链接11    007
1    变形金刚第二部    电影链接2    变形金刚
2    变形金刚第三部    电影链接3    变形金刚
3    变形金刚第一部    电影链接1    变形金刚

OK，现在可以给出完整的查询了，这个查询需要用到联合查询，统计那些没有分组的记录：

select Name,Link,MovieType from (
SELECT ROW_NUMBER() OVER(partition by MovieType ORDER BY Name) as row, * FROM MovieInfo   WHERE MovieType is not null
) t
where t.row=1
union
SELECT Name,Link,MovieType FROM MovieInfo WHERE MovieType is null

结果是：

Name    Link    MovieType
007第二部    电影链接12    007
变形金刚第二部    电影链接2    变形金刚
英雄    电影链接A1    NULL
终结者    电影链接A    NULL

后来想想，如果没有Row_Number 怎么办?同事说可以利用聚合函数或者自查询，下面是给出的2条SQL语句，功能一样：

select * from MovieInfo a where name=(select  MIN(name) from MovieInfo where MovieType=a.MovieType)
select * from MovieInfo a where name=(select TOP 1 name from MovieInfo where MovieType=a.MovieType)

由于常常使用ORM，很久没有练习SQL了，这里做一个笔记，感谢同事的指导。

下面是我今天练习这个SQL查询的PDF.NET集成开发工具，该工具可以在官网找到下载链接。