表A
id| 仓库|合同号| 列3
1 | 1 | 2 | 3
2 | 1 | 2 | null
3 | 4 | 2 | 6
4 | 3 | 2 | 5
表B
id| 仓库 | 零售 | 列3
1 | 1 | 2 | null
2 | 1 | 2 | null
3 | 4 | 2 | null
4 | 3 | 2 | null
表C
id| 仓库名 | 合同数 | 零售总数
1 | 1 | null | 3
2 | 1 | null | 3
3 | 4 | null | 6
4 | 3 | null | 5INSERT INTO C(合同数,仓库名) select count(*)as 合同数,仓库 as 仓库名 from A group by 仓库
INSERT INTO C(零售总数) select sum(零售)as 零售总数 from B group by 仓库
怎么把这两句和到一起?
id| 仓库|合同号| 列3
1 | 1 | 2 | 3
2 | 1 | 2 | null
3 | 4 | 2 | 6
4 | 3 | 2 | 5
表B
id| 仓库 | 零售 | 列3
1 | 1 | 2 | null
2 | 1 | 2 | null
3 | 4 | 2 | null
4 | 3 | 2 | null
表C
id| 仓库名 | 合同数 | 零售总数
1 | 1 | null | 3
2 | 1 | null | 3
3 | 4 | null | 6
4 | 3 | null | 5INSERT INTO C(合同数,仓库名) select count(*)as 合同数,仓库 as 仓库名 from A group by 仓库
INSERT INTO C(零售总数) select sum(零售)as 零售总数 from B group by 仓库
怎么把这两句和到一起?
select A.仓库 as 仓库名,count(A.仓库)as 合同数,sum(B.零售) as 零售总数 from A as A
inner join B as B
on A.仓库 = B.仓库
group by 仓库 这样似似吧
union
INSERT INTO C(零售总数) select sum(零售)as 零售总数 from B group by 仓库
group by a.仓库