各位高手，帮忙写个SQL语句，谢谢

ivwseeqg 2014-07-14 06:37:22

create table dp(did varchar(10),dname varchar(20));--部门

insert into dp values('1','部门1'),('2','部门2')

create table sl(did varchar(10),pid varchar(10),pname varchar(10))--产品

insert into sl values('1','1','产品1'),('2','2','产品2'),('1','3','产品3'),('2','4','产品4')

create table xl(pid varchar(10),count int,xdate date);--销量

insert into xl values('1',5,'2014-01-01'),('2',6,'2014-01-01'),('3',7,'2014-02-01'),('4',8,'2014-02-01')

统计每个部门每个月每个产品的销量。

...全文

200 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

ivwseeqg 2014-07-15

打赏
举报

引用 12 楼 chen357313771 的回复:

难道你要介个效果？ [quote=引用 11 楼 ivwseeqg 的回复:] [quote=引用 9 楼 chen357313771 的回复:] 你是想要什么结果呢？上面按月份的合计，下面按月份，产品的合计，横向，纵向合计不都有了么？

横向合计分开，即部门1所有月份产品1合计，部门1所有月份产品2合计，这样显示可以实现吗？[/quote]


IF OBJECT_ID('tempdb..#ColName','U') IS NOT NULL DROP TABLE #ColName
SELECT CAST([Month] AS NVARCHAR)+N'月'+B.pname AS ColName
	,[Month]
	,pname
INTO #ColName
FROM (SELECT DISTINCT MONTH(xdate) AS [Month] FROM xl ) A
CROSS JOIN (SELECT DISTINCT pname FROM sl) B

DECLARE @Sql NVARCHAR(MAX),@Sql1 NVARCHAR(MAX),@Sql2 NVARCHAR(Max),@Sql3 NVARCHAR(Max)
		,@Sql4 NVARCHAR(MAX),@Sql5 NVARCHAR(MAX)
