22,210
社区成员
发帖
与我相关
我的任务
分享想了半天，弄不出来，期待高手解答。。。
CREATE TABLE [dbo].[mytest](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[months] [int] NOT NULL,
[powermoney] [money] NOT NULL,
[createdatetime] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO mytest VALUES(1,5,600,'2011-05-30')
INSERT INTO mytest VALUES(1,6,700,'2011-06-30')
INSERT INTO mytest VALUES(2,5,800,'2011-05-30')
INSERT INTO mytest VALUES(3,6,900,'2011-06-30')
INSERT INTO mytest VALUES(1,3,300,'2011-04-30')
INSERT INTO mytest VALUES(3,4,300,'2011-04-30')
INSERT INTO mytest VALUES(2,3,400,'2011-03-30')
GO
--想达到的效果。如下运行结果
SELECT
userid as '用户',
max(CASE months WHEN '5' THEN months ELSE 0 END) AS '月份',
MAX(CASE months WHEN '5' THEN powermoney ELSE 0 END) AS '费用',
max(CASE months WHEN '6' THEN months ELSE 0 END) AS '月份',
MAX(CASE months WHEN '6' THEN powermoney ELSE 0 END) AS '费用'
FROM mytest
GROUP BY userid
--结果
--用户 月份 费用 月份 费用
--1 5 600.00 6 700.00
--2 5 800.00 0 0.00
--3 0 0.00 6 900.00
--所存在问题：上面SQL是根据ID分组，取了5、6月份的数据，结果userID=2时，六月份的数据为空，和ID=3时，五月份的数据为空。
--我想达到的效果是，如果userID=2时，如果没有六月份数据，那么得查出前面几个月，离他最近的数据。
--（也就是如果六月不存在，则填充五月的，五月也没找到，就填空四月的，以此类推，如果全找不到才填空值）
--
------------
--根据数据填充想到结果如下
--用户 月份 费用 月份 费用
--1 5 600.00 6 700.00
--2 5 800.00 3 400.00
--3 4 300.00 6 900.00
--谢谢了。。
...全文
请发表友善的回复…
发表回复
cd731107 2011-07-15
- 打赏
- 举报
CREATE TABLE [dbo].[mytest](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[months] [int] NOT NULL,
[powermoney] [money] NOT NULL,
[createdatetime] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO mytest VALUES(1,5,600,'2011-05-30')
INSERT INTO mytest VALUES(1,6,700,'2011-06-30')
INSERT INTO mytest VALUES(2,5,800,'2011-05-30')
INSERT INTO mytest VALUES(3,6,900,'2011-06-30')
INSERT INTO mytest VALUES(1,3,300,'2011-04-30')
INSERT INTO mytest VALUES(3,4,300,'2011-04-30')
INSERT INTO mytest VALUES(2,3,400,'2011-03-30')
GO
--原语句稍做修改
SELECT
userid as '用户',
isnull(max(CASE months WHEN '5' THEN months END),
(select top 1 months from mytest b where b.userid=a.userid and months <5 order by months desc)) AS '月份',
isnull(MAX(CASE months WHEN '5' THEN powermoney END),
(select top 1 powermoney from mytest b where b.userid=a.userid and months <5 order by months desc)) AS '费用',
isnull(max(CASE months WHEN '6' THEN months END),
(select top 1 months from mytest b where b.userid=a.userid and months <5 order by months desc)) AS '月份',
isnull(MAX(CASE months WHEN '6' THEN powermoney END),
(select top 1 powermoney from mytest b where b.userid=a.userid and months <5 order by months desc)) AS '费用'
FROM mytest a
GROUP BY userid
/*
用户 月份 费用 月份 费用
----------------------------------------------------------
1 5 600.0000 6 700.0000
2 5 800.0000 3 400.0000
3 4 300.0000 6 900.0000
（所影响的行数为 3 行）
*/
AcHerat 2011-07-14
- 打赏
- 举报
create table [dbo].[mytest](
[id] [int] identity(1,1) not null,
[userid] [int] not null,
[months] [int] not null,
[powermoney] [money] not null,
[createdatetime] [datetime] not null
) on [primary]
go
set nocount on
insert into mytest values(1,5,600,'2011-05-30')
insert into mytest values(1,6,700,'2011-06-30')
insert into mytest values(2,5,800,'2011-05-30')
insert into mytest values(3,6,900,'2011-06-30')
insert into mytest values(1,3,300,'2011-04-30')
insert into mytest values(3,4,300,'2011-04-30')
insert into mytest values(2,3,400,'2011-03-30')
set nocount off
go
declare @sql varchar(8000)
declare @start datetime
declare @end datetime
set @start = '2011-05-30' --起始日期
set @end = '2011-06-30' --截至日期
set @sql = 'select t.userid as [用户]'
select @sql = @sql + ',max(case t.months when ' + ltrim(months) + ' then t.months else isnull(g.months,0) end) [月份]'
+',max(case t.months when ' + ltrim(months) + ' then t.powermoney else isnull(g.powermoney,0) end) [费用]'
from(select distinct months from [mytest] where createdatetime between @start and @end)t
select @sql = @sql + ' from mytest t left join ('
+'select * from mytest e where not exists('
+'select 1 from mytest where userid = e.userid and createdatetime < '''
+convert(varchar(23),@start,120)+''' and createdatetime < e.createdatetime)'
+' and createdatetime < '''+convert(varchar(23),@start,120)+''') g on t.userid = g.userid'
+' where t.createdatetime between '''
+convert(varchar(23),@start,120)+''' and '''+convert(varchar(23),@end,120)+''' group by t.userid'
exec(@sql)
drop table [mytest]
/***************
用户 月份 费用 月份 费用
----------- ----------- --------------------- ----------- ---------------------
1 5 600.00 6 700.00
2 5 800.00 3 400.00
3 4 300.00 6 900.00
(3 行受影响)
qgqch2008 2011-07-14
- 打赏
- 举报
SELECT a.userid AS '用户',
max(CASE a.Row WHEN 1 THEN months ELSE 0 END) AS '月份',
MAX(CASE a.Row WHEN 1 THEN powermoney ELSE 0 END) AS '费用',
max(CASE a.Row WHEN 2 THEN months ELSE 0 END) AS '月份',
MAX(CASE a.Row WHEN 2 THEN powermoney ELSE 0 END) AS '费用'
from
(
SELECT ROW_NUMBER() OVER (PARTITION BY userid ORDER BY userid,CASE months WHEN 5 THEN 14 WHEN 6 THEN 13 ELSE months END DESC) AS Row,*
FROM dbo.mytest WHERE months<7
)a GROUP BY a.useridqgqch2008 2011-07-14
- 打赏
- 举报
SELECT a.userid AS '用户',
max(CASE a.Row WHEN 1 THEN months ELSE 0 END) AS '月份',
MAX(CASE a.Row WHEN 1 THEN powermoney ELSE 0 END) AS '费用',
max(CASE a.Row WHEN 2 THEN months ELSE 0 END) AS '月份',
MAX(CASE a.Row WHEN 2 THEN powermoney ELSE 0 END) AS '费用'
from
(
SELECT ROW_NUMBER() OVER (PARTITION BY userid ORDER BY userid,CASE months WHEN 5 THEN -1 WHEN 6 THEN 0 ELSE months END) AS Row,*
FROM dbo.mytest
)a GROUP BY a.userid
/*
用户 月份 费用 月份 费用
----------- ----------- --------------------- ----------- ---------------------
1 5 600.00 6 700.00
2 5 800.00 3 400.00
3 6 900.00 4 300.00
(3 行受影响)
*/
you_tube 2011-07-14
- 打赏
- 举报
select *
from (
select *,row_number() over(order by a.suerid) rownumber
from A,B
where a.userid = b.userid
and (case when a.month is not null and b.month is null then a.month else b.month) >= b.month
or (case when b.month is not null and a.month is null then a.month else b.month) <= a.month
) A
where rownumber = 1类似这种解决方案自己去搞掂吧
tashiwoweiyi 2011-07-14
- 打赏
- 举报
[Quote=引用 3 楼 bin_520_yan 的回复:]
仅仅只要5月和6月份的数据吗
[/Quote]
需求肯定不会这样，但现在就按5、6月份来取，如果有空、找不到的、则取他小的月份填充。
仅仅只要5月和6月份的数据吗
[/Quote]
需求肯定不会这样，但现在就按5、6月份来取，如果有空、找不到的、则取他小的月份填充。
DataBox-MDX 2011-07-14
- 打赏
- 举报
仅仅只要5月和6月份的数据吗
AcHerat 2011-07-14
- 打赏
- 举报
下班，回去看。。。。
小笨熊 2011-07-14
- 打赏
- 举报
sf，帮楼主顶
