一条 SQL 语句不会写

[媛爱]

大神们：
一个公司，在不同的城市有很多家店 如下的查询出来的结果

company_info（表1）
C_ID	Company_Name            Company_Name_F
198	宏图三胞高科技术有限公司	HISAP
199	国美电器控股有限公司	Gome
200	苏宁云商集团股份有限公司	Suning
201	美承(中国)投资有限公司	One zero
202	顺电连锁股份有限公司	Sundan

 Shop_info(表2)
S_ID	C_ID	Shop_Name	City_ID
10003	198	山西路宏图大厦店	371
10004	198	南京国贸店	371
10005	198	南京旗舰店	371
10006	198	川沙店	          370
10007	198	徐州王陵路店	374
10008	198	芜湖中山路店	441
10016	198	苏果联营店江宁分部	371
10017	198	苏州亚细亚店	372
10018	198	苏州阊胥路店	372
10019	198	徐家汇旗舰店	370

所以我想写个sql语句 查询出一下结果

           HISAP          Gome         Suning       Onezero        Sundan
城市(北京)  店数量         店数量       店数量        店数量           店数量
天津        5家             10家         6家           1家              4家
上海        3家             6家         29家          5家               7家
……

结果是手敲的！ 谢谢了！ 在线等！

急急

[Andy__Huang]

行列转换问题，请参考： http://blog.csdn.net/hdhai9451/article/details/5026933

[Mr_Nice]

if object_id('[company_info]') is not null drop table [company_info]
go
create table [company_info] (C_ID int,Company_Name nvarchar(24),Company_Name_F nvarchar(12))
insert into [company_info]
select 198,'宏图三胞高科技术有限公司','HISAP' union all
select 199,'国美电器控股有限公司','Gome' union all
select 200,'苏宁云商集团股份有限公司','Suning'union all
select 201,'美承(中国)投资有限公司','One zero' union all
select 202,'顺电连锁股份有限公司','Sundan'

if object_id('[Shop_info]') is not null drop table [Shop_info]
go
create table [Shop_info] (S_ID int,C_ID int,Shop_Name nvarchar(18),City_ID int)
insert into [Shop_info]
select 10003,198,'山西路宏图大厦店',371 union all
select 10004,198,'南京国贸店',371 union all
select 10005,198,'南京旗舰店',371 union all
select 10006,198,'川沙店',370 union all
select 10007,198,'徐州王陵路店',374 union all
select 10008,198,'芜湖中山路店',441 union all
select 10016,198,'苏果联营店江宁分部',371 union all
select 10017,198,'苏州亚细亚店',372 union all
select 10018,198,'苏州阊胥路店',372 union all
select 10019,198,'徐家汇旗舰店',370

select * from [company_info]
select * from [Shop_info]


declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + Company_Name_F from (SELECT A.Company_Name_F,b.city_id,COUNT(1) AS num
FROM [company_info] A
LEFT JOIN [Shop_info] B ON A.c_id =B.c_id
GROUP BY A.Company_Name_F,b.city_id)T group by Company_Name_F
set @sql = '[' + @sql + ']'
exec ('select * from (select * from (SELECT A.Company_Name_F,b.city_id,COUNT(1) AS num
FROM [company_info] A
LEFT JOIN [Shop_info] B ON A.c_id =B.c_id
GROUP BY A.Company_Name_F,b.city_id)T) a pivot (max(num) for Company_Name_F in (' + @sql + ')) b')

/*
city_id	Gome	HISAP	One zero	Sundan	Suning      --城市名称，lz再链接以下即可。
NULL	1	NULL	1	1	1
370	NULL	2	NULL	NULL	NULL
371	NULL	4	NULL	NULL	NULL
372	NULL	2	NULL	NULL	NULL
374	NULL	1	NULL	NULL	NULL
441	NULL	1	NULL	NULL	NULL*/

[学习中-------]

SELECT city_id,sum(case when c_id='198' then ci_id else null end as hisap)hisap,
sum(case when c_id='199' then ci_id else null end as hisap)Gome,
sum(case when c_id='200' then ci_id else null end as hisap)Suning,
sum(case when c_id='201' then ci_id else null end as hisap)Onezero,
sum(case when c_id='202' then ci_id else null end as hisap)Sundan from Shop_info group by city_id

[学习中-------]

引用 2 楼 myselfff 的回复:

SELECT city_id,sum(case when c_id='198' then ci_id else null end as hisap)hisap,
sum(case when c_id='199' then ci_id else null end as hisap)Gome,
sum(case when c_id='200' then ci_id else null end as hisap)Suning,
sum(case when c_id='201' then ci_id else null end as hisap)Onezero,
sum(case when c_id='202' then ci_id else null end as hisap)Sundan from Shop_info group by city_id

SELECT city_id,count(case when c_id='198' then c_id else null end )hisap, count(case when c_id='199' then c_id else null end )Gome, count(case when c_id='200' then c_id else null end )Suning, count(case when c_id='201' then c_id else null end )Onezero, count(case when c_id='202' then c_id else null end )Sundan from Shop_info group by city_id

[媛爱]

引用 1 楼 OrchidCat 的回复:

if object_id('[company_info]') is not null drop table [company_info]
go
create table [company_info] (C_ID int,Company_Name nvarchar(24),Company_Name_F nvarchar(12))
insert into [company_info]
select 198,'宏图三胞高科技术有限公司','HISAP' union all
select 199,'国美电器控股有限公司','Gome' union all
select 200,'苏宁云商集团股份有限公司','Suning'union all
select 201,'美承(中国)投资有限公司','One zero' union all
select 202,'顺电连锁股份有限公司','Sundan'

if object_id('[Shop_info]') is not null drop table [Shop_info]
go
create table [Shop_info] (S_ID int,C_ID int,Shop_Name nvarchar(18),City_ID int)
insert into [Shop_info]
select 10003,198,'山西路宏图大厦店',371 union all
select 10004,198,'南京国贸店',371 union all
select 10005,198,'南京旗舰店',371 union all
select 10006,198,'川沙店',370 union all
select 10007,198,'徐州王陵路店',374 union all
select 10008,198,'芜湖中山路店',441 union all
select 10016,198,'苏果联营店江宁分部',371 union all
select 10017,198,'苏州亚细亚店',372 union all
select 10018,198,'苏州阊胥路店',372 union all
select 10019,198,'徐家汇旗舰店',370

select * from [company_info]
select * from [Shop_info]


declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + Company_Name_F from (SELECT A.Company_Name_F,b.city_id,COUNT(1) AS num
FROM [company_info] A
LEFT JOIN [Shop_info] B ON A.c_id =B.c_id
GROUP BY A.Company_Name_F,b.city_id)T group by Company_Name_F
set @sql = '[' + @sql + ']'
exec ('select * from (select * from (SELECT A.Company_Name_F,b.city_id,COUNT(1) AS num
FROM [company_info] A
LEFT JOIN [Shop_info] B ON A.c_id =B.c_id
GROUP BY A.Company_Name_F,b.city_id)T) a pivot (max(num) for Company_Name_F in (' + @sql + ')) b')

/*
city_id	Gome	HISAP	One zero	Sundan	Suning      --城市名称，lz再链接以下即可。
NULL	1	NULL	1	1	1
370	NULL	2	NULL	NULL	NULL
371	NULL	4	NULL	NULL	NULL
372	NULL	2	NULL	NULL	NULL
374	NULL	1	NULL	NULL	NULL
441	NULL	1	NULL	NULL	NULL*/

大神 太强了！ 膜拜你了！