列转行之后行数显示问题请教

[qq_29845565]

表如下：

create table work (姓名 varchar(20),性别 varchar(20),班次 varchar(20))
insert into work values ('小明','男','早班')
insert into work values ('小李','女','早班')
insert into work values ('小陈','男','早班')
insert into work values ('小康','男','中班')
insert into work values ('小孙','男','中班')
insert into work values ('小王','男','晚班')
insert into work values ('小刘','女','晚班')

执行下面语句，进行列转行之后为以下：

select 班次,男,女 from work pivot (count(姓名) for 性别 in (男,女))t

语句中并没有加 distinct ，为什么不显示成如下效果：
班次 男 女
晚班 1 1
晚班 1 1
早班 2 1
早班 2 1
早班 2 1
中班 2 0
中班 2 0

[blueink_200451]

什么是聚合函数？感谢楼主分享。

[不穿内裤的超人]

你总共7条数据，显示下面那个统计就多了，count(姓名) 是计算人数了

[顾西昂]

因为pivot后加的是聚合函数要聚合的列

[qq_29845565]

引用 2 楼 SugarToffee 的回复:

因为pivot后加的是聚合函数要聚合的列

但班次那一列中，所有的行应该都还有啊，怎么少了

[中国风]

这是语法限制，不能直接sum/max/min的情况下，需要这样处理

SELECT 班次,[男]=ISNULL([男],0),[女]=ISNULL([女],0) FROM (SELECT 性别,班次,COUNT(姓名) AS 姓名 FROM dbo.work GROUP BY 性别,班次) AS A PIVOT (max(姓名) for 性别 in ([男],[女]))t
/*
班次	男	女
晚班	1	1
早班	2	1
中班	2	0
*/

[qq_29845565]

我意思是这条语句：

select 班次,男,女 from work pivot (count(姓名) for 性别 in (男,女))t

在未加distinct 的情况下，就应该是显示出这个效果： 班次 男 女 晚班 1 1 晚班 1 1 早班 2 1 早班 2 1 早班 2 1 中班 2 0 中班 2 0 但实际上为什么不是呢？

[中国风]

引用 5 楼 qq_29845565 的回复:

我意思是这条语句：

select 班次,男,女 from work pivot (count(姓名) for 性别 in (男,女))t

在未加distinct 的情况下，就应该是显示出这个效果： 班次 男 女 晚班 1 1 晚班 1 1 早班 2 1 早班 2 1 早班 2 1 中班 2 0 中班 2 0 但实际上为什么不是呢？

认真看#4回复

[中国风]

COPY语法限制内容: aggregate_function 接受一个或多个输入的系统聚合函数或用户定义的聚合函数。聚合函数应该对 Null 值固定不变。对 Null 值固定不变的聚合函数在求聚合值时不考虑组中的 Null 值。 不允许使用 COUNT(*) 系统聚合函数。

[qq_29845565]

引用 4 楼 roy_88 的回复:

这是语法限制，不能直接sum/max/min的情况下，需要这样处理

SELECT 班次,[男]=ISNULL([男],0),[女]=ISNULL([女],0) FROM (SELECT 性别,班次,COUNT(姓名) AS 姓名 FROM dbo.work GROUP BY 性别,班次) AS A PIVOT (max(姓名) for 性别 in ([男],[女]))t
/*
班次	男	女
晚班	1	1
早班	2	1
中班	2	0
*/

上面语句中你定义的A表如下：
SELECT 性别,班次,COUNT(姓名) AS 姓名 FROM dbo.work GROUP BY 性别,班次
显示为：
男 晚班 1
女 晚班 1
男 早班 2
女 早班 1
男 中班 2

为什么进行PIVOT转换后结果不是以下这样，“班次”那一列所有的行都应该是保留的，因为没加distinct ，为什么行变少了？
班次 男 女
晚班 1 1
晚班 1 1
早班 2 1
早班 2 1
中班 2 0

[中国风]

上面用法和语法都有提供就是不愿意去看？？ 要懂基础知识，学一门语言，那最基本的语法都不去遵守，那你也别学了

[qq_29845565]

要是会我还发贴问啊，这话能看明白吗，什么意思啊 COPY语法限制内容: aggregate_function 接受一个或多个输入的系统聚合函数或用户定义的聚合函数。聚合函数应该对 Null 值固定不变。对 Null 值固定不变的聚合函数在求聚合值时不考虑组中的 Null 值。 不允许使用 COUNT(*) 系统聚合函数。

