34,594
社区成员
发帖
与我相关
我的任务
分享
WITH
AKB1 AS
(SELECT YEAR(订单.订购日期) AS 年份,运货商.公司名称,COUNT(订单.发货日期) AS 运货量
FROM 订单,运货商 where 运货商.运货商ID = 订单.运货商
GROUP BY 运货商.公司名称,YEAR(订单.订购日期)),
AKB2 AS
(SELECT 年份,SUM(运货量) AS 总运货量
FROM AKB1
GROUP BY 年份),
AKB3 AS
(SELECT A.年份 as 年份,
A.公司名称 as 运货商,
A.运货量*100.0/B.总运货量 AS 占比
FROM AKB1 AS A INNER JOIN AKB2 AS B ON A.年份=B.年份)
SELECT * FROM AKB3 AS C
PIVOT ( SUM(C.占比) FOR C.年份 IN([1996],[1997],[1998]) ) TBL
;WITH cte AS (
SELECT YEAR(订单.订购日期) AS 年份,
运货商.公司名称,
COUNT(订单.发货日期) AS 运货量
FROM 订单
INNER JOIN 运货商
ON 运货商.运货商ID = 订单.运货商
GROUP BY
运货商.公司名称,
YEAR(订单.订购日期)
),cte2 AS (
SELECT 年份,SUM(运货量) AS 总运货量
FROM cte
GROUP BY 年份
)
SELECT A.年份
,A.公司名称
,A.运货量
,A.运货量*100.0/B.总运货量 AS 占比
FROM cte AS A INNER JOIN cte2 AS B ON A.年份=B.年份