基本上我有一个带有几列的表,但我只对4感兴趣。我的表看起来像这样:
accountID productID profit cost date country city quantity
2 10 25 15 2017/03/13 Afghanistan Kabul 330
18 3 45 42 2017/05/14 UK London 5300
25 14 22 17 2017/05/21 UK London 300
3 11 30 26 2017/04/23 Afghanistan Herat 400
我要实现的是按城市,国家/地区/country_quantity的比例来获取产品的总数量:
: country city city_quantity country_quantity city_percentage
Afghanistan Kabul 800 1400 0.57
Afghanistan Kandahar 400 1400 0.29
Afghanistan Herat 200 1400 0.14
UK London 6500 10000 0.65
UK Manchester 3000 10000 0.3
UK Newcastle 500 10000 0.05
到目前为止,我的脚本看起来像这样:
select country, city, sum(quantity)
from table
where date > dateadd(month,-1,getdate())
group by country, city
order by country, city
其中的条件是因为我只想要数据的最后一个月,所以认为它与之无关。
如何通过简单的脚本来实现我想要的东西?
您可以使用自加入来总和按国家/地区分组的数量进行此操作,然后使用该值来提出这样的百分比:
CREATE TABLE testTable (country VARCHAR(2), city CHAR(1), quantity DECIMAL(10,2));
INSERT INTO testTable (country, city, quantity)
VALUES
('Af', 'K', 330),
('Af', 'H', 400),
('Af', 'Q', 700),
('UK', 'L', 1000),
('UK', 'M', 500);
SELECT t.country, t.city, t.quantity AS city_quantity, q.qty AS country_quantity,
CAST(t.quantity / q.qty AS DECIMAL(10,2)) AS city_percentage
FROM testTable t
JOIN
(
SELECT z.country, SUM(z.quantity) qty
FROM testTable z
GROUP BY z.country
) q ON t.country = q.country
这将为您提供所需的结果集:
+---------+------+--------------+-----------------+-----------------+
| country | city | city_quantity| country_quantity| city_percentage |
+---------+------+--------------+-----------------+-----------------+
| Af | K | 330 | 1430 | 0.23 |
| Af | H | 400 | 1430 | 0.28 |
| Af | Q | 700 | 1430 | 0.49 |
| UK | L | 1000 | 1500 | 0.67 |
| UK | M | 500 | 1500 | 0.33 |
+---------+------+--------------+-----------------+-----------------+
请记住,您需要在任何生产查询中以零进行检查。