商品统计中很NB的SQL需要

liaoyukun111 2011-01-18 05:22:15
Create table test
(ids bigint identity(1,1) primary key,
names nvarchar(20),--名字
classify nvarchar(10),--分类
years int,--年
months int,--月
num bigint--数量
)
go
--测试数据(我看高手都喜欢这样写)-------
insert into test select 'phone1','A','2009',1,100 union all
select 'phone1','A','2009',2,200 union all
select 'phone1','A','2009',3,300 union all
select 'phone1','A','2009',4,400 union all
select 'phone1','A','2009',5,500 union all
select 'phone1','A','2009',6,600 union all
select 'phone1','A','2009',7,700 union all
select 'phone1','A','2009',8,800 union all
select 'phone1','A','2009',9,900 union all
select 'phone1','A','2009',10,1000 union all
select 'phone1','A','2009',11,1100 union all
select 'phone1','A','2009',12,1200 union all
select 'phone1','A','2010',1,100 union all
select 'phone1','A','2010',2,200 union all
select 'phone1','A','2010',3,300 union all
select 'phone1','A','2010',4,400 union all
select 'phone1','A','2010',5,500 union all
select 'phone1','A','2010',6,600 union all
select 'phone1','A','2010',7,700 union all
select 'phone1','A','2010',8,800 union all
select 'phone1','A','2010',9,900 union all
select 'phone1','A','2010',10,1000 union all
select 'phone1','A','2010',11,1100 union all
select 'phone1','A','2010',12,1200 union all
select 'phone1','B','2009',1,100 union all
select 'phone1','B','2009',2,200 union all
select 'phone1','B','2009',3,300 union all
select 'phone1','B','2009',4,400 union all
select 'phone1','B','2009',5,500 union all
select 'phone1','B','2009',6,600 union all
select 'phone1','B','2009',7,700 union all
select 'phone1','B','2009',8,800 union all
select 'phone1','B','2009',9,900 union all
select 'phone1','B','2009',10,1000 union all
select 'phone1','B','2009',11,1100 union all
select 'phone1','B','2009',12,1200 union all
select 'phone1','B','2010',1,100 union all
select 'phone1','B','2010',2,200 union all
select 'phone1','B','2010',3,300 union all
select 'phone1','B','2010',4,400 union all
select 'phone1','B','2010',5,500 union all
select 'phone1','B','2010',6,600 union all
select 'phone1','B','2010',7,700 union all
select 'phone1','B','2010',8,800 union all
select 'phone1','B','2010',9,900 union all
select 'phone1','B','2010',10,1000 union all
select 'phone1','B','2010',11,1100 union all
select 'phone1','B','2010',12,1200

go
---------------------------我的问题---------------
同比是:本月的和去年的本月比
环比是:(本月的-上月)/上月 要求2个小数位
累计是:当年每个月的数据加起来
要求得到 条件只有年和月,假若条件是:2010年5月,则结果
名字 数量(A),数量(B),当月同比(A),当月同比(B),当月环比(A),当月环比(B),累计数量(A),累计数量(B),累计当月同比(A),累计当月同比(B),累计当月环比(A),累计当月环比(B)
phone1 500 500 1 1 0.25 0.25 6500 12 0+1/1+1/2+1/3+1/4+1/5+1/6+1/7+1/8+1/9+1/10+1/11
求MSSQL2005语句怎么写(注,从个人来说,需要是比较BUG的,不知大师们可有NB方法)
...全文
150 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
liaoyukun111 2011-01-19
  • 打赏
  • 举报
 回复
果然是体力活 不过很容易看懂 哈哈 修改去了
liaoyukun111 2011-01-19
  • 打赏
  • 举报
 回复
好的 感谢爱新觉罗.毓华
这是很好的例子 再有问题我会再发帖子的
dawugui 2011-01-18
  • 打赏
  • 举报
 回复
就是一堆子查询而已,如果我的写法不对,你自己参考着修改一下即可.
dawugui 2011-01-18
  • 打赏
  • 举报
 回复 
Create table test

(ids bigint identity(1,1) primary key,

names nvarchar(20),--名字

classify nvarchar(10),--分类

years int,--年

months int,--月

num bigint--数量

)

