Try: SELECT TOP (100) PERCENT NULL AS 林业局, SUBSTRING(单位代码, 5, 2) AS 林场, SUBSTRING(单位代码, 5, 2) AS 单位, (LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', ''))) / LEN(',') + 1 AS 林班数, SUM(管护面积) AS 规划管护面积, SUM(管护面积) AS 实际管护面积, SUM(CASE WHEN 管护方式 = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN 管护方式 = 2 THEN 1 ELSE 0 END) AS 专业管护人数, SUM(CASE WHEN [管护方式] = 1 THEN [管护面积] ELSE 0 END) + SUM(CASE WHEN [管护方式] = 2 THEN [管护面积] ELSE 0 END) AS 专业管护面积, SUM(CASE WHEN 管护方式 = 1 THEN 1 ELSE 0 END) AS 设站管护人数, SUM(CASE WHEN [管护方式] = 1 THEN [管护面积] ELSE 0 END) AS 设站管护面积, SUM(CASE WHEN 管护方式 = 2 THEN 1 ELSE 0 END) AS 责任区人数, SUM(CASE WHEN [管护方式] = 2 THEN [管护面积] ELSE 0 END) AS 责任区面积, SUM(CASE WHEN 管护方式 = 3 THEN 1 ELSE 0 END) AS 承包管护人数, SUM(CASE WHEN [管护方式] = 3 THEN [管护面积] ELSE 0 END) AS 承包管护面积, SUM(CASE WHEN 管护方式 = 1 THEN LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) AS 设站管护林班数, SUM(CASE WHEN 管护方式 = 2 THEN LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) AS 责任区林班数, SUM(CASE WHEN 管护方式 = 3 THEN LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) AS 承包管护林班数, SUM(CASE WHEN 管护方式 = 1 THEN LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) + SUM(CASE WHEN 管护方式 = 2 THEN LEN(相关林班号)- LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) AS 专业管护林班数, ISNULL(MAX(CASE [管护方式] WHEN '1' THEN [相关林班号] END), '') AS 方式1的代号明细, ISNULL(MAX(CASE [管护方式] WHEN '2' THEN [相关林班号] END), '') AS 方式2的代号明细, ISNULL(MAX(CASE [管护方式] WHEN '3' THEN [相关林班号] END), '') AS 方式3的代号明细 FROM dbo.当期管护区信息表 GROUP BY SUBSTRING(单位代码, 5, 2),相关林班号
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp; go create table #temp( [单位代码] varchar(100), [管护方式] INT, [相关林班号] varchar(100), [面积] INT); insert #temp select '120101','2','2','1' union all select '120102','1','1,3','2' union all select '120103','3','1,2,4','3' union all select '120102','1','2,3','4' union all select '120102','2','1,4','5' --我计算出来的林班数都是3,楼主仅做个参考吧: SELECT 林业局, 林场, 方式1林班个数 = MAX(CASE WHEN 管护方式 = 1 THEN 林班数 END), 方式1面积 = SUM(CASE WHEN 管护方式 = 1 THEN [面积] END), 方式2面积 = SUM(CASE WHEN 管护方式 = 2 THEN [面积] END), 方式3面积 = SUM(CASE WHEN 管护方式 = 3 THEN [面积] END), 方式2林班个数 = MAX(CASE WHEN 管护方式 = 2 THEN 林班数 END), 方式3林班个数 = MAX(CASE WHEN 管护方式 = 3 THEN 林班数 END) from ( SELECT m.林业局, m.林场, m.[管护方式],m.面积, n.林班数 FROM ( SELECT 林业局 = SUBSTRING(单位代码, 3, 2), 林场 = SUBSTRING(单位代码, 5, 2), [管护方式], 林班数 = CONVERT(XML, '<root><v>'+replace( STUFF((SELECT ','+[相关林班号] FROM #temp b WHERE b.单位代码=a.单位代码 AND b.管护方式=a.管护方式 FOR XML PATH('')),1,1,'') ,',','</v><v>')+'</v></root>'), 面积 FROM #TEMP a ) m OUTER APPLY ( SELECT 林班数=COUNT(DISTINCT C.v.value('.','NVARCHAR(MAX)')) FROM m.林班数.nodes('/root/v') C(v) ) n ) T GROUP BY 林业局, 林场 /* 林业局 林场 方式1林班个数 方式1面积 方式2面积 方式3面积 方式2林班个数 方式3林班个数 01 01 NULL NULL 1 NULL 1 NULL 01 02 3 6 5 NULL 2 NULL 01 03 NULL NULL NULL 3 NULL 3 */
SELECT TOP (100) PERCENT
NULL AS 林业局,
SUBSTRING(单位代码, 5, 2) AS 林场,
SUBSTRING(单位代码, 5, 2) AS 单位,
(LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', ''))) / LEN(',') + 1 AS 林班数,
SUM(管护面积) AS 规划管护面积,
SUM(管护面积) AS 实际管护面积,
SUM(CASE WHEN 管护方式 = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN 管护方式 = 2 THEN 1 ELSE 0 END) AS 专业管护人数,
SUM(CASE WHEN [管护方式] = 1 THEN [管护面积] ELSE 0 END) + SUM(CASE WHEN [管护方式] = 2 THEN [管护面积] ELSE 0 END) AS 专业管护面积,
SUM(CASE WHEN 管护方式 = 1 THEN 1 ELSE 0 END) AS 设站管护人数,
SUM(CASE WHEN [管护方式] = 1 THEN [管护面积] ELSE 0 END) AS 设站管护面积,
SUM(CASE WHEN 管护方式 = 2 THEN 1 ELSE 0 END) AS 责任区人数,
SUM(CASE WHEN [管护方式] = 2 THEN [管护面积] ELSE 0 END) AS 责任区面积,
SUM(CASE WHEN 管护方式 = 3 THEN 1 ELSE 0 END) AS 承包管护人数,
SUM(CASE WHEN [管护方式] = 3 THEN [管护面积] ELSE 0 END) AS 承包管护面积,
SUM(CASE WHEN 管护方式 = 1 THEN LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) AS 设站管护林班数,
SUM(CASE WHEN 管护方式 = 2 THEN LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) AS 责任区林班数,
SUM(CASE WHEN 管护方式 = 3 THEN LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) AS 承包管护林班数,
SUM(CASE WHEN 管护方式 = 1 THEN LEN(相关林班号) - LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) + SUM(CASE WHEN 管护方式 = 2 THEN LEN(相关林班号)- LEN(REPLACE(相关林班号, ',', '')) + 1 ELSE 0 END) AS 专业管护林班数,
ISNULL(MAX(CASE [管护方式] WHEN '1' THEN [相关林班号] END), '') AS 方式1的代号明细,
ISNULL(MAX(CASE [管护方式] WHEN '2' THEN [相关林班号] END), '') AS 方式2的代号明细,
ISNULL(MAX(CASE [管护方式] WHEN '3' THEN [相关林班号] END), '') AS 方式3的代号明细
FROM dbo.当期管护区信息表
GROUP BY SUBSTRING(单位代码, 5, 2),相关林班号
单位代码——管护方式——相关林班号——面积
120101——2——2——1
120102 ——1 ——1,3——2
120103—— 3——1,2,4——3
120102——1——2,3——4
120102—— 2 ——1,4——5
想要的查询结果是
林业局——林场——方式1林班个数——方式1面积——方式2面积——方式3面积——方式2林班个数——方式3林班个数
01——01——0——null——1——null——X——x
01——02——1——6——5——null——X——x
01——03——0——null——null——3——X——x
其中,林业局为单位代码3,4位,林场为5,6位,林班个数为明细中林班号的个数。求sql语句。
go
create table #temp( [单位代码] varchar(100), [管护方式] INT, [相关林班号] varchar(100), [面积] INT);
insert #temp
select '120101','2','2','1' union all
select '120102','1','1,3','2' union all
select '120103','3','1,2,4','3' union all
select '120102','1','2,3','4' union all
select '120102','2','1,4','5' --我计算出来的林班数都是3,楼主仅做个参考吧:
SELECT
林业局, 林场,
方式1林班个数 = MAX(CASE WHEN 管护方式 = 1 THEN 林班数 END),
方式1面积 = SUM(CASE WHEN 管护方式 = 1 THEN [面积] END),
方式2面积 = SUM(CASE WHEN 管护方式 = 2 THEN [面积] END),
方式3面积 = SUM(CASE WHEN 管护方式 = 3 THEN [面积] END),
方式2林班个数 = MAX(CASE WHEN 管护方式 = 2 THEN 林班数 END),
方式3林班个数 = MAX(CASE WHEN 管护方式 = 3 THEN 林班数 END)
from
(
SELECT m.林业局, m.林场, m.[管护方式],m.面积, n.林班数
FROM
(
SELECT
林业局 = SUBSTRING(单位代码, 3, 2),
林场 = SUBSTRING(单位代码, 5, 2),
[管护方式],
林班数 = CONVERT(XML, '<root><v>'+replace(
STUFF((SELECT ','+[相关林班号] FROM #temp b WHERE b.单位代码=a.单位代码 AND b.管护方式=a.管护方式 FOR XML PATH('')),1,1,'')
,',','</v><v>')+'</v></root>'),
面积
FROM #TEMP a
) m
OUTER APPLY
(
SELECT 林班数=COUNT(DISTINCT C.v.value('.','NVARCHAR(MAX)')) FROM m.林班数.nodes('/root/v') C(v)
) n
) T
GROUP BY 林业局, 林场
/*
林业局 林场 方式1林班个数 方式1面积 方式2面积 方式3面积 方式2林班个数 方式3林班个数
01 01 NULL NULL 1 NULL 1 NULL
01 02 3 6 5 NULL 2 NULL
01 03 NULL NULL NULL 3 NULL 3
*/