34,590
社区成员
发帖
与我相关
我的任务
分享
select 地区代码, sum(国家),sum(集体),sum(个人),sum(其他的数量) group by 地区代码
--1)表和数据
CREATE TABLE Record
(code varchar(32),
ower varchar(32)
)
INSERT INTO Record
SELECT '320502','0001'
UNION ALL
SELECT '320502','0011'
UNION ALL
SELECT '320503','1100'
CREATE TABLE category
(code varchar(32),
name varchar(32)
)
INSERT INTO category
SELECT '0', '国家'
UNION ALL
SELECT '1', '集体'
UNION ALL
SELECT '2', '个人'
UNION ALL
SELECT '3', '其他'
--2)查询
SELECT CODE,'国家'= SUM(CAST (SUBSTRING(ower,1,1)AS int )) ,
'集体' = SUM(CAST (SUBSTRING(ower,2,1)AS int )) ,
'个人' = SUM(CAST (SUBSTRING(ower,3,1)AS int )) ,
'其他' = SUM(CAST (SUBSTRING(ower,4,1)AS int ))
FROM Record GROUP BY code
--3结果
CODE 国家 集体 个人 其他
-------------------------------- ----------- ----------- ----------- -----------
320502 0 0 1 2
320503 1 1 0 0
(2 row(s) affected)
--> 测试数据:[info]
if object_id('[info]') is not null drop table [info]
go
create table [info]([code] int,[ower] varchar(4))
insert [info]
select 320502,'0001' union all
select 320502,'0011' union all
select 320503,'1100'
--> 测试数据:[category]
if object_id('[category]') is not null drop table [category]
go
create table [category]([code] int,[name] varchar(4))
insert [category]
select 0,'国家' union all
select 1,'集体' union all
select 2,'个人' union all
select 3,'其他'
declare @sql varchar(8000)
select @sql = isnull(@sql+',','')+'sum(case substring(ower,'+rtrim(code)+'+1,1) when ''1'' then 1 else 0 end ) as ['+name+']'
from category
select @sql = 'select code,'+@sql+' from info group by code'
exec(@sql)
code 国家 集体 个人 其他
----------- ----------- ----------- ----------- -----------
320502 0 0 1 2
320503 1 1 0 0
没懂!!