搜索

登录

注册

  • 创建于：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 
数据圈子
微信群
热门文章
如何用EXCEL绘制正态曲线
2020-04-18
几种Python包的安装方式
2020-04-18
EXCEL函数篇——计算统计函数
2020-04-18
数据分析报告，就该这么写！超详细攻略奉上
2020-04-18
社区问答
查看更多
友情链接
关于我们
爱数据社区，原中国统计网（成立于2009年），是大数据从业人员、数据应用人员、数据爱好者的学习、探讨、交流高品质社区。社区定期组织线上与线下分享活动，邀请行业数据专家、技术大牛分享数据分析、BI工具、机器学习理论和案例、数据职场、行业数字化应用、大数据技术等数据领域专业知识。
      【了解更多】
小程序
爱数据小程序
公众号
爱数据公众号
©1997-2022 上海数统教育 版权所有
        ICP认证:
        沪ICP备15042641号-3