搜索

登录

注册

  • 创建于:2019-12-03 12:00:31
    郭海燕
    查询做字段

    通常查询都是多行的,那么用查询做字段时,那多行的数据时怎么理解的?不应该时每一行作为一个字段吗?怎么还是一个字段而已。难道说子查询做字段的要求是单行单列?

12个回答
  •  

    JaryYuan

    2019-12-04 22:27:02

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码及结果:

    点赞 1 回复 0
  •  

    JaryYuan

    2019-12-04 22:25:24


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    


    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:25:01

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码:


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    


    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:24:54

    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:24:39

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码:


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    
    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:24:39

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码:


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    
    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:24:38

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码:


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    
    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:24:38

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码:


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    
    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:24:37

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码:


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    
    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:24:37

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码:


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    
    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:24:36

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码:


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    
    点赞 0 回复 0
  •  

    JaryYuan

    2019-12-04 22:24:32

    字段的意思是列,可以多行但是只能一列。或者说行自动对齐。如果要多列的话,再添加一个子查询就行了。参考以下练习代码:


    SELECT  dimMemberID,
    		SUM(AMT) as Amount,
    		CONCAT(	
    			  ROUND(100*SUM(AMT)/(SELECT SUM(AMT)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as AmountPct,
    		COUNT(salesNo) as Orderss,
    		CONCAT(	
    			  ROUND(100*COUNT(salesNo)/(SELECT COUNT(salesNo)  FROM dw.fct_sales fs WHERE dimDateID = "20170702" AND dimMemberID <> 0),2), "%"
    			  ) as OrderPct	  
    FROM dw.fct_sales
    WHERE dimDateID = "20170702" AND dimMemberID <> 0
    GROUP BY dimMemberID
    ORDER BY Amount DESC
    
    点赞 0 回复 0

©1997-2022 上海数统教育 版权所有 ICP认证: 沪ICP备15042641号-3