小sql难么？

Question

Employee    Date         Amount
010        2010-04-01      5
010        2010-04-01      7
011        2010-04-01     19
014        2010-04-01     15
010        2010-04-02      8
013        2010-04-03     13
011        2010-04-04      4
....
010        2010-04-30     12
015        2010-04-30      8
如何得到下面这样的排序：
Date         Employee   Amount   Employee2    Amount2
2010-04-01    011         19        014          15 
...
也就是每天Sum(Amount)前2位的Employee 以及该人的当天合计数 。
如有不清楚的地方，尽请发问，谢了

Answer 1

---------------------------------
--  Author: liangCK 小梁
--  Title : 查每个分组前N条记录
--  Date  : 2008-11-13 17:19:23
-----------------------------------> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)
INSERT INTO #T
SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL
SELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALL
SELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALL
SELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALL
SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALL
SELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALL
SELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALL
SELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALL
SELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALL
SELECT '010',3,'黄占涛','SQL技术手册','2006-01-01'--SQL查询如下:--按GID分组,查每个分组中Date最新的前2条记录
--1.字段ID唯一时:
SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)--2.如果ID不唯一时:
SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)--SQL Server 2005 使用新方法--3.使用ROW_NUMBER()进行排位分组
SELECT ID,GID,Author,Title,Date
FROM
(
   SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),*
   FROM #T
) AS T
WHERE rid<=2--4.使用APPLY
SELECT DISTINCT b.*
FROM #T AS a
CROSS APPLY
(
    SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC
) AS b
--结果
/*ID   GID         Author                        Title                                   Date
---- ----------- ----------------------------- --------------------------------------- -----------------------
003  1           格罗夫Groff.J.R.                 SQL完全手册                                 2009-07-01 00:00:00.000
004  1           KalenDelaney                  SQLServer2005技术内幕存储引擎                   2008-08-01 00:00:00.000
005  2           Alex.Kriegel.Boris.M.Trukhnov SQL宝典                                   2007-10-05 00:00:00.000
007  2           胡百敬                           SQLServer2005数据库开发详解                    2008-06-15 00:00:00.000
009  3           赵松涛                           SQLServer2005系统管理实录                     2008-10-01 00:00:00.000
010  3           黄占涛                           SQL技术手册                                 2006-01-01 00:00:00.000(6 行受影响)
*/--得到每组前几条数据
--假設每組Col1中， Col3不會重復--建立測試環境
Create Table TEST
(Col1 Varchar(10),
 Col2 Varchar(10),
 Col3 Int)
--插入數據
Insert TEST Select 'BD1V','Label', 4
Union All Select 'BD1V', 'BATT', 2
Union All Select 'BD1V', 'ODD', 3
Union All Select 'BD1V', 'HDD', 5
Union All Select 'BD1V', 'LCD', 1
Union All Select 'BD1W','HDD', 3
Union All Select 'BD1W','RAM', 8
Union All Select 'BD1W','TP CABLE', 5
Union All Select 'BD1W','LCD', 6
Union All Select 'BD1W','Label', 2
Union All Select 'BL3', 'LCD CABLE', 7
Union All Select 'BL3', 'LABEL', 6
Union All Select 'BL3', 'LCD', 5
Union All Select 'BL3', 'RAM', 1
Union All Select 'BL3D', 'Label', 4
GO
--測試
--方法一：
Select Col1, Col2, Col3 From TEST A
Where (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3) < 3
Order By Col1, Col3 Desc
--方法二：
Select Col1, Col2, Col3 From TEST A
Where Exists (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3 Having Count(*) < 3)
Order By Col1, Col3 Desc
--方法三：
Select Col1, Col2, Col3 From TEST A
Where Col3 In (Select TOP 3 Col3 From TEST Where Col1 = A.Col1 Order By Col3 Desc)
Order By Col1, Col3 Desc
GO
--刪除測試環境
Drop Table TEST
--結果
/*
Col1  Col2   Col3
BD1V HDD  5
BD1V Label  4
BD1V ODD  3
BD1W RAM  8
BD1W LCD   6
BD1W TP CABLE 5
BL3  LCD CABLE 7
BL3  LABEL  6
BL3  LCD   5
BL3D Label  4
*/

Answer 2

use PracticeDB
go
if exists(select 1 from sysobjects where name='tb')
drop table tb
go
create table tb(Employee varchar(5),Date date,Amount int )
insert into tb
select '010', '2010-04-01', 5 union all
select '010', '2010-04-01', 7 union all
select '011', '2010-04-01', 19 union all
select '014', '2010-04-01', 15 union all
select '010', '2010-04-02', 8 union all
select '013', '2010-04-03', 13 union all
select '011', '2010-04-04', 4 union all
select '010', '2010-04-30', 12 union all
select '015', '2010-04-30', 8;with t
as
(
select rn=ROW_NUMBER() over(partition by date order by amount desc),*
from tb
)
select date,Employee=max(case when rn=1 then Employee else null end),Amount=sum(case when rn=1 then Amount else 0 end),
            Employee2=max(case when rn=2 then Employee else null end),Amount2=sum(case when rn=2 then Amount else 0 end)
from t
group by datedate Employee Amount Employee2 Amount2
2010-04-01 011 19 014 15
2010-04-02 010 8 NULL 0
2010-04-03 013 13 NULL 0
2010-04-04 011 4 NULL 0
2010-04-30 010 12 015 8

Answer 3

