表1:
ID Unit area price
1 单位1 重庆 2
2 单位2 宁波 4
3 单位3 宁波 6
表名:a2
ID Unit area price
1 单位3 美国 35
2 单位4 英国 23
3 单位2 重庆 5得到以下结果:
表1收入:12 --(2+4+6)
地税收入:58 --(35+23)
其中:重庆:7 --(2+5)
宁波:10 --(4+6)
美国:35 SQL语句怎么写?
ID Unit area price
1 单位1 重庆 2
2 单位2 宁波 4
3 单位3 宁波 6
表名:a2
ID Unit area price
1 单位3 美国 35
2 单位4 英国 23
3 单位2 重庆 5得到以下结果:
表1收入:12 --(2+4+6)
地税收入:58 --(35+23)
其中:重庆:7 --(2+5)
宁波:10 --(4+6)
美国:35 SQL语句怎么写?
SELECT SUM(PRICE)AS 表1收入 FROM TB1
UNION ALL
SELECT SUM(PRICE)AS 地税收入 FROM TB2
UNION ALL
SELECT area,SUM(PRICE)PRICE
FROM
(SELECT area,PRICE FROM TB1
UNION ALL
SELECT area,PRICE FROM TB2)AS T
GROUP BY area
UNION ALL
SELECT SUM(PRICE)AS 地税收入 FROM TB2
UNION ALL
SELECT PRICE FROM
(SELECT area,SUM(PRICE)PRICE
FROM
(SELECT area,PRICE FROM TB1
UNION ALL
SELECT area,PRICE FROM TB2)AS T
GROUP BY area) AS T
select sum(price) from a1
地税收入
select sum(price) from a2 where a2.area not in (select area from a1)
其中
select area, sum(price) from ( select * from a1 UNION ALL select * from a2) group by area
与
SELECT area,SUM(PRICE)PRICE列数不同怎么union???
UNION ALL
SELECT SUM(PRICE)AS 地税收入 FROM TB2
UNION ALL
SELECT PRICE FROM
(SELECT area,SUM(PRICE)PRICE
FROM
(SELECT area,PRICE FROM TB1
UNION ALL
SELECT area,PRICE FROM TB2)AS T
GROUP BY area) AS T
(ID int identity(1,1),
Unit varchar(20),
area varchar(20),
price int
)create table a2
(ID int identity(1,1),
Unit varchar(20),
area varchar(20),
price int
)insert a1
select '单位1','重庆',2
union all
select '单位2','宁波',4
union all
select '单位3','宁波',6insert a2
select '单位3','美国',35
union all
select '单位4','英国',23
union all
select '单位2','重庆',5
SELECT SUM(PRICE)AS PRICE FROM a1
UNION ALL
SELECT SUM(PRICE)AS PRICE FROM a2
where a2.area not in (select area from a1)
UNION ALL
SELECT PRICE FROM
(SELECT area,SUM(PRICE)PRICE
FROM
(SELECT area,PRICE FROM a1
UNION ALL
SELECT area,PRICE FROM a2)AS T1
GROUP BY area) AS T2