例如有一个表。销售表。
销售编号 销售日期 状态
10001 2013-01-01 13:11:11 正常
10002 2013-01-01 13:14:14 正常
10003 2013-01-01 14:14:14 作废
10004 2013-01-02 11:14:14 正常
10005 2013-01-02 11:13:14 正常
10006 2013-01-03 11:13:14 正常
我想得到数据是
销售总数 销售票据起始号 日期 作废张数
3 10001-10003 2013-01-01 1
2 10004-10005 2013-01-02 0
1 10006-10006 2013-01-03 0
求解答，谢谢，在线等。
SQL C#
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-19 11:17:44
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([销售编号] int,[销售日期] datetime,[状态] varchar(4))
insert [huang]
select 10001,'2013-01-01 13:11:11','正常' union all
select 10002,'2013-01-01 13:14:14','正常' union all
select 10003,'2013-01-01 14:14:14','作废' union all
select 10004,'2013-01-02 11:14:14','正常' union all
select 10005,'2013-01-02 11:13:14','正常' union all
select 10006,'2013-01-03 11:13:14','正常'
--------------开始查询--------------------------
select CONVERT(VARCHAR(10),[销售日期],23),count([销售编号])销售总数,sum(CASE WHEN [状态]='作废' THEN 1 ELSE 0 END )作废张数,CONVERT(VARCHAR(5),MIN([销售编号]))+'-'+CONVERT(VARCHAR(5),max([销售编号]))
from [huang]
GROUP BY CONVERT(VARCHAR(10),[销售日期],23)
----------------结果----------------------------
/*
销售总数 作废张数
---------- ----------- ----------- -----------
2013-01-01 3 1 10001-10003
2013-01-02 2 0 10004-10005
2013-01-03 1 0 10006-10006
*/