SQL语句中的子查询应用

最新推荐文章于 2020-11-14 13:50:31 发布

weixin_34150224

最新推荐文章于 2020-11-14 13:50:31 发布

阅读量75

点赞数

CC 4.0 BY-SA版权

文章标签：面试

原文链接：http://www.cnblogs.com/eshizhan/archive/2010/01/25/1655933.html

本文介绍了一种复杂的SQL查询方法，用于统计某公司销售信息表中指定设备编号在指定日期范围内的销售情况，包括现金销售数量与金额、IC卡销售数量与金额等指标，并通过子查询实现了数据的精确提取。

某公司的面试考核题：

销售信息表 tbSaleInfo

   DeviceNo        设备编号
   GoodNo          货道编号(1-24)
   CardNo          卡号
   Money           消费金额
   Balance         卡片余额
   SaleFlowNo      售卖流水号
   SaleTime        售卖时间
   CollectionTime 采集时间
   Mode        销售类型
               Mode = 0 表示现金消费，此时卡号=0，卡片余额=0
               Mode = 4 表示持卡消费，此时卡号有效，卡片余额有效

要求：

按设备编号查询：设备编号、开始日期 - 截止日期
输出信息字段：设备编号、销售日期、现金销售数量、现金销售金额、IC卡销售数量、IC卡销售金额、总销售数量、总销售金额
现金：销售日志 Mode = 0
IC卡：销售日志 Mode = 4
指定日期范围内每天一条销售信息，按照日期排序

我的方法：

@DeviceNo ，@SaleTime 和 @SaleTime2  本别对应三个参数：设备编号、开始日期 - 截止日期。

代码

SELECT
@DeviceNo AS 设备编号,
CONVERT(varchar(10), SaleTime, 120) AS 销售日期,

(SELECT COUNT(Id) AS Expr1
FROM tbSaleInfo AS tbSaleInfo_1
WHERE (DeviceNo = @DeviceNo) AND (Mode = 0) AND (CONVERT(varchar(10), SaleTime, 120) = CONVERT(varchar(10), tbSaleInfo.SaleTime, 120))
GROUP BY CONVERT(varchar(10), SaleTime, 120))
AS 现金销售数量,

(SELECT SUM([Money]) AS Expr1
FROM tbSaleInfo AS tbSaleInfo_1
WHERE (DeviceNo = @DeviceNo) AND (Mode = 0) AND (CONVERT(varchar(10), SaleTime, 120) = CONVERT(varchar(10), tbSaleInfo.SaleTime, 120))
GROUP BY CONVERT(varchar(10), SaleTime, 120))
AS 现金销售金额,

(SELECT COUNT(Id) AS Expr1
FROM tbSaleInfo AS tbSaleInfo_1
WHERE (DeviceNo = @DeviceNo) AND (Mode = 4) AND (CONVERT(varchar(10), SaleTime, 120) = CONVERT(varchar(10), tbSaleInfo.SaleTime, 120))
GROUP BY CONVERT(varchar(10), SaleTime, 120))
AS IC卡销售数量,

(SELECT SUM([Money]) AS Expr1
FROM tbSaleInfo AS tbSaleInfo_1
WHERE (DeviceNo = @DeviceNo) AND (Mode = 4) AND (CONVERT(varchar(10), SaleTime, 120) = CONVERT(varchar(10), tbSaleInfo.SaleTime, 120))
GROUP BY CONVERT(varchar(10), SaleTime, 120))
AS IC卡销售金额,

COUNT(Id) AS 总销售数量,

SUM([Money]) AS 总销售金额
FROM tbSaleInfo
WHERE (DeviceNo = @DeviceNo) AND (SaleTime BETWEEN @SaleTime AND @SaleTime2)
GROUP BY CONVERT(varchar(10), SaleTime, 120)
ORDER BY 销售日期

我想到的貌似只有子查询了，前辈们多多指教！

转载于:https://www.cnblogs.com/eshizhan/archive/2010/01/25/1655933.html