如何求这个透视表中的库存和销售占比差异？？？

foreverjhlc

快来人吧

wuxiang_123

1. SELECT "库存",* FROM (select "男" AS 性别,门店,男比率 AS 比率 FROM (SELECT C.门店,C.女数/(C.女数+D.男数) AS 女比率,D.男数/(C.女数+D.男数) AS 男比率 FROM (SELECT A.门店,B.女数 FROM
   
2. (select distinct 门店 FROM [库存$] WHERE 门店 IS NOT NULL)A
   
3. LEFT JOIN
   
4. (select 门店,SUM(库存值) AS 女数 from [库存$] WHERE 性别="女" GROUP BY 门店)B
   
5. ON A.门店=B.门店)C
   
6. LEFT JOIN
   
7. (select 门店,SUM(库存值) AS 男数 from [库存$] WHERE 性别="男" GROUP BY 门店)D
   
8. ON C.门店=D.门店)
   
9. UNION ALL
   
10. select "女" AS 性别,门店,女比率 FROM (SELECT C.门店,C.女数/(C.女数+D.男数) AS 女比率,D.男数/(C.女数+D.男数) AS 男比率 FROM (SELECT A.门店,B.女数 FROM
    
11. (select distinct 门店 FROM [库存$] WHERE 门店 IS NOT NULL)A
    
12. LEFT JOIN
    
13. (select 门店,SUM(库存值) AS 女数 from [库存$] WHERE 性别="女" GROUP BY 门店)B
    
14. ON A.门店=B.门店)C
    
15. LEFT JOIN
    
16. (select 门店,SUM(库存值) AS 男数 from [库存$] WHERE 性别="男" GROUP BY 门店)D
    
17. ON C.门店=D.门店))
    
18. UNION ALL
    
19. SELECT "销售",* FROM (select "男" AS 性别,门店名称,男比率 AS 比率 FROM (SELECT C.门店名称,C.女数/(C.女数+D.男数) AS 女比率,D.男数/(C.女数+D.男数) AS 男比率 FROM (SELECT A.门店名称,B.女数 FROM
    
20. (select distinct 门店名称 FROM [销售$] WHERE 门店名称 IS NOT NULL)A
    
21. LEFT JOIN
    
22. (select 门店名称,SUM(金额) AS 女数 from [销售$] WHERE 性别="女" GROUP BY 门店名称)B
    
23. ON A.门店名称=B.门店名称)C
    
24. LEFT JOIN
    
25. (select 门店名称,SUM(金额) AS 男数 from [销售$] WHERE 性别="男" GROUP BY 门店名称)D
    
26. ON C.门店名称=D.门店名称)
    
27. UNION ALL
    
28. select "女" AS 性别,门店名称,女比率 FROM (SELECT C.门店名称,C.女数/(C.女数+D.男数) AS 女比率,D.男数/(C.女数+D.男数) AS 男比率 FROM (SELECT A.门店名称,B.女数 FROM
    
29. (select distinct 门店名称 FROM [销售$] WHERE 门店名称 IS NOT NULL)A
    
30. LEFT JOIN
    
31. (select 门店名称,SUM(金额) AS 女数 from [销售$] WHERE 性别="女" GROUP BY 门店名称)B
    
32. ON A.门店名称=B.门店名称)C
    
33. LEFT JOIN
    
34. (select 门店名称,SUM(金额) AS 男数 from [销售$] WHERE 性别="男" GROUP BY 门店名称)D
    
35. ON C.门店名称=D.门店名称))

复制代码

SQL参考:请验证

foreverjhlc

感谢wuxiang_123
只是可否解释下上面的sql语句