CREATE TABLE TESTT(NAME NVARCHAR(10) NULL,ID int null,FLAG NVARCHAR(10) NULL)INSERT INTO TESTT(NAME,ID,FLAG)
SELECT 'ZHOUJIN','1','1'
UNION
SELECT 'ZHOUJIN','2','1'
UNION
SELECT 'ZHOUJIN','3','0'
UNION
SELECT 'ZHOUJIN','4','2'
UNION
SELECT 'ZHOUJIN','5','0'
UNION
SELECT 'ZHOUJIN','6',''
UNION
SELECT 'ZHOUJIN','7','1'
SELECT NAME,ID,FLAG,
(
select flag from testt a
where
id =
(
select MAX(a.ID) from TESTT a
where a.ID < TESTT.ID
and a.NAME = TESTT.NAME
and a.FLAG != 0
)
and a.NAME = TESTT.NAME
) AS C_T
FROM TESTT drop table testt上面是我想要实现的一个功能,C_T是我想要查询的结果,有没有别的方法可以实现同样的结果,我用的DB不支持这样的查询。
(
SELECT NAME,ID,FLAG,MAX(ISNULL(BID,0)) AS MAXID FROM
(
SELECT A.NAME,A.ID,A.FLAG,B.ID AS BID,B.FLAG AS BFLAG
FROM TESTT A
LEFT JOIN
(
SELECT DISTINCT NAME,ID,FLAG
FROM TESTT
WHERE FLAG != 0
) B ON A.NAME = B.NAME
AND A.ID > B.ID
) A
GROUP BY NAME,ID,FLAG
) A LEFT JOIN (SELECT DISTINCT NAME,ID,FLAG FROM TESTT WHERE FLAG != 0) B ON A.NAME = B.NAME AND A.MAXID = B.ID