面试时遇到一个这样的sql题如下:
表sales
年份 | 季度 | 金额 |
1991 | 1 | 11 |
1991 | 2 | 22 |
1991 | 3 | 33 |
1991 | 4 | 44 |
1992 | 1 | 55 |
1992 | 2 | 66 |
1992 | 3 | 77 |
1992 | 4 | 88 |
查询结果如下:
年份 | 第一季度 | 第二季度 | 第三季度 | 第四季度 |
1991 | 11 | 22 | 33 | 44 |
1992 | 55 | 66 | 77 | 88 |
请写出sql语句。
当我看到这样的问题时我的内心是崩溃的,一点思路都没有!回到家赶紧查上网查了一下,记录备忘。
建表:
CREATE TABLE [dbo].[sales](
[id] [int] IDENTITY(1,1) NOT NULL,
[year] [int] NULL,
[jidu] [int] NULL,
[jine] [int] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
有两种方法:
一、使用case when 实现:
SELECT
year as '年份',
max(CASE jidu WHEN 1 THEN jine END) AS '第一季度',
max(CASE jidu WHEN 2 THEN jine END) AS '第二季度',
max(CASE jidu WHEN 3 THEN jine END) AS '第三季度',
max(CASE jidu WHEN 4 THEN jine END) AS '第四季度'
FROM sales group by year
很容易理解,但写起来很麻烦。下面介绍下一种。
二、使用pivot函数:
使用pivot函数时须注意
对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时,必须将数据库的兼容级别设置为 90 或更高。
参考:
https://technet.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx
PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。
PIVOT的语法:
SELECT <非透视的列>,
[第一个透视的列] AS <列名称>,
[第二个透视的列] AS <列名称>,
...
[最后一个透视的列] AS <列名称>,
FROM
(<生成数据的 SELECT 查询>)
AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ( [第一个透视的列], [第二个透视的列],
... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;
按照上述语法写出sql:
select year as 年份,[1] as 第一季度,[2] as 第二季度,[3] as 第三季度,[4] as 第四季度 from sales pivot (SUM(jine) FOR jidu IN ([1],[2],[3],[4]))a
看着就清秀,执行结果如下:
这是什么鬼……
其实很明显,分组出现了问题,但是按照语法
只能指定需要横向显示的列即聚合函数中的列和要成为列标题的值的列,不能明确定义分组列。用作分组的列是PIVOT函数中没有应用到的列(即 字段 id),会根据id和year两个字段分组。
注意源查询中是可以使用select语句的,PIVOT可以操作结果集,因此只需要将源查询中不显示id这个字段就可以了:
select year as 年份,[1] as 第一季度,[2] as 第二季度,[3] as 第三季度,[4] as 第四季度 from (select year,jidu,jine from sales) as t1 pivot (SUM(jine) FOR jidu IN ([1],[2],[3],[4])) as a --'t1'和'a'这两个别名必须得有要不会报错。
执行结果:
终于对了。
- 大小: 7 KB
- 大小: 3.6 KB
分享到:
相关推荐
Sql语句实现表的行列转换,行转列,列转行
将列值旋转为列名(即行转列)是我们在开发中经常会遇到的一个需要,下面这篇文章主要给大家介绍了关于sql语句实现行转列的3种方法,分别给出了详细的示例代码,需要的朋友可以参考借鉴,下面来一起学习学习吧。
使用sql语句实现动态行转列,建表后导入excel测试数据
主要是关于sqlserver的多行转列的问题,这是我经过个人测试的语句。
SQL语句实现行转列的几种方法示例,在SQL查询中经常需要行转列,文档中详细介绍了几种方法并对比
sql 常用语句汇总
sql语言让数据行变列显示,简单的sql语句就能实现
1. 列转行 2. 行转列 3. 多列转换成字符串 4. 多行转换成字符串 5. 字符串转换成多列 6. 字符串转换成多行
10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1...
通过sql语句实现分组小计功能,看看测试代码,认真分析
导入excel可以直接将数据转成SQL语句,其中字段名就是excel文档每列第一行的内容。该工具虽然是以.jar结尾,可以直接双击使用。只要是做开发的孩纸,看一下就会用的。
找了网上很多资料,都没有详细的做法有的还很复杂,自己写了个分割函数,通过直接查询就能分割多行,很方便。
SQLServer行转列的sql语句有很多,可以使用静态的、动态的、Case When等等,均能实现
10、几个简单的基本的sql语句 11、几个高级查询运算词 12、使用外连接 13、分组:Group by: 14、对数据库进行操作: 15.如何修改数据库的名称: 第二部分、 提升 1、复制表 2、拷贝表 3、跨数据库之间表的...
4.3.3 修改数据表中的列 4.3.4 删除数据表中的列 4.4 数据表的删除 4.4.1 删除数据表的语句结构 4.4.2 使用SQL语句删除数据表 4.5 数据表的重命名 4.5.1 重命名数据表的语句 4.5.2 使用SQL语句重命名数据表 4.6 小结...
1.新建一数据表,里面有字段id,将id设为为主键 代码如下: create table tb(id int,constraint pkid primary key (id)) create table tb(id int primary key ) 2.新建一数据表,里面有字段id,将id设为主键且自动...
随机选择数据库记录的方法(使用Randomize函数,通过SQL语句实现) 对存储在数据库中的数据来说,随机数特性能给出上面的效果,但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决...
VB6.0编程实现SQL数据任意条件查询 基于VB6.0编程实现SQL数据任意条件查询
数据库中的某字段的值像(4,5,6,9,15),查询出包含字符"5"的记录,像(6,9,15)这样的不算。
mybatisPlus的queryWrapper查询条件我们一般都是写死带下划线(如:create_time),但是如果用@FieldNameConstants注解的files字段去当条件的话,是驼峰,sql语句报找不到列,所以期望这里会自动帮我们把驼峰转下划线...