SQL筛选取多个字段值语法

trans表
date room transcode amount
2015-05-11 711 1 298
2015-05-10 421 2 398
2015-05-08 711 1 298
2015-05-09 601 1 298
2015-05-10 622 2 498
2015-05-10 501 2 368
2015-05-11 711 1 298
2015-05-09 431 2 238

查询结果：
date room transcode amount
2015-05-11 711 1 298
2015-05-11 711 1 298

我读取的语句：
SELECT *
FROM Trans
WHERE (YEAR([Date]) = YEAR({ fn NOW() })) AND (MONTH([Date]) = MONTH({ fn NOW() }))
AND (DAY([Date]) = DAY({ fn NOW() }))

SELECT DISTINCT *
FROM Trans main
WHERE (NOT EXISTS
(SELECT *
FROM trans sub
WHERE main.amount = sub.amount AND main.room = sub.room AND main.TransCode <= 2 = TransCode <= 2))

上面的语法在<=2处报错

SELECT DISTINCT *
FROM Trans main
WHERE (NOT EXISTS
(SELECT *
FROM trans sub
WHERE main.room = sub.room AND main.TransCode = TransCode AND main.amount = sub.amount))

这个又查询不出来结果，出来的是全部，并且room字段显示NULL.

求高手指导，SQL取系统时间=date transcode<=2 amount金额相同 显示三个字段值相同的房间号及三个字段数据。

 select m.* from trans m,
(
select date,room,amount,transcode,COUNT(*) cnt from trans where date=CONVERT(varchar(100), GETDATE(), 23) 
and transcode<=2  group by date,room,amount,transcode
) t 
where t.room=m.room 
and t.amount=m.amount 
and t.transcode = m.transcode 
and t.date = m.date 
and cnt>1  --cnt>1表示有相同的，起码2条

    -- (SQL Server) 先统计重复个数 dupCount，再过滤
    ;WITH t AS (
        SELECT *,
               COUNT(*) OVER(PARTITION BY date,room,transcode,amount) dupCount
          FROM trans
         WHERE transCode <= 2
           AND DateDiff(day,date,GetDate()) = 0 -- 到底要不要过滤日期？
    )
    SELECT date,room,transcode, amount
      FROM t
     WHERE dupCount > 1

Microsoft VBScript 编译器错误 错误 '800a0408' 无效字符 /hycom/hyquery/amountRepeat.asp，行 24 sql="SELECT m.* FROM Trans m INNER JOIN (SELECT date, room, amount, transcode, COUNT(*) cnt FROM trans WHERE date = CONVERT(varchar(100), GETDATE(), 23) AND transcode <= 2 GROUP BY date, room, amount, transcode) t ON m.Room = t.room AND m.Amount = t.amount AND m.TransCode = t.transcode AND m.[Date] = t.[date] WHERE (t.cnt > 1 if rs.recordcount <1 then response.write "没有重复记录" else response.write rs("titel") end if)"

-----到response.write "没有重复记录" 中的W下面

danielinbiti 麻烦你给看看啊