select distinct a.diqu,a.kahao,a.CheckInNo,a.piaolei,a.ZhengJian,a.CheckInTime,a.RenShu,b.mingcheng,c.mingma,d.mingcheng as mch,e.mingcheng as pl,f.DFMingCheng as zj,g.Expr1 as cf from(select k.CheckInTime,k.RenShu,k.diqu,k.kahao,k.checkinno,k.piaolei,k.ZhengJian from Tbl_checkIn k,(select max(id) as id,zhengjian from Tbl_checkIn group by zhengjian) b where k.id=b.id) a,tbl_diqu b,tbl_ShouPiao c,tbl_YanPiaoDian d,tbl_PiaoLei e,Tbl_SFZ f,zgUser.tbl_S g where a.diqu=b.bianhao and a.KaHao=c.kahao and a.CheckInNo=d.bianhao and left(a.zhengjian,6)=f.SFZBianHao and a.piaolei=e.bianhao and a.ZhengJian=g.ZhengJian and mingma>='18001' and mingma<='18205' order by a.ZhengJian
首先说下这条语句是朋友帮我写的,我的学艺不精,麻烦各位老师帮忙看下这个语句,之前没加g.Expr1、zgUser.tbl_S g,a.ZhengJian=g.ZhengJian这三句是能查出数据的,但是加完后就不显示结果了,也没有报错查询结果为空,g.Expr1表项是证件的使用次数,a.ZhengJian=g.ZhengJian这个筛选条件写法有错误吗?请各位老师帮忙给看下,谢谢啦!
首先说下这条语句是朋友帮我写的,我的学艺不精,麻烦各位老师帮忙看下这个语句,之前没加g.Expr1、zgUser.tbl_S g,a.ZhengJian=g.ZhengJian这三句是能查出数据的,但是加完后就不显示结果了,也没有报错查询结果为空,g.Expr1表项是证件的使用次数,a.ZhengJian=g.ZhengJian这个筛选条件写法有错误吗?请各位老师帮忙给看下,谢谢啦!
a.diqu ,
a.kahao ,
a.CheckInNo ,
a.piaolei ,
a.ZhengJian ,
a.CheckInTime ,
a.RenShu ,
b.mingcheng ,
c.mingma ,
d.mingcheng AS mch ,
e.mingcheng AS pl ,
f.DFMingCheng AS zj ,
g.Expr1 AS cf
FROM ( SELECT k.CheckInTime ,
k.RenShu ,
k.diqu ,
k.kahao ,
k.checkinno ,
k.piaolei ,
k.ZhengJian
FROM Tbl_checkIn k ,
( SELECT MAX(id) AS id ,
zhengjian
FROM Tbl_checkIn
GROUP BY zhengjian
) b
WHERE k.id = b.id
) a ,
tbl_diqu b ,
tbl_ShouPiao c ,
tbl_YanPiaoDian d ,
tbl_PiaoLei e ,
Tbl_SFZ f ,
zgUser.tbl_S g
WHERE a.diqu = b.bianhao
AND a.KaHao = c.kahao
AND a.CheckInNo = d.bianhao
AND LEFT(a.zhengjian, 6) = f.SFZBianHao
AND a.piaolei = e.bianhao
AND a.ZhengJian = g.ZhengJian --默认是inner join 的方式,说明在a,g之间,没有对应的zhengjian列的值相同
AND mingma >= '18001'
AND mingma <= '18205'
ORDER BY a.ZhengJian
/*
g.Expr1、zgUser.tbl_S g,a.ZhengJian=g.ZhengJian*/
但是a表和g表之间zhengjian列的值确实有相同的,g表里记录的是证件和使用次数啊