求两个SQL语句

Question

有一个表如下
SELECT * FROM TABLE_1 order by 日期 descwid         款号         工价                    数量          日期
----------- ---------- --------------------- ----------- -----------------------
6           A款         0.50                  1000        2010-10-10 00:00:00.000
7           A款         0.50                  600         2010-10-10 00:00:00.000
8           D款         0.20                  300         2010-10-10 00:00:00.000
3           B款         0.60                  500         2010-10-05 00:00:00.000
1           A款         0.50                  1000        2010-10-01 00:00:00.000
9           A款         0.50                  1000        2010-10-01 00:00:00.000第一个问题：如果得到以下结果日期                   款号        工价         数量
2010-10-10             A款         0.5          1600  <---1000+600,按 [款号]分组，再按 [日期]，再 SUM 同日期 [款号] 的 [数量]
2010-10-10             D款         0.2          300
2010-10-05             B款         0.6          500
2010-10-01             A款         0.5          2000第二个问题：如何得到下面的结果日期                   款号        工价         数量
2010-10-10             A款         0.5          1000
空                     A款         0.5          600
空                     D款         0.2          300
2010-10-05             B款         0.6          500
2010-10-01             A款         0.5          1000
空                     A款         0.5          1000

Answer 1

--问题1select convert(varchar(10),日期,10)日期,款号,工价,sum(数量)数量
from tb
group by 日期,款号,工价

Answer 2

select 日期,款号,工价,sum(数量) as 数量 from tb group by 日期,款号,工价

Answer 3

--问问题一
select convert(varchar(10),日期,10)日期,款号,工价,sum(数量)数量
from tb
group by convert(varchar(10),日期,10),款号,工价

Answer 4

--问题2with cte as
(
    select *,rn = row_number() over (partition by convert(varchar(10),日期,10) order by getdate())
    from tb
)select (case when rn = 1 then convert(varchar(10),日期,10) else '' end),日期,款号,工价,数量
from cte

Answer 5

select (case when no>1 then null else convert(varchar(10),日期,10) end) 日期,款号,工价,数量
 from (select row_number() over(partition by 款号 order by 日期),* from tb) a

Answer 6

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([wid] int,[款号] varchar(3),[工价] numeric(3,2),[数量] int,[日期] datetime)
insert [TB]
select 6,'A款',0.50,1000,'2010-10-10 00:00:00.000' union all
select 7,'A款',0.50,600,'2010-10-10 00:00:00.000' union all
select 8,'D款',0.20,300,'2010-10-10 00:00:00.000' union all
select 3,'B款',0.60,500,'2010-10-05 00:00:00.000' union all
select 1,'A款',0.50,1000,'2010-10-01 00:00:00.000' union all
select 9,'A款',0.50,1000,'2010-10-01 00:00:00.000'select * from [TB]--1
SELECT [日期] = CONVERT(VARCHAR(10),[日期],120),[款号],[工价],数量 = SUM([数量])
FROM TB
GROUP BY CONVERT(VARCHAR(10),[日期],120),[款号],[工价]
ORDER BY CONVERT(VARCHAR(10),[日期],120) DESC ,[款号] ASC;--2
WITH TT
AS(
SELECT *,ROW_NUMBER() OVER(partition BY [款号],CONVERT(VARCHAR(10),[日期],120) ORDER BY GETDATE()) AS num
FROM TB)SELECT [日期] = CASE WHEN num = 1 THEN CONVERT(VARCHAR(10),[日期],120) ELSE '' END ,
[款号],[工价],[数量]
FROM TT

Answer 7

--问题2 环境：SQL2000
select rn = identity(int,1,1),* into #t
from tbselect (case when (select count(*) from #t 
                   where convert(varchar(10),日期,10) = convert(varchar(10),t.日期,10)
                        and rn <= t.rn) = 1 then convert(varchar(10),日期,10) else '' end)日期,
        款号,工价,数量
from #t t

Answer 8

4楼修改！
--问题2 环境：SQL2005with cte as
(
    select *,rn = row_number() over (partition by convert(varchar(10),日期,10) order by getdate())
    from tb
)select (case when rn = 1 then convert(varchar(10),日期,10) else '' end)日期,款号,工价,数量
from cte

Answer 9

create table [TB]([wid] int,[款号] varchar(3),[工价] numeric(3,2),[数量] int,[日期] datetime)
insert [TB]
select 6,'A款',0.50,1000,'2010-10-10 00:00:00.000' union all
select 7,'A款',0.50,600,'2010-10-10 00:00:00.000' union all
select 8,'D款',0.20,300,'2010-10-10 00:00:00.000' union all
select 3,'B款',0.60,500,'2010-10-05 00:00:00.000' union all
select 1,'A款',0.50,1000,'2010-10-01 00:00:00.000' union all
select 9,'A款',0.50,1000,'2010-10-01 00:00:00.000'select (case when no>1 then null else convert(varchar(10),日期,10) end) 日期,款号,工价,数量
 from (select row_number() over(partition by 款号 order by 日期)no,* from tb) a
/*
日期         款号   工价                                      数量
---------- ---- --------------------------------------- -----------
10-01-10   A款   0.50                                    1000
NULL       A款   0.50                                    1000
NULL       A款   0.50                                    1000
NULL       A款   0.50                                    600
10-05-10   B款   0.60                                    500
10-10-10   D款   0.20                                    300