你的位置:首页 > 数据库

[数据库]给定一张表(列有月份,销售额),要求查询出月份、本月销售额、上月销售额这三个结果,如果当月上个月的销售额不存在就显示为“*”。


刚逛论坛,发现一个这样的问题,如果不建立一个新的月份的表,可以用CET来解决。

给定一张表(列有月份,销售额),要求查询出月份、本月销售额、上月销售额这三个结果,如果当月上个月的销售额不存在就显示为“*”。

if exists (select * from sysobjects where id = object_id(N'[tb2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [tb2] CREATE TABLE tb2(ID INT IDENTITY,SALEDATE DATETIME,AMOUNT FLOAT)INSERT INTO tb2SELECT '2015-05-02',20.1INSERT INTO tb2SELECT '2015-02-02',24.1INSERT INTO tb2SELECT '2015-03-02',10.4INSERT INTO tb2SELECT '2015-05-12',27.8INSERT INTO tb2SELECT '2015-06-02',70.13INSERT INTO tb2SELECT '2015-06-12',90.31INSERT INTO tb2SELECT '2016-01-06',30.22INSERT INTO tb2SELECT '2016-02-08',5.7

插入后显示表的数据

SELECT * FROM tb2

用以下sql跑出所需要的结果

WITH MON as(select CONVERT(VARCHAR(7),GETDATE(),120) AS MOUNION SELECT CONVERT(VARCHAR(7),DATEADD(M,-1,GETDATE()),120) AS MO)SELECT MO AS MONTH,CASE WHEN SUM(ISNULL(AMOUNT,0))=0 THEN '*'ELSE CAST(SUM(ISNULL(AMOUNT,0)) AS VARCHAR(10)) END AS SUM_AMOUNT FROM Mon LEFT JOIN tb2 ON MO=CONVERT(VARCHAR(7),SALEDATE,120)GROUP BY MO

如果还有其他好的方法,希望互相交流下,谢谢!