要选取20110101日14:01:01到20110428日10:01:01的数据语句应该怎么写?其中表中的时间14:01:01的格式为140101,10:01:01的格式为100101,我写的语句只能查出来每天1010101到140101之间的数据,其他时间的都查不出来,这个语句应该怎么写啊?
SELECT FSV.dbo.htrans.transcode, unitinfo.unitname, htrans.TRACENO, htrans.TRANSDATE,
htrans.TRANSTIME, htrans.CARDNO, htrans.DATA1, htrans.DATA4, htrans.amount, terminfo.merchname,
terminfo.merchno, htrans.DATA2 FROM FSV.dbo.htrans INNER JOIN FSV.dbo.terminfo ON htrans.TERMNO = terminfo.termno
INNER JOIN FSV.dbo.unitinfo INNER JOIN FSV.dbo.merchinfo ON unitinfo.unitno = merchinfo.unitno ON terminfo.custno = merchinfo.MERCHNO
WHERE (htrans.transcode = '2') AND (unitinfo.unitname ='杭州测试')
AND ((htrans.TRANSDATE between '20110101' AND '20110428') and (htrans.TRANSTIME between '100101' and '140101'))
SELECT FSV.dbo.htrans.transcode, unitinfo.unitname, htrans.TRACENO, htrans.TRANSDATE,
htrans.TRANSTIME, htrans.CARDNO, htrans.DATA1, htrans.DATA4, htrans.amount, terminfo.merchname,
terminfo.merchno, htrans.DATA2 FROM FSV.dbo.htrans INNER JOIN FSV.dbo.terminfo ON htrans.TERMNO = terminfo.termno
INNER JOIN FSV.dbo.unitinfo INNER JOIN FSV.dbo.merchinfo ON unitinfo.unitno = merchinfo.unitno ON terminfo.custno = merchinfo.MERCHNO
WHERE (htrans.transcode = '2') AND (unitinfo.unitname ='杭州测试')
AND ((htrans.TRANSDATE between '20110101' AND '20110428') and (htrans.TRANSTIME between '100101' and '140101'))
htrans.TRANSTIME, htrans.CARDNO, htrans.DATA1, htrans.DATA4, htrans.amount, terminfo.merchname,
terminfo.merchno, htrans.DATA2 FROM FSV.dbo.htrans INNER JOIN FSV.dbo.terminfo ON htrans.TERMNO = terminfo.termno
INNER JOIN FSV.dbo.unitinfo INNER JOIN FSV.dbo.merchinfo ON unitinfo.unitno = merchinfo.unitno ON terminfo.custno = merchinfo.MERCHNO
WHERE (htrans.transcode = '2') AND (unitinfo.unitname ='杭州测试')
AND ((htrans.TRANSDATE between '20110101' AND '20110428') and (htrans.TRANSTIME between '100101' and '140101'))
看似语句没什么问题了,试试用Left或者right连接了.
terminfo.custno = merchinfo.MERCHNO terminfo中的字段custno数据跟merchinfo中的字段MERCHNO的数据时一样的吗?
看似语句没什么问题了,试试用Left或者right连接了.
[/Quote]
这个条件都是对的,不用考虑了,要考虑的就是时间问题,在实际情况中transdate和transtime都是用下拉列表来选择的,实际的语句应该这样写:
SELECT FSV.dbo.htrans.transcode, unitinfo.unitname, htrans.TRACENO, htrans.TRANSDATE,
htrans.TRANSTIME, htrans.CARDNO, htrans.DATA1, htrans.DATA4, htrans.amount, terminfo.merchname,
terminfo.merchno, htrans.DATA2 FROM FSV.dbo.htrans INNER JOIN FSV.dbo.terminfo ON htrans.TERMNO = terminfo.termno
INNER JOIN FSV.dbo.unitinfo INNER JOIN FSV.dbo.merchinfo ON unitinfo.unitno = merchinfo.unitno ON terminfo.custno = merchinfo.MERCHNO
WHERE (htrans.transcode = '2') AND (unitinfo.unitname ='杭州测试')
AND ((htrans.TRANSDATE >=@transdate1) AND (htrans.TRANSDATE <=@transdate2)) and ((htrans.TRANSTIME >=@transtime1) and (htrans.TRANSTIME <=@transtime2))
这样连接的话怎么连接?
[/Quote]
你这个SQL语句应该是写到一个方法里了吧!
SqlParameter[] parameters = { new SqlParameter("@transdate1",SqlDbType.字段类型),
new SqlParameter("@transdate2",SqlDbType.字段类型)};
parameters[0].Value = 赋值;
parameters[1].Value = 赋值;
......