救命啊！ 如何对来自不同表的代理事务进行计数和求和。 在代理卖方表上，每笔交易都已经由代理出售，由每个代理计数和总和。 mike$1800.00和ray$400.00。 如何在SQL上做到这一点？
我只希望显示1个座席。。 麦克
Mike已售商品=4
mike收入总额=$1800
ray Saled item=2
ray收入总额=400美元
对不起，我英语不好
price table
-----------------------------------------
| comics | type | price |comic_id |
|----------------------------------------|
|comic1 | sci-fi | $500.00 | 1 |
|comic2 | History | $200.00 | 2 |
|comic3 | Horror | $350.00 | 3 |
-----------------------------------------
Agent seller Table
-----------------------------------------------
|Agent| transaction_id| datesold | comic_id |
-----------------------------------------------
|mike | 93847534eara33| 2020-00-01 | 1 |
|mike | 933sdf34eara33| 2020-00-01 | 3 |
|mike | 4ssffss4eara33| 2020-00-01 | 1 |
|mike | efeaf334aafafa| 2020-00-02 | 1 |
|ray | 4223r3qraa4322| 2020-00-01 | 2 |
|ray | aerar3qraa4322| 2020-00-01 | 2 |
-----------------------------------------------
考虑如下表结构:
price table
-----------------------------------------
| comics | type | price |comic_id |
|----------------------------------------|
|comic1 | sci-fi | $500.00 | 1 |
|comic2 | History | $200.00 | 2 |
|comic3 | Horror | $350.00 | 3 |
-----------------------------------------
Agent seller Table
-----------------------------------------------
|Agent| transaction_id| datesold | comic_id |
-----------------------------------------------
|mike | 93847534eara33| 2020-00-01 | 1 |
|mike | 933sdf34eara33| 2020-00-01 | 3 |
|mike | 4ssffss4eara33| 2020-00-01 | 1 |
|mike | efeaf334aafafa| 2020-00-02 | 1 |
|ray | 4223r3qraa4322| 2020-00-01 | 2 |
|ray | aerar3qraa4322| 2020-00-01 | 2 |
-----------------------------------------------
您的查询如下所示:
SELECT Agent, sum(price) as total_agent_revenue, count(comics) as sold_item_count
FROM price
JOIN agent on price.comic_id = agent.comic_id
GROUP BY Agent
结果:
