统计成绩疑难问题

[czyoooo]

Tid workid kind creattime price
1001 201 销售 2015-04-01 200
1002 201 销售 2015-04-02 100
5001 201 销售 2015-05-20 500
1001 702 调整 2015-05-20 -100

要求统计工号201报表 2015-05-20日结果为

5001 201 销售 2015-05-20 500
1001 702 调整 2015-05-20 -100


Tid= 1001第一次出现是201操作的,201是第一责任人,
所以 2015-05-20也要出现在201的报表里面

[RINK_1]

SELECT A1.* FROM TABLE_a A1 JOIN ( SELECT * FROM TABLE_a A2 WHERE NOT EXISTS (SELECT 1 FROM TABLE_a WHERE Tid=A2.Tid AND creattime<A2.creattime) ) AS A3 ON A1.Tid=A3.Tid WHERE A3.workid='201' AND A1.creattime='2015-05-20' UNION ALL SELECT A4.* FROM TABLE_a A4 WHERE A4.workid='201' AND A4.creattime='2015-05-20'

[还在加载中灬]

DECLARE @workid INT,@creattime DATETIME
SET @workid='201'
SET @creattime='2015-05-20'
SELECT * FROM TB T1
WHERE creattime=@creattime
	AND EXISTS(SELECT 1 FROM TB T2 WHERE T2.workid=@workid AND T1.Tid=T2.Tid
	AND NOT EXISTS(SELECT 1 FROM TB T3 WHERE T2.Tid=T3.Tid AND T2.creattime>T3.creattime))

[还在加载中灬]

--SQL2005可以这样
;WITH CTE AS(
	SELECT T1.*,T2.workid workid2
		,ROW_NUMBER()OVER(PARTITION BY T1.Tid ORDER BY T2.creattime)RN
	FROM TB T1
		JOIN TB T2 ON T1.Tid=T2.Tid
	WHERE T1.creattime='2015-05-20'
)
SELECT Tid,workid,kind,creattime,price
FROM CTE
WHERE workid2='201' AND RN=1

[一头大葱]

有序的运算用SQL搞起来比较绕，其实这个问题本身的逻辑并不复杂，如果是JAVA环境可以用免费的集算器来处理，代码要更直观： A1 $select * from tb order by creattime A2 =A1.select(creattime==date("2015-05-20") && (workid==201 || (t=Tid,A1.select@1(Tid==t).workid==201))) A1：执行SQL取数，按creattime排序 A2：选出creattime为2015-05-20且workid为201；或者日期为2015-05-20且相同Tid下第一条记录workid为201的记录。 这里http://blog.raqsoft.cn/?p=5186 还有些别的序运算例子。

[Tiger_Zhao]

WITH
/* 测试数据 
table1(Tid,workid,kind,creattime,price) AS (
SELECT 1001,201,'销售','2015-04-01',200 UNION ALL
SELECT 1002,201,'销售','2015-04-02',100 UNION ALL
SELECT 5001,201,'销售','2015-05-20',500 UNION ALL
SELECT 1001,702,'调整','2015-05-20',-100
), */
t1 AS (
        SELECT a.*,
               b.workid firstworkid
          FROM table1 a
   CROSS APPLY (
                    SELECT TOP 1 workid
                      FROM table1
                     WHERE table1.Tid <= a.Tid
                  ORDER BY creattime
               ) b          
         WHERE a.creattime = '2015-05-20'
)
SELECT Tid,workid,kind,creattime,price
  FROM t1
 WHERE firstworkid = 201

        Tid      workid kind creattime        price
----------- ----------- ---- ---------- -----------
       5001         201 销售 2015-05-20         500
       1001         702 调整 2015-05-20        -100