date,province,city,sale,四個欄位，銷售日，省，市，銷售額；
1、要計算每個市的銷售匯總，然后統計輸出各個省成交額最大的城市及其成交額；
2、統計輸出各個城市有交易的天數及其平均成交額；
有沒有簡單效率快的sql查詢方法；
uj5u.com熱心網友回復：
--要計算每個市的銷售匯總
SELECT CITY,SUM(SALE) AS TOTAL
FROM TABLE
GROUP BY CITY
--統計輸出各個省成交額最大的城市及其成交額
SELECT *
FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY PROVINCE ORDER BY TOTAL DESC) AS SEQ
FROM
(SELECT PROVINCE,CITY,SUM(SALE) AS TOTAL
FROM TABLE
GROUP BY PROVINCE,CITY) AS A) AS B
WHERE SEQ=1
--統計輸出各個城市有交易的天數及其平均成交額
SELECT CITY,COUNT(DISTINCT DATE) AS DT_QTY,(SUM(SALE)*1.0)/COUNT(DISTINCT DATE) AS AVG_BY_DATE
FROM TABLE
GROUP BY CITY
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/41504.html
標籤：基礎類
上一篇：求sql server 統計方案