3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
一个关于查询的问题 ，同一个表的内连接查询，高手来帮帮忙啊
被保险人 车牌号 险种 承保保费 应收保费
吴飞 鄂A9Q518 交强险 990 800
黎华 鄂ARB837 交强险 945 800
申升 鄂AEK718 交强险 945 800
申升 鄂AEK718 商业险 2000 1750
申升 鄂AEK718 车船税 420 420
小红 鄂AKL448 交强险 945 800
小红 鄂AKL448 商业险 1500 1200
要查询出的结果是：
被保险人 车牌号 商业承保保费 交强承保保费 商业应收 交强应收
吴飞 鄂A9Q518 990 800
黎华 鄂ARB837 945 800
申升 鄂AEK718 2000 945 1750 800
小红 鄂AKL448 1500 945 1200 800
语言描述就时，在上面的表中，险种有交强险、商业险、车船税三种，
如果同一个被保险人和车牌号同时有这三个险种就会生成三条数据就像被保险人叫申升的单子，
但是统计的时候只需要险种为交强险和商业险的单子，但有的单子只可能是其中的一种或两个都有
这个怎么能显示成我要的效果啊 ，请各位高手帮帮忙
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
dobear_0922 2008-01-21
dawugui
可以结贴了，老乌龟都给说完了
可以结贴了，老乌龟都给说完了
lishijie910123 2008-01-21
好的，
问题解决
结贴
问题解决
结贴
dawugui 2008-01-21
Haiwer
可以结贴了，老乌龟都给说完了
可以结贴了，老乌龟都给说完了
dawugui 2008-01-21
--完整点的.
create table tb(被保险人 varchar(10), 车牌号 varchar(10), 险种 varchar(10), 承保保费 int, 应收保费 int)
insert into tb values('吴飞', '鄂A9Q518', '交强险', 990 , 800 )
insert into tb values('黎华', '鄂ARB837', '交强险', 945 , 800 )
insert into tb values('申升', '鄂AEK718', '交强险', 945 , 800 )
insert into tb values('申升', '鄂AEK718', '商业险', 2000, 1750 )
insert into tb values('申升', '鄂AEK718', '车船税', 420 , 420 )
insert into tb values('小红', '鄂AKL448', '交强险', 945 , 800 )
insert into tb values('小红', '鄂AKL448', '商业险', 1500, 1200 )
--静态SQL
select 被保险人,车牌号,
sum(case 险种 when '商业险' then 承保保费 else 0 end) '商业承保保费',
sum(case 险种 when '商业险' then 应收保费 else 0 end) '商业应收',
sum(case 险种 when '交强险' then 承保保费 else 0 end) '交强承保保费',
sum(case 险种 when '交强险' then 应收保费 else 0 end) '交强应收',
sum(case 险种 when '车船税' then 承保保费 else 0 end) '车船税保费',
sum(case 险种 when '车船税' then 应收保费 else 0 end) '车船税应收'
from tb
group by 被保险人,车牌号
order by 被保险人,车牌号
/*
被保险人 车牌号 商业承保保费 商业应收 交强承保保费 交强应收 车船税保费 车船税应收
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
黎华 鄂ARB837 0 0 945 800 0 0
申升 鄂AEK718 2000 1750 945 800 420 420
吴飞 鄂A9Q518 0 0 990 800 0 0
小红 鄂AKL448 1500 1200 945 800 0 0
*/
--动态SQL
declare @sql varchar(8000)
set @sql = 'select 被保险人,车牌号'
select @sql = @sql + ' , sum(case 险种 when ''' + 险种 + ''' then 承保保费 else 0 end) [' + 险种 + '保费]'
+ ' , sum(case 险种 when ''' + 险种 + ''' then 应收保费 else 0 end) [' + 险种 + '应收]'
from (select distinct 险种 from tb) as a
set @sql = @sql + ' from tb group by 被保险人,车牌号 order by 被保险人,车牌号'
exec(@sql)
/*
被保险人 车牌号 车船税保费 车船税应收 交强险保费 交强险应收 商业险保费 商业险应收
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
黎华 鄂ARB837 0 0 945 800 0 0
申升 鄂AEK718 420 420 945 800 2000 1750
吴飞 鄂A9Q518 0 0 990 800 0 0
小红 鄂AKL448 0 0 945 800 1500 1200
*/
drop table tb
pt1314917 2008-01-21
create table tb(被保险人 varchar(50),车牌号 varchar(50),险种 varchar(50),承保保费 int,应收保费 int)
insert into tb select '吴飞','鄂A9Q518','交强险',990,800
insert into tb select '黎华','鄂ARB837','交强险',945,800
insert into tb select '申升','鄂AEK718','交强险',945,800
insert into tb select '申升','鄂AEK718','商业险',2000,1750
insert into tb select '申升','鄂AEK718','车船税',420,420
insert into tb select '小红','鄂AKL448','交强险',945,800
insert into tb select '小红','鄂AKL448','商业险',1500,1200
select 被保险人,车牌号,
商业承保保费=sum(case 险种 when '商业险' then 承保保费 else 0 end),
交强承保保费=sum(case 险种 when '交强险' then 承保保费 else 0 end),
商业应收=sum(case 险种 when '商业险' then 应收保费 else 0 end),
交强应收=sum(case 险种 when '交强险' then 应收保费 else 0 end)
from tb group by 被保险人,车牌号
dawugui 2008-01-21
如果只要这两种保险,上面的静态SQL就行了.
如果需要所有的,使用我2楼给出动态SQL
如果需要所有的,使用我2楼给出动态SQL
昵称被占用了 2008-01-21
可以结贴了，老乌龟都给说完了
kk19840210 2008-01-21
select 被保险人,车牌号,商业承保保费=max(case when 险种='商业险' then 承保保费 else null end),交强承保保费=max(case when 险种='交强险' then 承保保费 else null end),
商业应收=max(case when 险种='交强险' then 应收保费 else null end),交强应收=max(case when 险种='交强险' then 应收保费 else null end)
from tab group by 被保险人,车牌号dawugui 2008-01-21
create table tb(被保险人 varchar(10), 车牌号 varchar(10), 险种 varchar(10), 承保保费 int, 应收保费 int)
insert into tb values('吴飞', '鄂A9Q518', '交强险', 990 , 800 )
insert into tb values('黎华', '鄂ARB837', '交强险', 945 , 800 )
insert into tb values('申升', '鄂AEK718', '交强险', 945 , 800 )
insert into tb values('申升', '鄂AEK718', '商业险', 2000, 1750 )
insert into tb values('申升', '鄂AEK718', '车船税', 420 , 420 )
insert into tb values('小红', '鄂AKL448', '交强险', 945 , 800 )
insert into tb values('小红', '鄂AKL448', '商业险', 1500, 1200 )
select 被保险人,车牌号,
sum(case 险种 when '商业险' then 承保保费 else 0 end) '商业承保保费',
sum(case 险种 when '交强险' then 承保保费 else 0 end) '交强承保保费',
sum(case 险种 when '商业险' then 应收保费 else 0 end) '商业应收',
sum(case 险种 when '交强险' then 应收保费 else 0 end) '交强应收'
from tb
group by 被保险人,车牌号
order by 被保险人,车牌号
drop table tb
/*
被保险人 车牌号 商业承保保费 交强承保保费 商业应收 交强应收
---------- ---------- ----------- ----------- ----------- -----------
黎华 鄂ARB837 0 945 0 800
申升 鄂AEK718 2000 945 1750 800
吴飞 鄂A9Q518 0 990 0 800
小红 鄂AKL448 1500 945 1200 800
（所影响的行数为 4 行）
*/dawugui 2008-01-21
select 被保险人,车牌号,
sum(case 险种 when '商业险' then 承保保费 else 0 end) '商业承保保费',
sum(case 险种 when '交强险' then 承保保费 else 0 end) '交强承保保费',
sum(case 险种 when '商业险' then 应收保费 else 0 end) '商业应收',
sum(case 险种 when '交强险' then 应收保费 else 0 end) '交强应收'
from tb
group by 被保险人,车牌号
order by 被保险人,车牌号
dawugui 2008-01-21
/*
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)
假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/
-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)
insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/
-------------------------------------------------------------------
/*加个平均分，总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/
drop table tb
---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/
create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end
--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb1dawugui 2008-01-21
行列转换
