搜索

登录

注册

  • 来自话题:2019-12-03 12:00:31
    郭海燕
    • 关注

      0

    • 问答

      12

    查询做字段

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

回复
  • JaryYuan

       

    2019-12-04 22:27:02

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

    点赞 0 回复 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