比如有个表test_tab 记录如下:tid tname tvalue
1 a 1
1 b 2
1 c 12
2 a 3
2 b 4
2 c 34
3 a 5
3 b 6
3 c 56
现在要求实现分组tid tvalue1 tvalue2 tvalue3
1 1 2 12
2 3 4 34
3 5 6 56现在用以下语句可以实现分组:
select tid,
sum(case tname when 'a' then tvalue else 0 end) tvalue1,
sum(case tname when 'b' then tvalue else 0 end) tvalue2,
sum(case tname when 'c' then tvalue else 0 end) tvalue3
from tb
group by tid;
我这里还有一个表:new_tabnew_id new_name new_text
1 aa a_1
2 bb b_1现在要对new_tab 和 分组后的test_tab 做一个左连接查询 ,条件为new_id = tid结果为:
new_id new_name tvalue1 tvalue2 tvalue3请问这个语句怎么写?
1 a 1
1 b 2
1 c 12
2 a 3
2 b 4
2 c 34
3 a 5
3 b 6
3 c 56
现在要求实现分组tid tvalue1 tvalue2 tvalue3
1 1 2 12
2 3 4 34
3 5 6 56现在用以下语句可以实现分组:
select tid,
sum(case tname when 'a' then tvalue else 0 end) tvalue1,
sum(case tname when 'b' then tvalue else 0 end) tvalue2,
sum(case tname when 'c' then tvalue else 0 end) tvalue3
from tb
group by tid;
我这里还有一个表:new_tabnew_id new_name new_text
1 aa a_1
2 bb b_1现在要对new_tab 和 分组后的test_tab 做一个左连接查询 ,条件为new_id = tid结果为:
new_id new_name tvalue1 tvalue2 tvalue3请问这个语句怎么写?
a.tid,
sum(case tname when 'a' then tvalue else 0 end) tvalue1,
sum(case tname when 'b' then tvalue else 0 end) tvalue2,
sum(case tname when 'c' then tvalue else 0 end) tvalue3
from
test_tab a let join new_tab b
on
a.tid=b.new_id
group by
a.tid;
a.tid,
sum(case tname when 'a' then tvalue else 0 end) tvalue1,
sum(case tname when 'b' then tvalue else 0 end) tvalue2,
sum(case tname when 'c' then tvalue else 0 end) tvalue3
from
test_tab a left join new_tab b--掉了个f
on
a.tid=b.new_id
group by
a.tid;
我的new_tab表还有字段;我想把new_tab字段也查出来;
a.tid,
sum(case tname when 'a' then tvalue else 0 end) tvalue1,
sum(case tname when 'b' then tvalue else 0 end) tvalue2,
sum(case tname when 'c' then tvalue else 0 end) tvalue3
from
test_tab a right join new_tab b--掉了个f
on
a.tid=b.new_id
group by
a.tid;
from (
select tid,
sum(case tname when 'a' then tvalue else 0 end) tvalue1,
sum(case tname when 'b' then tvalue else 0 end) tvalue2,
sum(case tname when 'c' then tvalue else 0 end) tvalue3
from tb
group by tid)T1,new_tab T2
where T1.new_id =T2.tid
select
a.tid,
sum(case tname when 'a' then tvalue else 0 end) tvalue1,
sum(case tname when 'b' then tvalue else 0 end) tvalue2,
sum(case tname when 'c' then tvalue else 0 end) tvalue3
from
test_tab a full join new_tab b
on
a.tid=b.new_id
group by
a.tid;
insert into test_tab values(1 ,'a', 1)
insert into test_tab values(1 ,'b', 2)
insert into test_tab values(1 ,'c', 12)
insert into test_tab values(2 ,'a', 3)
insert into test_tab values(2 ,'b', 4)
insert into test_tab values(2 ,'c', 34)
insert into test_tab values(3 ,'a', 5)
insert into test_tab values(3 ,'b', 6)
insert into test_tab values(3 ,'c', 56)
create table new_tab(new_id int,new_name varchar(10),new_text varchar(10))
insert into new_tab values(1, 'aa', 'a_1')
insert into new_tab values(2, 'bb', 'b_1')
goselect tid,
sum(case tname when 'a' then tvalue else 0 end) tvalue1,
sum(case tname when 'b' then tvalue else 0 end) tvalue2,
sum(case tname when 'c' then tvalue else 0 end) tvalue3
from test_tab
group by tid
/*
tid tvalue1 tvalue2 tvalue3
----------- ----------- ----------- -----------
1 1 2 12
2 3 4 34
3 5 6 56(所影响的行数为 3 行)
*/select m.new_id,m.new_name ,
sum(case n.tname when 'a' then n.tvalue else 0 end) tvalue1,
sum(case n.tname when 'b' then n.tvalue else 0 end) tvalue2,
sum(case n.tname when 'c' then n.tvalue else 0 end) tvalue3
from new_tab m left join test_tab n
on m.new_id = n.tid
group by m.new_id ,m.new_name
/*
new_id new_name tvalue1 tvalue2 tvalue3
----------- ---------- ----------- ----------- -----------
1 aa 1 2 12
2 bb 3 4 34(所影响的行数为 2 行)
*/select n.tid,m.new_name ,
sum(case n.tname when 'a' then n.tvalue else 0 end) tvalue1,
sum(case n.tname when 'b' then n.tvalue else 0 end) tvalue2,
sum(case n.tname when 'c' then n.tvalue else 0 end) tvalue3
from test_tab n left join new_tab m
on n.tid = m.new_id
group by n.tid ,m.new_name
order by n.tid
/*
tid new_name tvalue1 tvalue2 tvalue3
----------- ---------- ----------- ----------- -----------
1 aa 1 2 12
2 bb 3 4 34
3 NULL 5 6 56(所影响的行数为 3 行)
*/drop table test_tab,new_tab