34,838
社区成员
发帖
与我相关
我的任务
分享结合一定的过滤条件,按月统计数据,无限制时间段,需要可以跨年度
表数据如下:
货品编号 数量 成本 日期
no qty cst dd
001 100 10 2016-01-02
001 20 2 2016-02-02
001 26 2.6 2016-02-03
002 30 28 2016-01-06
002 50 40 2016-03-05
003 120 80 2016-03-06
需要得到结果
货品编号 2016-01数量 2016-01成本 2016-02数量 2016-02成本 2016-03数量 2016-03成本
001 100 10 46 4.6
002 30 28 50 40
003 120 80
货品编号 数量 成本 日期
no qty cst dd
001 100 10 2016-01-02
001 20 2 2016-02-02
001 26 2.6 2016-02-03
002 30 28 2016-01-06
002 50 40 2016-03-05
003 120 80 2016-03-06
需要得到结果
货品编号 2016-01数量 2016-01成本 2016-02数量 2016-02成本 2016-03数量 2016-03成本
001 100 10 46 4.6
002 30 28 50 40
003 120 80
...全文
请发表友善的回复…
发表回复
顺势而为1 2017-06-30
- 打赏
- 举报
之前少了个GO
if not object_id(N'Tempdb..#Tmp_Tbl1') is null
drop table #Tmp_Tbl1
Go
Create table #Tmp_Tbl1(
no varchar(10),
qty int,
cst int,
dd Datetime)
go
INSERT INTO #Tmp_Tbl1
select '001',100,10,'2016-01-02' union all
select '001',20,2,'2016-02-02' union all
select '001',26,2.6,'2016-02-03' union all
select '002',30,28,'2016-01-06' union all
select '002',50,40,'2016-03-05' union all
SELECT '003',120,80,'2016-03-06'
--按no, 年,月汇总
if not object_id(N'Tempdb..#Tmp_Tbl') is null
drop table #Tmp_Tbl
Go
Select no,sum(qty) as qty,sum(cst) as cst,year(dd) as year,month(dd) as month
INTO #Tmp_Tbl
From #Tmp_Tbl1
Group By no,year(dd),month(dd)
--所有年月
if not object_id(N'Tempdb..#Tmp_YearMonth') is null
drop table #Tmp_YearMonth
Go
CREATE TABLE #Tmp_YearMonth (
List_ID int identity(1,1),
Year int,
Month int)
Insert into #Tmp_YearMonth Select Distinct Year,month From #Tmp_Tbl
--结果集
if not object_id(N'Tempdb..#Tmp_Result') is null
drop table #Tmp_Result
Go
CREATE TABLE #Tmp_Result (
No varchar(10))
Insert into #Tmp_Result Select Distinct No From #Tmp_Tbl
GO
DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Year int
DECLARE @Month int
DECLARE @colName01 as nvarchar(30)
DECLARE @colName02 as nvarchar(30)
DECLARE @minList_ID int
Select @minList_ID=min(List_ID) From #Tmp_YearMonth
WHILE @minList_ID is not null
Begin
Select @Year=Year,@Month=Month From #Tmp_YearMonth Where List_ID=@minList_ID
SET @colName01=N'数量'+dbo.intToStr(@year,4)+dbo.intToStr(@month,2)
SET @colName02=N'成本'+dbo.intToStr(@year,4)+dbo.intToStr(@month,2)
SET @sql=' Alter Table #Tmp_Result Add ' + @colName01+' int,'+ @colName02+' int '
exec (@sql)
set @sql=''
SET @sql=@sql+' Update #Tmp_Result '
+' Set '+@colName01+' =b.qty,'+ @colName02+'=cst '
+' From #Tmp_Result a '
+' Join #Tmp_Tbl b on a.no=b.no'
+' Where year='+cast(@year as varchar)
+' and Month='+cast(@Month as varchar)
--Print @sql
exec (@sql)
Select @minList_ID=MIN(List_ID) From #Tmp_YearMonth Where List_ID>@minList_ID
End
Select * From #Tmp_Result
顺势而为1 2017-06-30
- 打赏
- 举报
Create FUNCTION [dbo].[IntToStr]
(@IntValue int,@Lenth int)
RETURNS Nvarchar(20)
AS
BEGIN
Return REPLICATE('0',@Lenth-len(ltrim(rtrim(str( @IntValue))))) + ltrim(rtrim(str(@IntValue)))
END
顺势而为1 2017-06-30
- 打赏
- 举报
if not object_id(N'Tempdb..#Tmp_Tbl1') is null
drop table #Tmp_Tbl1
Go
Create table #Tmp_Tbl1(
no varchar(10),
qty int,
cst int,
dd Datetime)
go
INSERT INTO #Tmp_Tbl1
select '001',100,10,'2016-01-02' union all
select '001',20,2,'2016-02-02' union all
select '001',26,2.6,'2016-02-03' union all
select '002',30,28,'2016-01-06' union all
select '002',50,40,'2016-03-05' union all
SELECT '003',120,80,'2016-03-06'
--按no, 年,月汇总
if not object_id(N'Tempdb..#Tmp_Tbl') is null
drop table #Tmp_Tbl
Go
Select no,sum(qty) as qty,sum(cst) as cst,year(dd) as year,month(dd) as month
INTO #Tmp_Tbl
From #Tmp_Tbl1
Group By no,year(dd),month(dd)
--所有年月
if not object_id(N'Tempdb..#Tmp_YearMonth') is null
drop table #Tmp_YearMonth
Go
CREATE TABLE #Tmp_YearMonth (
List_ID int identity(1,1),
Year int,
Month int)
Insert into #Tmp_YearMonth Select Distinct Year,month From #Tmp_Tbl
--结果集
if not object_id(N'Tempdb..#Tmp_Result') is null
drop table #Tmp_Result
Go
CREATE TABLE #Tmp_Result (
No varchar(10))
Insert into #Tmp_Result Select Distinct No From #Tmp_Tbl
--Select * From #Tmp_Tbl
--Select * From #Tmp_Result
--Select * From #Tmp_YearMonth
DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Year int
DECLARE @Month int
DECLARE @colName01 as nvarchar(30)
DECLARE @colName02 as nvarchar(30)
DECLARE @minList_ID int
Select @minList_ID=min(List_ID) From #Tmp_YearMonth
WHILE @minList_ID is not null
Begin
Select @Year=Year,@Month=Month From #Tmp_YearMonth Where List_ID=@minList_ID
SET @colName01=N'数量'+dbo.intToStr(@year,4)+dbo.intToStr(@month,2)
SET @colName02=N'成本'+dbo.intToStr(@year,4)+dbo.intToStr(@month,2)
SET @sql=' Alter Table #Tmp_Result Add ' + @colName01+' int,'+ @colName02+' int '
exec (@sql)
set @sql=''
SET @sql=@sql+' Update #Tmp_Result '
+' Set '+@colName01+' =b.qty,'+ @colName02+'=cst '
+' From #Tmp_Result a '
+' Join #Tmp_Tbl b on a.no=b.no'
+' Where year='+cast(@year as varchar)
+' and Month='+cast(@Month as varchar)
--Print @sql
exec (@sql)
Select @minList_ID=MIN(List_ID) From #Tmp_YearMonth Where List_ID>@minList_ID
End
Select * From #Tmp_Result
zhouyuehai1978 2017-06-30
- 打赏
- 举报
--测试数据
IF NOT OBJECT_ID(N'Tempdb..#T') IS NULL
DROP TABLE #T
GO
CREATE TABLE #T
(
no VARCHAR(10),
qty INT,
cst INT,
dd DATETIME
)
GO
INSERT INTO #T
select '001',100,10,'2016-01-02' union all
select '001',20,2,'2016-02-02' union all
select '001',26,2.6,'2016-02-03' union all
select '002',30,28,'2016-01-06' union all
select '002',50,40,'2016-03-05' union all
SELECT '003',120,80,'2016-03-06'
IF NOT OBJECT_ID(N'Tempdb..#T1') IS NULL
DROP TABLE #T1
GO
CREATE TABLE #T1
(
no VARCHAR(10),
num INT,
col VARCHAR(20),
)
GO
INSERT INTO #T1
SELECT no,qty AS num,CONVERT(VARCHAR(7),dd,120)+'数量' AS col FROM #T AS t
UNION ALL
SELECT no,cst AS num,CONVERT(VARCHAR(7),dd,120)+'成本' AS col FROM #T AS t
GO
--测试数据完毕
DECLARE @colstr VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)
SELECT @colstr = STUFF((SELECT ',' + QUOTENAME(col)FROM #T1 GROUP BY col FOR XML PATH('')),1,1,'')
SET @sql = 'SELECT * FROM #T1 PIVOT(SUM(num) FOR col IN (' + @colstr + ')) AS P'
PRINT @sql
EXEC (@sql)
xxfvba 2017-02-13
- 打赏
- 举报
;WITH tab1(no,qty,cst,dd)AS(
select '001',100,10,'2016-01-02' union all
select '001',20,2,'2016-02-02' union all
select '001',26,2.6,'2016-02-03' union all
select '002',30,28,'2016-01-06' union all
select '002',50,40,'2016-03-05' union all
SELECT '003',120,80,'2016-03-06'
)
select * into #T from tab1
select * from #T
declare @s varchar(max)
select @s=ISNULL(@s+',','')+'sum(case when convert(varchar(7),dd,112)='''+convert(varchar(7),dd,112)+''' then qty end) as ['+convert(varchar(7),dd,112)+'数量],
sum(case when convert(varchar(7),dd,112)='''+convert(varchar(7),dd,112)+''' then cst end) as ['+convert(varchar(7),dd,112)+'成本]'
from #T group by convert(varchar(7),dd,112)
set @s='select no,'+@s+' from #T group by no'
exec(@s)
drop table #T
二月十六 版主 2017-02-10
- 打赏
- 举报
语句：
结果：
;WITH tab1(no,qty,cst,dd)AS(
select '001',100,10,'2016-01-02' union all
select '001',20,2,'2016-02-02' union all
select '001',26,2.6,'2016-02-03' union all
select '002',30,28,'2016-01-06' union all
select '002',50,40,'2016-03-05' union all
SELECT '003',120,80,'2016-03-06'
)
SELECT no ,
SUM(CASE WHEN CONVERT(NVARCHAR(7), dd, 112) = '2016-01' THEN qty
ELSE 0
END) AS [2016-01数量] ,
SUM(CASE WHEN CONVERT(NVARCHAR(7), dd, 112) = '2016-01' THEN cst
ELSE 0
END) AS [2016-01成本] ,
SUM(CASE WHEN CONVERT(NVARCHAR(7), dd, 112) = '2016-02' THEN qty
ELSE 0
END) AS [2016-02数量] ,
SUM(CASE WHEN CONVERT(NVARCHAR(7), dd, 112) = '2016-02' THEN cst
ELSE 0
END) AS [2016-02成本] ,
SUM(CASE WHEN CONVERT(NVARCHAR(7), dd, 112) = '2016-03' THEN qty
ELSE 0
END) AS [2016-03数量] ,
SUM(CASE WHEN CONVERT(NVARCHAR(7), dd, 112) = '2016-03' THEN cst
ELSE 0
END) AS [2016-03成本]
FROM tab1
GROUP BY no
结果：
卖水果的net 版主 2017-02-10
- 打赏
- 举报
那就动态 SQL + 行转列吧。
