求SQL语句

[cxudong]

tb
co11 col2 col3
A1 20090104 2126
A2 20090104 2126
A3 20090104 2990
A4 20090104 2988
A5 20090106 2990
A6 20090106 2990
A7 20090106 2990
A8 20090106 2489
A9 20090107 1513
A10 20090107 1513
A11 20090107 1513
A12 20090108 2990
A13 20090108 2990
A14 20090108 2990

需要生成
co11 col2 col3 Tmp
A1 20090104 2126 T
A2 20090104 2126 T
A3 20090104 2990 F
A4 20090104 2988 F
A5 20090106 2990 T
A6 20090106 2990 T
A7 20090106 2990 F
A8 20090106 2489 F
A9 20090107 1513 T
A10 20090107 1513 T
A11 20090107 1513 F
A12 20090108 2990 T
A13 20090108 2990 T
A14 20090108 2990 F

在col1和col2相同的情况下,相同的个数如果是偶数则Tmp全为T,否则最后一个为F
不知道我表述清楚没有??(不用cursor)

[usher_gml]

应该是col2和col3相同的情况下,相同的个数如果是偶数则Tmp全为T,否则最后一个为F

[cxudong]

楼上对头,笔误

[sgtzzc]

SELECT
  A.*,
  TMP=CASE 
        WHEN A.COL1=A.COL2 AND B.CNT%2=0 
          THEN 'T' 
        ELSE CASE 
               WHEN NOT EXISTS(SELECT 1 FROM TB WHERE COL1=A.COL1 AND COL2=A.COL2 AND COL1>A.COL1) 
                  THEN 'F'
                ELSE 'T'
             END
      END
FROM TB A,
     (SELECT COL1,COL2,COUNT(1) AS CNT FROM TB GROUP BY COL1,COL2) B
WHERE A.COL1=B.COL1 
AND A.COL2=B.COL2

[--小F--]

col2和col3相同吗?

[JonasFeng]

SELECT TB.COL1,TB.COL2,TB.COL3, 
      (CASE WHEN (LO.MARK % 2 = 0) THEN T
            WHEN (LO.MARK % 2 <> 0) AND TB.COL1 = LO.COL1 THEN 'F'
            ELSE 'T' END) AS TMP  
FROM TB, 
(
SELECT COUNT(1) AS MARK,COL2 + '-' + COL3  AS RID, 
       MAX(COL1) AS COL1 
FROM TB 
GROUP BY COL2 + '-' + COL3) LO 
WHERE TB.COL2 = LEFT(LO.RID,CHARINDEX('-',LO.RID)- 1) 
      AND TB.COL3 = RIGHT(LO.RID,LEN(LO.RID) - CHARINDEX('-',LO.RID))  

[olddown]

alter table tb add Tmp varchar(10) null
GO 
update tb set Tmp='T' from tb a,
(select col2,col3 from tb group by col2,col3 having count(col2)%2=0) b where a.col2=b.col2 and a.col3=b.col3
update tb set Tmp='F' where Tmp is null

还有，楼主是不是说错了，应该是col2和col3相同的情况下吧？

[sgtzzc]

if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([col1] varchar(3),[col2] INT,[col3] int)
insert [TB]
select 'A1','20090104',2126 union all
select 'A2','20090104',2126 union all
select 'A3','20090104',2990 union all
select 'A4','20090104',2988 union all
select 'A5','20090106',2990 union all
select 'A6','20090106',2990 union all
select 'A7','20090106',2990 union all
select 'A8','20090106',2489 union all
select 'A9','20090107',1513 union all
select 'A10','20090107',1513 union all
select 'A11','20090107',1513 union all
select 'A12','20090108',2990 union all
select 'A13','20090108',2990 union all
select 'A14','20090108',2990


SELECT
  A.*,
  TMP=CASE 
        WHEN B.CNT%2!=0 
             AND NOT EXISTS(SELECT 1 FROM TB WHERE COL2=A.COL2 AND COL3=A.COL3 AND CAST(RIGHT(COL1,LEN(COL1)-1) AS INT)>CAST(RIGHT(A.COL1,LEN(A.COL1)-1) AS INT))
         THEN 'F'
                ELSE 'T'
      END
FROM TB A,
     (SELECT COL2,COL3,COUNT(1) AS CNT FROM TB GROUP BY COL2,COL3) B
WHERE A.COL2=B.COL2 
AND A.COL3=B.COL3
ORDER BY CAST(RIGHT(COL1,LEN(COL1)-1) AS INT)
/**
col1 col2        col3        TMP  
---- ----------- ----------- ---- 
A1   20090104    2126        T
A2   20090104    2126        T
A3   20090104    2990        F
A4   20090104    2988        F
A5   20090106    2990        T
A6   20090106    2990        T
A7   20090106    2990        F
A8   20090106    2489        F
A9   20090107    1513        T
A10  20090107    1513        T
A11  20090107    1513        F
A12  20090108    2990        T
A13  20090108    2990        T
A14  20090108    2990        F

（所影响的行数为 14 行）
**/

[JonasFeng]

IF OBJECT_ID('TB') IS NOT NULL
   DROP TABLE TB
GO 
CREATE TABLE TB(COL1 VARCHAR(10),COL2 VARCHAR(10),COL3 VARCHAR(10))
INSERT INTO TB(COL1,COL2,COL3)
SELECT 'A1', '20090104', '2126' UNION ALL 
SELECT 'A2', '20090104', '2126' UNION ALL 
SELECT 'A3', '20090104', '2990' UNION ALL 
SELECT 'A4', '20090104', '2988' UNION ALL  
SELECT 'A5', '20090106', '2990' UNION ALL  
SELECT 'A6', '20090106', '2990' UNION ALL  
SELECT 'A7', '20090106', '2990' UNION ALL  
SELECT 'A8', '20090106', '2489' UNION ALL  
SELECT 'A9', '20090107', '1513' UNION ALL  
SELECT 'A10', '20090107', '1513' UNION ALL  
SELECT 'A11', '20090107', '1513' UNION ALL  
SELECT 'A12', '20090108', '2990' UNION ALL  
SELECT 'A13', '20090108', '2990' UNION ALL  
SELECT 'A14', '20090108', '2990' 
GO 

SELECT TB.COL1,TB.COL2,TB.COL3, 
      (CASE WHEN (LO.MARK % 2 = 0) THEN 'T' 
            WHEN (LO.MARK % 2 <> 0) AND TB.COL1 = LO.COL1 THEN 'F'
            ELSE 'T' END) AS TMP  
FROM TB, 
(
SELECT COUNT(1) AS MARK,COL2 + '-' + COL3  AS RID, 
       MAX(COL1) AS COL1 
FROM TB 
GROUP BY COL2 + '-' + COL3) LO 
WHERE TB.COL2 = LEFT(LO.RID,CHARINDEX('-',LO.RID)- 1) 
      AND TB.COL3 = RIGHT(LO.RID,LEN(LO.RID) - CHARINDEX('-',LO.RID))  


/*结果
 COL1       COL2       COL3       TMP
---------- ---------- ---------- ----
A1         20090104   2126       T
A2         20090104   2126       T
A4         20090104   2988       F
A3         20090104   2990       F
A8         20090106   2489       F
A5         20090106   2990       T
A6         20090106   2990       T
A7         20090106   2990       F
A9         20090107   1513       F
A10        20090107   1513       T
A11        20090107   1513       T
A12        20090108   2990       T
A13        20090108   2990       T
A14        20090108   2990       F

(14 行受影响)

*/

[cxudong]

谢谢大家