首页新闻找找看学习计划

超难复杂查询如何写?

0
[已解决问题] 解决于 2011-04-09 13:32

我有数据库 xianshiqi  如下;
bianhao    chicun    didian 
1           17        gz
2           15        gz
3           17        sh
4           19        bj
5           19        sh
6           17        bj
7           17        bj
我想搜索出如下结果,语句应该如何写?
注意搜索语句不出现15,gz等词,因为这些是可变的数据,只出现bianhao,chicun,didian。

chicun    gz   sh   bj
15        1    0     0
17        1    1     2
19        0    1     1

xzf_fancy的主页 xzf_fancy | 初学一级 | 园豆:28
提问于:2011-03-29 11:26
< >
分享
最佳答案
0

USE TEMPDB
GO

Create Table #temp
(
bianhao
int Primary Key Identity(1,1) not null,
chicun
int,
didian
nvarchar(5)
)
GO

INSERT INTO #temp
Select 17,'gz' Union
Select 15,'gz' Union
Select 17,'sh' Union
Select 19,'bj' Union
Select 19,'sh' Union
Select 17,'bj' Union
Select 17,'bj'
GO

INSERT INTO #temp Values(17,'bj')

SELECT * from #temp

Select chicun,MAX(isnull(gz,0)) as gz,MAX(isnull(bj,0)) as bj,MAX(isnull(sh,0)) as sh From
(
Select chicun,
Case didian when 'gz' then Count(chicun) end as gz,
Case didian when 'bj' then Count(chicun) end as bj,
Case didian when 'sh' then Count(chicun) end as sh
From #temp
Group By chicun,didian
)
as a Group By chicun

写代码的小2B | 老鸟四级 |园豆:4346 | 2011-03-29 12:11
其他回答(1)
0

基本查询来的,百度 SQL交叉表

彭汉生 | 园豆:1233 (小虾三级) | 2011-03-29 12:10
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册