有如下2个表结构
create table a
(
id int,
name varchar(100)
)
insert into a values(1,'宁波')
insert into a values(2,'存储')
insert into a values(3,'宁波')
insert into a values(4,'宁波')
insert into a values(4,'余姚')
insert into a values(4,'温州')
insert into a values(6,'余姚')create table a2
(
id int,
name int,
id2 int,
name2 int
)
现在要将下面的查询出来的两条数据同时插入到表a2中去.请注意是同时插入....SELECT COUNT(*),SUM(id) FROM a WHERE name = '宁波' 这条语句插入对应的 id,nameSELECT COUNT(*),SUM(id) FROM a WHERE name = '余姚' 这条语句插入对应的 id2,name2
插入表a2后查询出的结果应该是:id name id2 name2
3 8 2 10
create table a
(
id int,
name varchar(100)
)
insert into a values(1,'宁波')
insert into a values(2,'存储')
insert into a values(3,'宁波')
insert into a values(4,'宁波')
insert into a values(4,'余姚')
insert into a values(4,'温州')
insert into a values(6,'余姚')create table a2
(
id int,
name int,
id2 int,
name2 int
)
现在要将下面的查询出来的两条数据同时插入到表a2中去.请注意是同时插入....SELECT COUNT(*),SUM(id) FROM a WHERE name = '宁波' 这条语句插入对应的 id,nameSELECT COUNT(*),SUM(id) FROM a WHERE name = '余姚' 这条语句插入对应的 id2,name2
插入表a2后查询出的结果应该是:id name id2 name2
3 8 2 10
select id,name,id2,name2
from (
SELECT COUNT(*) as id,SUM(id) as name FROM a WHERE name = '宁波'
) as t1,(
SELECT COUNT(*) as id2,SUM(id) as name2 FROM a WHERE name = '余姚'
) as t2
id=sum(case when name = '宁波' then 1 else 0 end),
name=sum(case when name = '宁波' then id else 0 end),
id=sum(case when name = '余姚' then 1 else 0 end),
name=sum(case when name = '余姚' then id else 0 end)
from
a
(
id int,
name varchar(100)
)
insert into a values(1,'宁波')
insert into a values(2,'存储')
insert into a values(3,'宁波')
insert into a values(4,'宁波')
insert into a values(4,'余姚')
insert into a values(4,'温州')
insert into a values(6,'余姚')create table a2
(
id int,
name int,
id2 int,
name2 int
)insert a2
select
id=sum(case when name = '宁波' then 1 else 0 end),
name=sum(case when name = '宁波' then id else 0 end),
id=sum(case when name = '余姚' then 1 else 0 end),
name=sum(case when name = '余姚' then id else 0 end)
from
aselect * from a2
/**
id name id2 name2
----------- ----------- ----------- -----------
3 8 2 10(所影响的行数为 1 行)
**/drop table a,a2