34,588
社区成员
发帖
与我相关
我的任务
分享关于不同表间的分类汇总sql语句问题
表1进货表：日期，货物，始发城市，重量，数量
表2出货表：日期，货物，目的城市，重量，数量
表3： 城市，省份，地区，国家
如何汇总，因为城市很多，也可能会随机增加，所需需动态结合表3，进行分类汇总。各位大侠赐教
效果应如下：
--------------------吞吐量-------------------------------
年，月，港澳台，大陆地区，华东地区，全部
表2出货表：日期，货物，目的城市，重量，数量
表3： 城市，省份，地区，国家
如何汇总，因为城市很多，也可能会随机增加，所需需动态结合表3，进行分类汇总。各位大侠赐教
效果应如下：
--------------------吞吐量-------------------------------
年，月，港澳台，大陆地区，华东地区，全部
...全文
请发表友善的回复…
发表回复
groundsky 2017-11-28
- 打赏
- 举报
感谢
道素 2017-11-28
- 打赏
- 举报
你要要创建城市和地区的一个关系表，直接在Code里写，不是变成Hard Code了，这样的习惯不好，另外一个城市应该也属于多个区域，下面举个例子，其实有些数据还可以进一步定义字典表，可以和下拉列表等绑定
下面仅是演示，如果你确实想每个区做为一个列，那么第一步的汇总可以不用，直接用明细表进行转置汇总
IF OBJECT_ID('tempdb..#in') IS NOT NULL DROP TABLE #in
IF OBJECT_ID('tempdb..#out') IS NOT NULL DROP TABLE #out
IF OBJECT_ID('tempdb..#city') IS NOT NULL DROP TABLE #city
IF OBJECT_ID('tempdb..#territory') IS NOT NULL DROP TABLE #territory
IF OBJECT_ID('tempdb..#CityTerritory') IS NOT NULL DROP TABLE #CityTerritory
CREATE TABLE #in(ProcessedDate DATETIME,GoodsID VARCHAR(100),FromCity VARCHAR(100),Weight DECIMAL(19,4),Qty INT )
CREATE TABLE #out(ProcessedDate DATETIME,GoodsID VARCHAR(100),DestCity VARCHAR(100),Weight DECIMAL(19,4),Qty INT )
CREATE TABLE #City(CityCode VARCHAR(100),CityName NVARCHAR(100),Province NVARCHAR(100),Region NVARCHAR(100),Country NVARCHAR(100))
CREATE TABLE #CityTerritory(City VARCHAR(100),Territory NVARCHAR(100))
INSERT INTO #City (CityCode,CityName,Province,Region,Country)
SELECT 'HongKong',N'香港',N'香港',NULL,N'中国' UNION
SELECT 'Ningbo',N'宁波',N'浙江',NULL,N'中国'
INSERT INTO #CityTerritory(City,Territory)
SELECT 'HongKong',N'港澳台地区' UNION
SELECT 'Ningbo',N'浙江省地区' UNION
SELECT 'Ningbo',N'华东地区' UNION
SELECT 'Ningbo',N'大陆地区'
INSERT INTO #in(ProcessedDate,GoodsID,FromCity,Weight,Qty)
SELECT DATEADD(d,-30,GETDATE()),'001','Ningbo',100,160
INSERT INTO #out(ProcessedDate,GoodsID,DestCity,Weight,Qty)
SELECT GETDATE(),'001','HongKong',100,160
IF OBJECT_ID('tempdb..#summary') IS NOT NULL DROP TABLE #summary
SELECT YEAR(t.ProcessedDate) AS yr,MONTH(t.ProcessedDate) AS mn,ct.Territory,SUM(weight) AS weight
INTO #summary
FROM (
SELECT i.ProcessedDate,i.GoodsID,i.FromCity AS City,i.Weight,i.Qty FROM #in AS i
UNION ALL --吞吐应该是入和出合计吧
SELECT o.ProcessedDate,o.GoodsID,o.DestCity,o.Weight,o.Qty FROM #out AS o
) AS t
INNER JOIN #CityTerritory AS ct ON ct.City=t.City
GROUP BY ct.Territory,YEAR(t.ProcessedDate),MONTH(t.ProcessedDate)
SELECT * FROM #summary
----转置
DECLARE @cols NVARCHAR(max),@sql NVARCHAR(max)
SELECT @cols=ISNULL(@cols+N',','')+QUOTENAME(Territory) FROM #summary
PRINT @cols
SELECT * FROM #summary AS s
PIVOT(sum(weight) FOR Territory IN ([华东地区],[大陆地区],[浙江省地区],[港澳台地区])) p
+------+----+-----------+--------+
| yr | mn | Territory | weight |
+------+----+-----------+--------+
| 2017 | 10 | 华东地区 | 100 |
| 2017 | 10 | 大陆地区 | 100 |
| 2017 | 10 | 浙江省地区 | 100 |
| 2017 | 11 | 港澳台地区 | 100 |
+------+----+-----------+--------+
+------+----+------+------+-------+-------+
| yr | mn | 华东地区 | 大陆地区 | 浙江省地区 | 港澳台地区 |
+------+----+------+------+-------+-------+
| 2017 | 10 | 100 | 100 | 100 | NULL |
| 2017 | 11 | NULL | NULL | NULL | 100 |
+------+----+------+------+-------+-------+
groundsky 2017-11-27
- 打赏
- 举报
表3 中是 城市列表，
汇总可能是浙江省，华东地区，华中地区，港澳地区等，如需对浙江省汇总，把所有城市都写到 in里面去 总是不大方便吧。
年 |月 |浙江地区出货量 （公斤）
-----------------------------
17|1 |2567
17|2 |4545
17|3 |9846
这样的效果
表3中 ，添加 杭州、宁波、余姚、湖州等字段 和 始发地、目的地 对应
groundsky 2017-11-27
- 打赏
- 举报
如何把 汇总成一个表，按年 月 汇总，关键是 始发站、目的站 不是固定的。
select year(日期) as 年，month(日期) as 月 ,sum(case when 始发站 in('香港',‘澳门’) then 重量 else 0 end ) as 港澳运货量 from 表1
select year(日期) as 年，month(日期) as 月 ,sum(case when 目的站 in('香港',‘澳门’) then 重量 else 0 end ) as 港澳运货量 from 表2
听雨停了 2017-11-27
- 打赏
- 举报
给些测试数据，汇总什么，用啥汇总。最终结果要成什么样子。建议给出来～～～
