SQL筛选取多个字段值语法

Question

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金额相同 显示三个字段值相同的房间号及三个字段数据。

Answer 1

 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条