insert into test select 'phone1','A','2009',1,100 union all

  select 'phone1','A','2009',2,200 union all

  select 'phone1','A','2009',3,300 union all

  select 'phone1','A','2009',4,400 union all

  select 'phone1','A','2009',5,500 union all

  select 'phone1','A','2009',6,600 union all

  select 'phone1','A','2009',7,700 union all

  select 'phone1','A','2009',8,800 union all

  select 'phone1','A','2009',9,900 union all

  select 'phone1','A','2009',10,1000 union all

  select 'phone1','A','2009',11,1100 union all

  select 'phone1','A','2009',12,1200 union all  

  select 'phone1','A','2010',1,100 union all

  select 'phone1','A','2010',2,200 union all

  select 'phone1','A','2010',3,300 union all

  select 'phone1','A','2010',4,400 union all

  select 'phone1','A','2010',5,500 union all

  select 'phone1','A','2010',6,600 union all

  select 'phone1','A','2010',7,700 union all

  select 'phone1','A','2010',8,800 union all

  select 'phone1','A','2010',9,900 union all

  select 'phone1','A','2010',10,1000 union all

  select 'phone1','A','2010',11,1100 union all

  select 'phone1','A','2010',12,1200 union all  

  select 'phone1','B','2009',1,100 union all

  select 'phone1','B','2009',2,200 union all

  select 'phone1','B','2009',3,300 union all

  select 'phone1','B','2009',4,400 union all

  select 'phone1','B','2009',5,500 union all

  select 'phone1','B','2009',6,600 union all

  select 'phone1','B','2009',7,700 union all

  select 'phone1','B','2009',8,800 union all

  select 'phone1','B','2009',9,900 union all

  select 'phone1','B','2009',10,1000 union all

  select 'phone1','B','2009',11,1100 union all

  select 'phone1','B','2009',12,1200 union all  

  select 'phone1','B','2010',1,100 union all

  select 'phone1','B','2010',2,200 union all

  select 'phone1','B','2010',3,300 union all

  select 'phone1','B','2010',4,400 union all

  select 'phone1','B','2010',5,500 union all

  select 'phone1','B','2010',6,600 union all

  select 'phone1','B','2010',7,700 union all

  select 'phone1','B','2010',8,800 union all

  select 'phone1','B','2010',9,900 union all

  select 'phone1','B','2010',10,1000 union all

  select 'phone1','B','2010',11,1100 union all

  select 'phone1','B','2010',12,1200  



go



declare @year as int

declare @month as int

set @year = 2010

set @month = 5



select 名字 = names,

       分类 = classify ,

       数量 = m.num ,

       当月同比 = cast(m.num * 1.0 / (select num from test n where n.names = m.names and n.classify = m.classify and n.years = @year - 1 and n.months = @month) as decimal(18,2)), 

       当月环比 = cast((m.num - (select top 1 num from test n where n.names = m.names and n.classify = m.classify and ltrim(n.years) + right('0'+ltrim(n.months),2) < ltrim(m.years) + right('0'+ltrim(m.months),2) order by n.years desc , n.months desc))*1.0/

                  (select top 1 num from test n where n.names = m.names and n.classify = m.classify and ltrim(n.years) + right('0'+ltrim(n.months),2) < ltrim(m.years) + right('0'+ltrim(m.months),2) order by n.years desc , n.months desc) as decimal(18,2)),

       累计数量 = (select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years and n.months <= m.months),

       累计当月同比 = cast((select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years and n.months <= m.months) * 1.0 / 

                      (select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years - 1 and n.months <= m.months) as decimal(18,2)),

       累计当月环比 = cast(((select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years and n.months <= m.months) - (select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years - 1 and n.months <= m.months))*1.0/

                      (select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years - 1 and n.months <= m.months) as decimal(18,2))

from test m 

where m.years = @year and m.months = @month



drop table test



/*

名字                   分类         数量                   当月同比                 当月环比                 累计数量                 累计当月同比               累计当月环比               

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

phone1               A          500                  1.00                 .25                  1500                 1.00                 .00

phone1               B          500                  1.00                 .25                  1500                 1.00                 .00



（所影响的行数为 2 行）

*/
lester19872007 2011-01-18
  • 打赏
  • 举报
 回复
嗯，我发现这个和我现在做的一个项目里面的数据统计功能很想!!晚上抽时间看看！
liaoyukun111 2011-01-18
  • 打赏
  • 举报
 回复
liaoyukun111 2011-01-18
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 wxf163 的回复:]
体力活，把分给一个人吧，我就不写了
[/Quote]

写前6个一样给全分,要是可以后面6个再开一个帖子
AcHerat 2011-01-18
  • 打赏
  • 举报
 回复
我out了，看晕了！
xiaozheyou 2011-01-18
  • 打赏
  • 举报
 回复
heh 是够体力
王向飞 2011-01-18
  • 打赏
  • 举报
 回复
体力活，把分给一个人吧，我就不写了
网上购物系统
这种预先判断的处理方式，很大的程度上解决了非法输入对后面的存储所带来的一系列问题。 <p><B>&nbsp;</B></P> <p><B>3)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </B><B>DelAll</B><B>模式（清空购物篮） &nbsp; </B></P> 这...
每天一道大厂SQL题【Day03】订单量统计
相信大家和我一样，都有一个大厂梦，作为一名资深大数据选手，深知SQL重要性，接下来我准备用100天时间，基于大数据岗面试中的经典SQL题，以每日1题的形式，带你过一遍热门SQL题并给出恰如其分的解答。一路走来，...
【Flink】Flink SQL之路
让小伙伴萌能直观明白博主的心思博主会以实际的应用场景和案例入手，不只是知识点的简单堆砌博主会把重要的知识点的原理进行剖析，让小伙伴萌做到深入浅出呕心沥血，Flink SQL 成神之路出品。小伙伴萌可以先体验一下...
SQL数据库练习（编程题）
表中每个商品种类对应的最高销售价格,查询出其中最高销售价格大于100的商品信息，要求显示结果中包含符合条件的商品种类及对应的最高销售单价。本题目要求编写SQL语句，查询显示一周8节课及以上的学生的名单，显示...
学习笔记：SQL大厂面试题集
题源代码来自：​​​​​​数据分析-全网最全SQL面试真题(做完，面试再也不虚了-划分了知名互联网大厂、不同行业背景（如电商、游戏）、常考知识点三大部分的SQL真题）_哔哩哔哩_bilibili欢迎大家一起交流学习，...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章