[中国风]

聚合函数--把非聚合函数GROUP BY ，不可能出现重复分组，你这样的显示只能写死SQL语句 以上PIVOT等同于

SELECT
班次
,COUNT(CASE WHEN 性别=N'男' THEN 姓名 END) AS 男
,COUNT(CASE WHEN 性别=N'女' THEN 姓名 END) AS 女
FROM dbo.work
GROUP BY 班次

处理在分组加一列区别，数据会显示全为1，比如：

;WITH work2
AS
(
SELECT *,RK=ROW_NUMBER()OVER(PARTITION BY 班次 ORDER BY 班次) FROM dbo.work
)
SELECT  班次
       ,[男] = ISNULL([男] , 0)
       ,[女] = ISNULL([女] , 0)
FROM    work2 PIVOT ( COUNT(姓名) FOR 性别 IN ( [男] , [女] ) ) t;

/* 班次 男 女 晚班 1 0 晚班 0 1 早班 1 0 早班 0 1 早班 1 0 中班 1 0 中班 1 0 */

[qq_29845565]

我就应该不懂装懂，说你说的我全明白了，其实根本不明白，我要问的什么是不是你没明白啊？

[中国风]

首先看一下PIVOT的语法和例子，语法和含义要搞清楚，什么是聚合函数？

[中国风]

参照用法例子： http://bbs.csdn.net/topics/240002706 看完你应该可以掌握用法

[qq_29845565]

你看我这个写法有错误是吗？

select 班次,男,女 from work pivot (count(姓名) for 性别 in (男,女))t

[中国风]

引用 15 楼 qq_29845565 的回复:

你看我这个写法有错误是吗？

select 班次,男,女 from work pivot (count(姓名) for 性别 in (男,女))t

你没理解用法，语法没问题，同你要显示的效果完全两回事，明白，聚合函数就是把相同的分组合并pivot实现功能就是等同于 sum(case when ... )--简化了 你要的这样的效果？是在什么情况下有这样的要求，会出现么？

班次    男    女
晚班    1     1
晚班    1     1
早班    2     1
早班    2     1
中班    2     0

[qq_29845565]

1、以上写法如果没问题说明我知道PIVOT的用法，算知道大概的用法吧。 2、我不是要得到以下效果，按我的写法是出不来以下这个效果的

         班次    男    女
          晚班    1     1
          晚班    1     1
          早班    2     1
          早班    2     1
          早班    2     1
          中班    2     0
          中班    2     0

3、但我不明白为什么此语句

select 班次,男,女 from work pivot (count(姓名) for 性别 in (男,女))t

执行后为什么会显示成如下效果：

班次    男    女
晚班    1    1
早班    2    1
中班    2    0

而不是显示成2中的效果，因为只是对姓名进行了聚合，性别分成男女两列，但班次并没有动啊，应该是全都显示出来，就是班次中6行内容应该全显示出来，是2中的效果的才对，然后加distinct后才显示为3的效果，是进行了PIVOT后自动去掉了重复的项目吗，还是我没理解对呀？我应该说明白了吧

[qq_29845565]

也就是说我执行：

select 班次,男,女 from work pivot (count(姓名) for 性别 in (男,女))t

我觉得应该显示成以下才对：

班次    男    女
          晚班    1     1
          晚班    1     1
          早班    2     1
          早班    2     1
          早班    2     1
          中班    2     0
          中班    2     0

但其实不是，为什么会显示成这样呢？

班次    男    女
晚班    1    1
早班    2    1
中班    2    0

[中国风]

#12已回复PIVOT等同于，SQL2005提供这函数就是为了简化以下语句，功能相同 也就是聚合函数+GROUP BY用法的简化

SELECT
班次
,COUNT(CASE WHEN 性别=N'男' THEN 姓名 END) AS 男
,COUNT(CASE WHEN 性别=N'女' THEN 姓名 END) AS 女
FROM dbo.work
GROUP BY 班次

可这样理解，红色部分再行转列，红色部分不出现重复就不会重复显示 SELECT 班次 ,[男] = ISNULL([男] , 0) ,[女] = ISNULL([女] , 0) FROM ( SELECT 性别 ,班次 ,COUNT(姓名) AS 姓名 FROM dbo.work GROUP BY 性别 ,班次 ) AS A PIVOT ( MAX(姓名) FOR 性别 IN ( [男] , [女] ) ) t;