SELECT yh.[zjh] '证件号', yh.[zjmc] '证件名称', yh.[sfzh] '身份证号', cyrzt.xx '从业人员状态',yh.[clpzh] '车辆牌照号', cpys.[xx] '车牌颜色', yh.[dlyszh] '道路运输证号', jbbm.[xx] '经办部门'
FROM [yh_cyry] yh,[zd_cyrzt] cyrzt,[zd_cpys] cpys,[zd_jbbm] jbbm
where yh.cyrzt = cyrzt.cyrzt
and yh.jbbm = jbbm.jbbm
and yh.cpys = cpys.cpys
and yh.cyrzt = '30'--在职
and yh.dlyszh in
(select dlyszh from yh_cyry
where cyrzt = '30'
group by zjh, sfzh,dlyszh
having count(zjh) > 1 and count(sfzh) > 1 and count(cpys) > 1
)
order by yh.zjh
FROM [yh_cyry] yh,[zd_cyrzt] cyrzt,[zd_cpys] cpys,[zd_jbbm] jbbm
where yh.cyrzt = cyrzt.cyrzt
and yh.jbbm = jbbm.jbbm
and yh.cpys = cpys.cpys
and yh.cyrzt = '30'--在职
and yh.dlyszh in
(select dlyszh from yh_cyry
where cyrzt = '30'
group by zjh, sfzh,dlyszh
having count(zjh) > 1 and count(sfzh) > 1 and count(cpys) > 1
)
order by yh.zjh
解决方案 »
- 系统自带的隐藏分页存储过程在sql2005 无法使用
- 关于SQL Server恢复,肯请帮忙!
- 报表执行时提示临时表已经存在,但是DROP时又没有?
- 菜鸟初学,这2个问题实在想不出了
- 当进行数据库操作时,出现了如下错误信息,是什么原因?错误信息详情请进!
- 【问个简单问题】如何得到...
- 创建借阅表出现报错
- 怎么样不通过SQL SERVER CLIENT MANAGEMENT而改变客户端访问SQL SERVER为通过TCP/IP来访问?
- 如何设置sqlserver访问权限?
- 求救,sql server2000 在windows xp(sp2)上装不上啊!!!
- 求一最优查询语句(一定要效率最高)
- 跪求:按角色分配的权限管理(数据库设计)急!!
语法没错,我是我写SQL的思想可能有错,因为查不出预期的结果。
FROM [yh_cyry] yh,[zd_cyrzt] cyrzt,[zd_cpys] cpys,[zd_jbbm] jbbm
where yh.cyrzt = cyrzt.cyrzt
and yh.jbbm = jbbm.jbbm
and yh.cpys = cpys.cpys
and yh.cyrzt = '30'--在职
and yh.dlyszh in
(select dlyszh from yh_cyry
where cyrzt = '30'
group by zjh, sfzh,dlyszh
having count(*)>1
)
order by yh.zjh
FROM [yh_cyry] yh,[zd_cyrzt] cyrzt,[zd_cpys] cpys,[zd_jbbm] jbbm,
(select zjh, sfzh,cpys from yh_cyry
where cyrzt = '30'
group by zjh, sfzh,cpys
having count(*)>1
) t1
where yh.cyrzt = cyrzt.cyrzt
and yh.jbbm = jbbm.jbbm
and yh.cpys = cpys.cpys
and yh.cyrzt = '30'--在职
and yh.zjh=t1.zjh and yh.sfzh=t1.sfzh and yh.cpys=t1.cpys
order by yh.zjh