通用查询图表配置示例

[复制链接]
发表于 2016-4-14 10:04:23 | 显示全部楼层 |阅读模式
本帖最后由 zhouyangping 于 2016-4-14 10:06 编辑

简单图表
  1. SELECT sMonth=CONVERT(NVARCHAR(7),a.dReceivedDate,120),
  2. nQty=SUM(c.nQty)
  3. FROM dbo.sdOrderHdr a
  4. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  5. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  6. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  7. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  8. GROUP BY CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  9. ORDER BY 1
复制代码
1.png
显示效果
2.png

多系列图表示例一
  1. SELECT sSalesName=LEFT(d.sSalesName,1)+'**'
  2. ,sMonth=CONVERT(NVARCHAR(7),a.dReceivedDate,120),nQty=SUM(c.nQty)
  3. FROM dbo.sdOrderHdr a
  4. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  5. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  6. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  7. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  8. GROUP BY LEFT(d.sSalesName,1)+'**',CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  9. ORDER BY 1,2,3 desc
复制代码


3.png
显示效果


4.png

多系列图表示例二


  1. SELECT sMonth=CONVERT(NVARCHAR(7),a.dReceivedDate,120),
  2. 业务员_陈=SUM(CASE WHEN d.sSalesName  LIKE '陈%' then c.nQty ELSE 0 END ) ,
  3. 业务员_方=SUM(CASE WHEN d.sSalesName  LIKE '方%' then c.nQty ELSE 0 END ) ,
  4. 业务员_高=SUM(CASE WHEN d.sSalesName  LIKE '高%' then c.nQty ELSE 0 END )
  5. FROM dbo.sdOrderHdr a
  6. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  7. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  8. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  9. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  10. GROUP BY CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  11. ORDER BY 1
复制代码


5.png
显示效果

6.png
堆叠效果
7.png
8.png


回复

使用道具 举报

 楼主| 发表于 2016-4-14 10:30:47 | 显示全部楼层
本帖最后由 zhouyangping 于 2016-4-14 10:41 编辑

图表混排示例

  1. SELECT 年月=CONVERT(NVARCHAR(7),a.dReceivedDate,120),
  2. 数量=SUM(c.nQty),
  3. 金额=SUM(c.nAmount)
  4. FROM dbo.sdOrderHdr a
  5. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  6. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  7. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  8. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  9. GROUP BY CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  10. ORDER BY 1
复制代码

表格设置
1.png

柱状图设置
2.png

线状图设置
3.png

显示效果
4.png

回复

使用道具 举报

 楼主| 发表于 2016-4-14 10:41:03 | 显示全部楼层
图表混排重叠效果

  1. SELECT 年月=CONVERT(NVARCHAR(7),a.dReceivedDate,120),
  2. 数量=SUM(c.nQty),
  3. 金额=SUM(c.nAmount)
  4. FROM dbo.sdOrderHdr a
  5. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  6. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  7. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  8. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  9. GROUP BY CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  10. ORDER BY 1
复制代码



表格设置
5.png

柱线图设置
6.png

显示效果
7.png
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关注公众号
Archiver|手机版|小黑屋|上海环思

相关侵权、举报、投诉及建议等,请发 E-mail:admin@discuz.vip

Powered by Discuz! X5.0 © 2001-2026 Discuz! Team.|沪ICP备11022428号

在本版发帖
关注公众号
QQ客服返回顶部