1.用SQL语句建立下面这张表:月份 地区 销量
1 A 123
2 A 234
3 B 456
4 B 789
2.基于上表,分别做按月份、地区及月份和地区的分类汇总
3.基于上表,建立这样一个查询,实现交叉表的功能如:地区 1月 2月 3月
4.建立查询销量,计算累计销量,如:月份 地区 销量
1 A 123
2 A 234+123
3 B 456+234+123
4 B 789+456+234+123
5.建立一个自定义函数,要求传入年份、月份,返回该月最后一天
1 A 123
2 A 234
3 B 456
4 B 789
2.基于上表,分别做按月份、地区及月份和地区的分类汇总
3.基于上表,建立这样一个查询,实现交叉表的功能如:地区 1月 2月 3月
4.建立查询销量,计算累计销量,如:月份 地区 销量
1 A 123
2 A 234+123
3 B 456+234+123
4 B 789+456+234+123
5.建立一个自定义函数,要求传入年份、月份,返回该月最后一天
IF OBJECT_ID('FUN_GETDAY') IS NOT NULL DROP FUNCTION FUN_GETDAY
GO
CREATE FUNCTION FUN_GETDAY(@YEAR INT,@MONTH INT)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(DAY,-1,RIGHT('0000'+CAST(@YEAR AS VARCHAR(10)),4)+RIGHT('00'+CAST(@MONTH+1 AS VARCHAR(10)),2)+'01')
END
GO
SELECT DBO.FUN_GETDAY(2009,2)
--2009-02-28 00:00:00.000
SELECT DBO.FUN_GETDAY(2008,2)
--2008-02-29 00:00:00.000
-- 第四题
DECLARE @T TABLE (M TINYINT,A VARCHAR(50),S FLOAT)
INSERT INTO @T(M,A,S)
SELECT 1,'A',123
UNION ALL
SELECT 2 , 'A', 234
UNION ALL
SELECT 3 , 'B', 456
UNION ALL
SELECT 4 , 'B', 789
;
SELECT M AS [月份],A AS [地区],(SELECT SUM(S) FROM @T B WHERE B.M<=A.M) AS [销量] FROM @T A
以后就会了!
if object_id('addr') is not null drop table addr
create table addr(月份 int , 地区 varchar(10),销量 int)
insert into addr
select 1 , 'A' , 123 union all
select 2 , 'A' , 234 union all
select 3 , 'B' , 456 union all
select 4 , 'B' , 789 select 月份 , 地区 , 累计销量=(select sum(销量) from addr b where a.月份 > = b.月份)
from addr a
DECLARE @T TABLE (M TINYINT,A VARCHAR(50),S FLOAT)
INSERT INTO @T(M,A,S)
SELECT 1,'A',123
UNION ALL
SELECT 2 , 'A', 234
UNION ALL
SELECT 3 , 'B', 456
UNION ALL
SELECT 4 , 'B', 789
;
SELECT A
,SUM(CASE WHEN M = 1 THEN s ELSE 0 END) AS '1月'
,SUM(CASE WHEN M = 2 THEN s ELSE 0 END) AS '2月'
,SUM(CASE WHEN M = 3 THEN s ELSE 0 END) AS '3月'
FROM @T
GROUP BY A
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
NO2
CREATE TABLE test
(月份 INT ,地区 VARCHAR( 10),销量 INT )INSERT [test]
SELECT 1,'A',123 UNION ALL
SELECT 2,'A',234 UNION ALL
SELECT 3,'B',456 UNION ALL
SELECT 4,'B',789 SELECT * FROM [test] SELECT 月份,SUM(销量) FROM [test] GROUP BY 月份
SELECT 地区,SUM(销量) FROM [test] GROUP BY 地区
SELECT 月份,地区,SUM (销量) FROM [test] GROUP BY 月份,地区
RETURNS DATETIME
AS
BEGIN
if(@MONTH=12)
begin
set @YEAR=@YEAR+1
set @MONTH=0
end
RETURN DATEADD(DAY,-1,RIGHT('0000'+CAST(@YEAR AS VARCHAR(10)),4)+RIGHT('00'+CAST(@MONTH+1 AS VARCHAR(10)),2)+'01')
END
GO
/*----------------------------------
瘦狼阿亮 | 钱不是问题,问题是没钱
------------------------------------
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1
(Build 2600: Service Pack 3)
----------------------------------*/--1.用SQL语句建立下面这张表:CREATE TABLE tmpTable
(recordMonth tinyint
,Region varchar(20)
,SaleAmount int)/*--表字段说明--
月份-recordMonth
地区-Region
销量-SaleAmount
*/--插入测试数据
INSERT INTO tmpTable
SELECT 1,'A',123 UNION ALL
SELECT 2,'A',234 UNION ALL
SELECT 3,'B',456 UNION ALL
SELECT 4,'B',789
/*执行结果:
(4 行受影响)
*/--检查插入的测试数据
SELECT recordMonth,Region,SaleAmount FROM tmpTable
/*执行结果:
recordMonth Region SaleAmount
----------- -------------------- -----------
1 A 123
2 A 234
3 B 456
4 B 789(4 行受影响)
*/--2.基于上表,分别做按月份、地区及月份和地区的分类汇总
--按月份汇总
SELECT
recordMonth,SaleAmount=SUM(ISNULL(SaleAmount,0))
FROM
tmpTable
GROUP BY
recordMonth
/*执行结果:
recordMonth SaleAmount
----------- -----------
1 123
2 234
3 456
4 789(4 行受影响)
*/--按地区汇总
SELECT
Region,SaleAmount=SUM(ISNULL(SaleAmount,0))
FROM
tmpTable
GROUP BY
Region
/*执行结果:
Region SaleAmount
-------------------- -----------
A 357
B 1245(2 行受影响)
*/--按月份和地区汇总
SELECT
recordMonth,Region,SaleAmount=SUM(ISNULL(SaleAmount,0))
FROM
tmpTable
GROUP BY
recordMonth,Region
/*执行结果:
recordMonth Region SaleAmount
----------- -------------------- -----------
1 A 123
2 A 234
3 B 456
4 B 789(4 行受影响)
*/--3.基于上表,建立这样一个查询,实现交叉表的功能
--如:地区 1月 2月 3月
SELECT
Region,
Jan=SUM(CASE recordMonth WHEN 1 THEN ISNULL(recordMonth,0) ELSE 0 END),
Feb=SUM(CASE recordMonth WHEN 2 THEN ISNULL(recordMonth,0) ELSE 0 END),
Mar=SUM(CASE recordMonth WHEN 3 THEN ISNULL(recordMonth,0) ELSE 0 END)
FROM
tmpTable
GROUP BY
Region
--这个实际上是一个行转列的问题,详细的可以在论坛里面搜索一下,有很多这方面的帖子的
/*执行结果:
Region Jan Feb Mar
-------------------- ----------- ----------- -----------
A 1 2 0
B 0 0 3(2 行受影响)
*/--4.建立查询销量,计算累计销量
SELECT
recordMonth,Region,
TotalAmount=(SELECT SUM(SaleAmount) FROM tmpTable b WHERE a.recordMonth >= b.recordMonth)
from
tmpTable a
/*执行结果:
recordMonth Region TotalAmount
----------- -------------------- -----------
1 A 123
2 A 357
3 B 813
4 B 1602(4 行受影响)
*/--第五个有人做出来了,就不整了
--删除表
DROP TABLE tmpTable