DECLARE @A TABLE(Employee VARCHAR(3), Date VARCHAR(20), Amount INT)
INSERT @A SELECT '010' ,'2010-04-01', 5
UNION ALL SELECT '010' ,'2010-04-01', 7
UNION ALL SELECT '011' ,'2010-04-01', 19
UNION ALL SELECT '014' ,'2010-04-01', 15
UNION ALL SELECT '010' ,'2010-04-02', 8
UNION ALL SELECT '013' ,'2010-04-03', 13
UNION ALL SELECT '011' ,'2010-04-04', 4
UNION ALL SELECT '010' ,'2010-04-30', 12
UNION ALL SELECT '015' ,'2010-04-30', 8DECLARE @B TABLE(Employee VARCHAR(3), Date VARCHAR(20), Amount INT)
DECLARE @C TABLE(Employee VARCHAR(3), Date VARCHAR(20), Amount INT,ID INT)
INSERT @B 
SELECT EMPLOYEE,DATE,SUM(AMOUNT) FROM @A A 
GROUP BY DATE,A.Employee
ORDER BY DATE,A.EmployeeINSERT @C 
SELECT *,C=(SELECT COUNT(1)+1 FROM @B WHERE DATE=A.DATE AND AMOUNT>A.AMOUNT) FROM @B A 
SELECT * FROM @CSELECT DATE,
EMPLOYEE=MAX(CASE WHEN ID=1 THEN EMPLOYEE END),
AMOUNT=MAX(CASE WHEN ID=1 THEN AMOUNT END),
EMPLOYEE2=MAX(CASE WHEN ID=2 THEN EMPLOYEE END),
AMOUNT2=MAX(CASE WHEN ID=2 THEN AMOUNT END)
FROM @C A GROUP BY Date--RESULT
/*DATE                 EMPLOYEE AMOUNT      EMPLOYEE2 AMOUNT2     
-------------------- -------- ----------- --------- ----------- 
2010-04-01           011      19          014       15
2010-04-02           010      8           NULL      NULL
2010-04-03           013      13          NULL      NULL
2010-04-04           011      4           NULL      NULL
2010-04-30           010      12          015       8（所影响的行数为 5 行）*/

Answer 4

3楼兄弟，你是在SQLserver么，怎么我的查询分析器提示
服务器: 消息 195，级别 15，状态 1，行 16
'ROW_NUMBER' 不是可以识别的 函数名。

Answer 5

6楼兄弟：
SELECT *,C=(SELECT COUNT(1)+1 FROM @B WHERE DATE=A.DATE AND AMOUNT>A.AMOUNT) 如果2条记录的Amount一样，那么ID就区分不出来了

Answer 6

一样的也要排个1，2出来啊，难道Amount一样，就省略 了？
当天有2个员工都卖掉相同数量的产品，怎么排？不会随便省略一个人当没有吧

Answer 7

你的意思是如果两个人都是80，且最高即两个都排第一，那employee随便填一个，employee2再填另一个？
但如果两个人都是80，前面有一个90的即两个都排第二，那employee2随便填一个？

Answer 8

UNION ALL SELECT '011' ,'2010-04-01', 19
UNION ALL SELECT '014' ,'2010-04-01', 15这两条，你可以试着吧后面都改成15，或者都改成19，就有一条记录在结果出不来，那么的话，不就有一个人虽然也是卖掉当天最多数量，却被忽略了么？

Answer 9

DECLARE @A TABLE(Employee VARCHAR(3), Date VARCHAR(20), Amount INT)
INSERT @A SELECT '010' ,'2010-04-01', 5
UNION ALL SELECT '010' ,'2010-04-01', 7
UNION ALL SELECT '011' ,'2010-04-01', 19
UNION ALL SELECT '014' ,'2010-04-01', 19
UNION ALL SELECT '010' ,'2010-04-02', 8
UNION ALL SELECT '013' ,'2010-04-03', 13
UNION ALL SELECT '011' ,'2010-04-04', 4
UNION ALL SELECT '010' ,'2010-04-30', 12
UNION ALL SELECT '015' ,'2010-04-30', 8
DECLARE @B TABLE(ID INT IDENTITY(1,1),Employee VARCHAR(3), Date VARCHAR(20), Amount INT)
DECLARE @C TABLE(Employee VARCHAR(3), Date VARCHAR(20), Amount INT,ID INT)
INSERT @B(Employee, Date, Amount)
SELECT EMPLOYEE,DATE,SUM(AMOUNT) FROM @A A 
GROUP BY DATE,A.Employee
ORDER BY DATE,A.Employee
INSERT @C 
SELECT EMPLOYEE,DATE,AMOUNT,C=(SELECT COUNT(1)+1 FROM @B WHERE DATE=A.DATE AND (AMOUNT>A.AMOUNT OR (AMOUNT=A.AMOUNT AND ID>A.ID))) FROM @B A SELECT DATE,
EMPLOYEE=MAX(CASE WHEN ID=1 THEN EMPLOYEE END),
AMOUNT=MAX(CASE WHEN ID=1 THEN AMOUNT END),
EMPLOYEE2=MAX(CASE WHEN ID=2 THEN EMPLOYEE END),
AMOUNT2=MAX(CASE WHEN ID=2 THEN AMOUNT END)
FROM @C A GROUP BY Date--RESULT
/*
DATE                 EMPLOYEE AMOUNT      EMPLOYEE2 AMOUNT2     
-------------------- -------- ----------- --------- ----------- 
2010-04-01           014      19          011       19
2010-04-02           010      8           NULL      NULL
2010-04-03           013      13          NULL      NULL
2010-04-04           011      4           NULL      NULL
2010-04-30           010      12          015       8（所影响的行数为 5 行）*/