SELECT @Sql=ISNULL(@Sql+',','')+'['+ColName+']' FROM #ColName GROUP BY ColName
SELECT @Sql1=ISNULL(@Sql1+',','')+'ISNULL('+'SUM(['+ColName+'])'+',0) AS '+'['+ColName+']' FROM #ColName GROUP BY ColName
SELECT @Sql2=ISNULL(@Sql2+',','')+'['+pname+']' FROM #ColName GROUP BY pname
SELECT @Sql4=ISNULL(@Sql4+',','')+'ISNULL('+'SUM(['+pname+'])'+',0) AS '+'['+pname+N'合计'+']' FROM #ColName GROUP BY pname
SELECT @Sql5=ISNULL(@Sql5+'+','')+'ISNULL('+'SUM(['+pname+'])'+',0)'  FROM #ColName GROUP BY pname
SELECT @Sql5=ISNULL(@Sql5+N' AS 合计','')

		SELECT @Sql3=N'SELECT CASE WHEN dname IS NULL THEN +'+N'N''合计'''+' ELSE dname END AS dname,'+@Sql1+','+@Sql4+','+@Sql5+N'
						FROM (
						SELECT did,dname,'+@Sql1+'
						FROM (
						SELECT A.did, A.dname
							,CAST(MONTH(xdate) AS NVARCHAR(10))+'+N'N''月'''+'+B.pname AS MonthProd
							,C.count
						FROM dp AS A
						JOIN sl AS B ON A.did=B.did
						LEFT JOIN xl AS C ON B.pid=C.pid ) AS A
						PIVOT(SUM(a.count) FOR MonthProd IN ('+@Sql+N'))p
						GROUP BY did,dname
						 ) AS A
						 JOIN (
						 SELECT did,'+@Sql2+'
						FROM (SELECT A.did
								,B.pname 
								,C.count
							FROM dp AS A
							JOIN sl AS B ON A.did=B.did
							LEFT JOIN xl AS C ON B.pid=C.pid ) A
						PIVOT(SUM(COUNT) FOR pname IN('+@Sql2+'))p
						 ) AS B ON A.did=B.did 
				GROUP BY dname
				WITH rollup '    
				PRINT @Sql3  
EXEC(@Sql3)

/*
dname	1月产品1	1月产品2	1月产品3	1月产品4	2月产品1	2月产品2	2月产品3	2月产品4	产品1合计	产品2合计	产品3合计	产品4合计	合计
部门1	5			0			0			0			0			0			7			0			5			0			7			0			12
部门2	0			6			0			0			0			0			0			8			0			6			0			8			14
合计	5			6			0			0			0			0			7			8			5			6			7			8			26
*/

[/quote] 对了，就是这个效果，非常感谢

chen357313771 2014-07-15

打赏
举报

难道你要介个效果？

引用 11 楼 ivwseeqg 的回复:

[quote=引用 9 楼 chen357313771 的回复:] 你是想要什么结果呢？上面按月份的合计，下面按月份，产品的合计，横向，纵向合计不都有了么？

横向合计分开，即部门1所有月份产品1合计，部门1所有月份产品2合计，这样显示可以实现吗？[/quote]


IF OBJECT_ID('tempdb..#ColName','U') IS NOT NULL DROP TABLE #ColName
SELECT CAST([Month] AS NVARCHAR)+N'月'+B.pname AS ColName
	,[Month]
	,pname
INTO #ColName
FROM (SELECT DISTINCT MONTH(xdate) AS [Month] FROM xl ) A
CROSS JOIN (SELECT DISTINCT pname FROM sl) B

DECLARE @Sql NVARCHAR(MAX),@Sql1 NVARCHAR(MAX),@Sql2 NVARCHAR(Max),@Sql3 NVARCHAR(Max)
		,@Sql4 NVARCHAR(MAX),@Sql5 NVARCHAR(MAX)
SELECT @Sql=ISNULL(@Sql+',','')+'['+ColName+']' FROM #ColName GROUP BY ColName
SELECT @Sql1=ISNULL(@Sql1+',','')+'ISNULL('+'SUM(['+ColName+'])'+',0) AS '+'['+ColName+']' FROM #ColName GROUP BY ColName
SELECT @Sql2=ISNULL(@Sql2+',','')+'['+pname+']' FROM #ColName GROUP BY pname
SELECT @Sql4=ISNULL(@Sql4+',','')+'ISNULL('+'SUM(['+pname+'])'+',0) AS '+'['+pname+N'合计'+']' FROM #ColName GROUP BY pname
SELECT @Sql5=ISNULL(@Sql5+'+','')+'ISNULL('+'SUM(['+pname+'])'+',0)'  FROM #ColName GROUP BY pname
SELECT @Sql5=ISNULL(@Sql5+N' AS 合计','')

		SELECT @Sql3=N'SELECT CASE WHEN dname IS NULL THEN +'+N'N''合计'''+' ELSE dname END AS dname,'+@Sql1+','+@Sql4+','+@Sql5+N'
						FROM (
						SELECT did,dname,'+@Sql1+'
						FROM (
						SELECT A.did, A.dname
							,CAST(MONTH(xdate) AS NVARCHAR(10))+'+N'N''月'''+'+B.pname AS MonthProd
							,C.count
						FROM dp AS A
						JOIN sl AS B ON A.did=B.did
						LEFT JOIN xl AS C ON B.pid=C.pid ) AS A
						PIVOT(SUM(a.count) FOR MonthProd IN ('+@Sql+N'))p
						GROUP BY did,dname
						 ) AS A
						 JOIN (
						 SELECT did,'+@Sql2+'
						FROM (SELECT A.did
								,B.pname 
								,C.count
							FROM dp AS A
							JOIN sl AS B ON A.did=B.did
							LEFT JOIN xl AS C ON B.pid=C.pid ) A
						PIVOT(SUM(COUNT) FOR pname IN('+@Sql2+'))p
						 ) AS B ON A.did=B.did 
				GROUP BY dname
				WITH rollup '    
				PRINT @Sql3  
EXEC(@Sql3)

/*
dname	1月产品1	1月产品2	1月产品3	1月产品4	2月产品1	2月产品2	2月产品3	2月产品4	产品1合计	产品2合计	产品3合计	产品4合计	合计
部门1	5			0			0			0			0			0			7			0			5			0			7			0			12
部门2	0			6			0			0			0			0			0			8			0			6			0			8			14
合计	5			6			0			0			0			0			7			8			5			6			7			8			26
*/

ivwseeqg 2014-07-15

打赏
举报

引用 9 楼 chen357313771 的回复:

你是想要什么结果呢？上面按月份的合计，下面按月份，产品的合计，横向，纵向合计不都有了么？

横向合计分开，即部门1所有月份产品1合计，部门1所有月份产品2合计，这样显示可以实现吗？

Andy-W 2014-07-15

打赏
举报


use tempdb
go

declare @Month1 date='20140101' --左邊的1月份
declare @Month2 date='20140201' --右邊的2月份

--pivot中的in列表
declare @ProductList nvarchar(max)=(select stuff((select ','+quotename(pname) from sl for xml path('')),1,1,''))

--最後顯示控制
declare @select_1 nvarchar(max)=(select stuff((select ',b.'+quotename(pname)+' as '+quotename(pname+N'销量_1') from sl for xml path('')),1,1,''))
declare @select_2 nvarchar(max)=(select stuff((select ',b.'+quotename(pname)+' as '+quotename(pname+N'销量_2') from sl for xml path('')),1,1,''))

declare @sql nvarchar(max)=''

set @sql='
;with cte_xl as 
(
select a.xdate,a.count,b.pname,c.dname
	from xl a
		inner join sl b on b.pid=a.pid
		inner join dp c on c.did=b.did
	where a.xdate in(@Month1,@Month2)
),cte_left as
(
select * 
	from cte_xl a
	 pivot(max([count]) for pname in('+@ProductList+')) b
	where xdate=@Month1
),cte_right as
(
select * 
	from cte_xl a
	 pivot(max([count]) for pname in('+@ProductList+')) b
	where xdate=@Month2
)
select a.dname as [部门名称],'+@select_1+','+@select_2+'
	from dp a
		left join cte_left b on b.dname=a.dname
		left join cte_right c on c.dname=a.dname 
'	  
exec sp_executesql @sql,N'@Month1 date,@Month2 date',@Month1,@Month2

/*
部门名称	产品1销量_1	产品2销量_1	产品3销量_1	产品4销量_1	产品1销量_2	产品2销量_2	产品3销量_2	产品4销量_2
部门1	5	NULL	NULL	NULL	5	NULL	NULL	NULL
部门2	NULL	6	NULL	NULL	NULL	6	NULL	NULL
*/

實際報表填充時，標題名中"_1" and "_2"部份可以通過程序過濾掉不顯示。

chen357313771 2014-07-15

打赏
举报

你是想要什么结果呢？上面按月份的合计，下面按月份，产品的合计，横向，纵向合计不都有了么？

ivwseeqg 2014-07-15

打赏
举报

引用 7 楼 chen357313771 的回复:


---只要月份合计的。。
SELECT CASE WHEN dname IS NULL THEN N'合计' ELSE dname END AS dname
	,[1月]
	,[2月]
	,[合计]
FROM (
SELECT  A.dname,
SUM (CASE WHEN MONTH (xdate)=1 THEN COUNT ELSE 0 END )AS [1月] ,
SUM (CASE WHEN MONTH (xdate)=2 THEN COUNT ELSE 0  END) AS [2月] 
,SUM (CASE WHEN MONTH (xdate)=1 THEN COUNT ELSE 0 END )
+SUM (CASE WHEN MONTH (xdate)=2 THEN COUNT ELSE 0  END) AS N'合计'
FROM dp AS A
JOIN sl AS B ON A.did=B.did
LEFT JOIN xl AS C ON B.pid=C.pid
GROUP BY  A.dname  
WITH ROLLUP ) A

/*
dname	1月	2月	合计
部门1	5	7	12
部门2	6	8	14
合计	11	15	26
*/



---严格按照需求,分月份,产品动态统计--

IF OBJECT_ID('tempdb..#ColName','U') IS NOT NULL DROP TABLE #ColName
SELECT CAST([Month] AS NVARCHAR)+N'月'+B.pname AS ColName
INTO #ColName
FROM (SELECT DISTINCT MONTH(xdate) AS [Month] FROM xl ) A
CROSS JOIN (SELECT DISTINCT pname FROM sl) B

DECLARE @Sql NVARCHAR(MAX),@Sql1 NVARCHAR(MAX),@Sql2 NVARCHAR(Max),@Sql3 NVARCHAR(Max)

SELECT @Sql=ISNULL(@Sql+',','')+'['+ColName+']' FROM #ColName GROUP BY ColName

SELECT @Sql1=ISNULL(@Sql1+',','')+'ISNULL('+'SUM(['+ColName+'])'+',0) AS '+'['+ColName+']' FROM #ColName GROUP BY ColName
SELECT @Sql2=ISNULL(@Sql2+'+','')+'ISNULL('+'SUM(['+ColName+'])'+',0)'  FROM #ColName GROUP BY ColName
SELECT @Sql2=ISNULL(@Sql2+N' AS 合计','')

SELECT @Sql3=N'	SELECT CASE WHEN dname IS NULL THEN +'+N'N''合计'''+' ELSE dname END AS dname,'+@Sql1+','+@Sql2+N'
				FROM (
				SELECT A.dname
					,CAST(MONTH(xdate) AS NVARCHAR(10))+'+N'N''月'''+'+B.pname AS MonthProd
					,C.count
				FROM dp AS A
				JOIN sl AS B ON A.did=B.did
				LEFT JOIN xl AS C ON B.pid=C.pid ) AS A
				PIVOT(SUM(a.count) FOR MonthProd IN ('+@Sql+N'))p
				GROUP BY dname
				WITH rollup '    
				PRINT @Sql3  
EXEC(@Sql3)

/*
dname	1月产品1	1月产品2	1月产品3	1月产品4	2月产品1	2月产品2	2月产品3	2月产品4	合计
部门1	5			0			0			0			0			0			7			0			12
部门2	0			6			0			0			0			0			0			8			14
合计	5			6			0			0			0			0			7			8			26
*/

SELECT CASE WHEN dname IS NULL THEN N'合计' ELSE dname END AS dname ,[1月] ,[2月] ,[合计] FROM ( SELECT A.dname, SUM (CASE WHEN MONTH (xdate)=1 THEN COUNT ELSE 0 END )AS [1月] , SUM (CASE WHEN MONTH (xdate)=2 THEN COUNT ELSE 0 END) AS [2月] ,SUM (CASE WHEN MONTH (xdate)=1 THEN COUNT ELSE 0 END ) +SUM (CASE WHEN MONTH (xdate)=2 THEN COUNT ELSE 0 END) AS N'合计'FROM dp AS A JOIN sl AS B ON A.did=B.did LEFT JOIN xl AS C ON B.pid=C.pid GROUP BY A.dname WITH ROLLUP ) A 这位兄弟，通过上面这个查询语句可以实现你下面用多条语句实现的功能吗，产品都是固定的。横向合计可以用产品1合计，产品2合计等来输出呢？

chen357313771 2014-07-15

打赏
举报


---只要月份合计的。。
SELECT CASE WHEN dname IS NULL THEN N'合计' ELSE dname END AS dname
	,[1月]
	,[2月]
	,[合计]
FROM (
SELECT  A.dname,
SUM (CASE WHEN MONTH (xdate)=1 THEN COUNT ELSE 0 END )AS [1月] ,
SUM (CASE WHEN MONTH (xdate)=2 THEN COUNT ELSE 0  END) AS [2月] 
,SUM (CASE WHEN MONTH (xdate)=1 THEN COUNT ELSE 0 END )
+SUM (CASE WHEN MONTH (xdate)=2 THEN COUNT ELSE 0  END) AS N'合计'
FROM dp AS A
JOIN sl AS B ON A.did=B.did
LEFT JOIN xl AS C ON B.pid=C.pid
GROUP BY  A.dname  
WITH ROLLUP ) A

/*
dname	1月	2月	合计
部门1	5	7	12
部门2	6	8	14
合计	11	15	26
*/



---严格按照需求,分月份,产品动态统计--

IF OBJECT_ID('tempdb..#ColName','U') IS NOT NULL DROP TABLE #ColName
SELECT CAST([Month] AS NVARCHAR)+N'月'+B.pname AS ColName
INTO #ColName
FROM (SELECT DISTINCT MONTH(xdate) AS [Month] FROM xl ) A
CROSS JOIN (SELECT DISTINCT pname FROM sl) B

DECLARE @Sql NVARCHAR(MAX),@Sql1 NVARCHAR(MAX),@Sql2 NVARCHAR(Max),@Sql3 NVARCHAR(Max)

SELECT @Sql=ISNULL(@Sql+',','')+'['+ColName+']' FROM #ColName GROUP BY ColName

SELECT @Sql1=ISNULL(@Sql1+',','')+'ISNULL('+'SUM(['+ColName+'])'+',0) AS '+'['+ColName+']' FROM #ColName GROUP BY ColName
SELECT @Sql2=ISNULL(@Sql2+'+','')+'ISNULL('+'SUM(['+ColName+'])'+',0)'  FROM #ColName GROUP BY ColName
SELECT @Sql2=ISNULL(@Sql2+N' AS 合计','')

SELECT @Sql3=N'	SELECT CASE WHEN dname IS NULL THEN +'+N'N''合计'''+' ELSE dname END AS dname,'+@Sql1+','+@Sql2+N'
				FROM (
				SELECT A.dname
					,CAST(MONTH(xdate) AS NVARCHAR(10))+'+N'N''月'''+'+B.pname AS MonthProd
					,C.count
				FROM dp AS A
				JOIN sl AS B ON A.did=B.did
				LEFT JOIN xl AS C ON B.pid=C.pid ) AS A
				PIVOT(SUM(a.count) FOR MonthProd IN ('+@Sql+N'))p
				GROUP BY dname
				WITH rollup '    
				PRINT @Sql3  
EXEC(@Sql3)

/*
dname	1月产品1	1月产品2	1月产品3	1月产品4	2月产品1	2月产品2	2月产品3	2月产品4	合计
部门1	5			0			0			0			0			0			7			0			12
部门2	0			6			0			0			0			0			0			8			14
合计	5			6			0			0			0			0			7			8			26
*/

xxfvba 2014-07-15

打赏
举报

declare @s varchar(max) select @s=ISNULL(@s+',','')+'sum(case when month(xdate)='''+convert(varchar,month(xdate))+''' and a.pid='''+a.pid+''' then count end) as ['+CONVERT(varchar,month(xdate))+'月'+pname+']' from xl a,sl b where a.pid=b.pid group by a.pid,MONTH(xdate),pname set @s='select case when grouping(dname)=0 then dname else ''Total'' end as 部门,'+@s+',sum(count) as total from dp c,sl b,xl a where c.did=b.did and b.pid=a.pid group by dname with rollup' --print @s exec(@s)

xxfvba 2014-07-15

打赏
举报

--稍微改动了点 declare @s varchar(max) select @s=ISNULL(@s+',','')+'sum(case when month(xdate)='''+convert(varchar,month(xdate))+''' and a.pid='''+a.pid+''' then count end) as ['+CONVERT(varchar,month(xdate))+'月'+pname+']' from xl a,sl b where a.pid=b.pid group by a.pid,MONTH(xdate),pname set @s='select dname,'+@s+',sum(count) as total from dp c,sl b,xl a where c.did=b.did and b.pid=a.pid group by dname with rollup' --print @s exec(@s)

ivwseeqg 2014-07-15

打赏
举报

引用 2 楼 chz415767975 的回复:

select  b.dname,
max (case when month (xdate)=1    then count end )as [1月] ,
max (case when month (xdate)=2   then count end) as [2月] ,
''as'合计'
from xl a left join  dp  b on a.pid =b.did  where b.dname is not null
group by  b.dname   
union all
select '合计',
sum (case when month (xdate)=1   then count end )as [1月] ,
sum (case when month (xdate)=2  then count end) as [2月],
sum (case when month (xdate)=1   then count end )+sum (case when month (xdate)=2  then count end) 
from xl


--dname	1月	2月	合计
--部门1	5	NULL	0
--部门2	6	NULL	0
--合计	11	15	26

兄弟，横向合计呢？合计是错误的吧

treemo 2014-07-14

打赏
举报

二楼的好像不对吧

霜寒月冷 2014-07-14

打赏
举报

select  b.dname,
max (case when month (xdate)=1    then count end )as [1月] ,
max (case when month (xdate)=2   then count end) as [2月] ,
''as'合计'
from xl a left join  dp  b on a.pid =b.did  where b.dname is not null
group by  b.dname   
union all
select '合计',
sum (case when month (xdate)=1   then count end )as [1月] ,
sum (case when month (xdate)=2  then count end) as [2月],
sum (case when month (xdate)=1   then count end )+sum (case when month (xdate)=2  then count end) 
from xl


--dname	1月	2月	合计
--部门1	5	NULL	0
--部门2	6	NULL	0
--合计	11	15	26

ivwseeqg 2014-07-14