路过请进,求一SQL语句,在线等
表table1如下:(没有主键,因为实际表中字段较多,不存在两行数据完全相同的,现用dif字段以示区别)
name ta dif
a 1 1
b 2 2
c 3 3
a 1 4
a 2 5
b 3 6
b 2 7
我想通过查询行到的结果:
name ta=1 ta=2 ta=3
a 2 1 0
b 0 2 1
c 0 0 1
也就是想得到这个表中对应name的各个ta值分别为1,2,3的次数.
谢谢
[解决办法]
到sql板块一问就出答案了
[解决办法]
pl/sql:
select name,sum(CASE ta WHEN 1 THEN ISVALUES ELSE 0 END)as ta=1 ,sum(CASE ta WHEN 2 THEN ISVALUES ELSE 0 END)as ta=2,sum(CASE ta WHEN 3 THEN ISVALUES ELSE 0 END)as ta=3 from table1
[解决办法]
利用矩阵转置来实现的方法：
矩阵表
create table t2 (a0 varchar(20), a1 int,a2 int,a3 int )
insert t2 values( 'a ',1,0, 0)
insert t2 values( 'b ',0,1, 0)
insert t2 values( 'c ',0,0, 1)
转置：
select name, a1*ta, a2*ta, a3*ta
from t1 ,t2
where name = a0
结果：
Name col1 col2 col3
a100
a100
a200
b020
b030
b020
c003
[解决办法]
哦看错题目了，如果按值转置的化需要用另外的矩阵
矩阵：
create table t2 (a0 varchar(20), a1 int,a2 int,a3 int )
insert t2 values( '1 ',1,0, 0)
insert t2 values( '2 ',0,1, 0)
insert t2 values( '3 ',0,0, 1)
转置：
select name,
sum(case a1*ta when 0 then 0 else 1 end) ,
sum(case a2*ta when 0 then 0 else 1 end),
sum(case a3*ta when 0 then 0 else 1 end)
from t1 ,t2
where ta = a0
group by name
结果：
a210
b021
c001
[解决办法]
以下SQL语句在MSSQL中调式通过:
select name,tag,count(tag)as 次数 into #A from table1 group by tag,name
select distinct name into #C from table1
select #C.name,isnull(#A.次数,0)as 'tag=1 ' into #tag1 from #C left join #A on #A.name=#C.name and #A.tag= '1 '
select #C.name,isnull(#A.次数,0)as 'tag=2 ' into #tag2 from #C left join #A on #A.name=#C.name and #A.tag= '2 '
select #C.name,isnull(#A.次数,0)as 'tag=3 ' into #tag3 from #C left join #A on #A.name=#C.name and #A.tag= '3 '
select #tag1.name,#tag1.tag=1,#tag2.tag=2,#tag3.tag=3 from #tag1,#tag2,#tag3 where #tag1.name=#tag2.name and #tag2.name=#tag3.name
