select * from sj_jhjc a
where not exists
(select * from sjjc where jhrq = a.jcrq
AND jhbm = a.bjbm AND jhrm = a.jcrm AND jhxm = a.bjxm)
where not exists
(select * from sjjc where jhrq = a.jcrq
AND jhbm = a.bjbm AND jhrm = a.jcrm AND jhxm = a.bjxm)
FROM sj_jhjc a
WHERE NOT EXIST (SELECT 1 from sjjc b where a.jhrq = b.jcrq AND a.jhbm = b.bjbm AND a.jhrm = b.jcrm AND a.jhxm = b.bjxm)
where not exists
(select * from sj_jhjc where jhrq = b.jcrq AND jhbm = b.bjbm AND jhrm = b.jcrm AND jhxm = b.bjxm)select * from sj_jhjc a
where not exists
(select * from sjjc where a.jhrq = jcrq AND a.jhbm = bjbm AND a.jhrm = jcrm AND a.jhxm = bjxm)
where jhrq+'-'+jhbm+'-'+jhrm+'-'+jhxm
not int(select a.jhrq+'-'+a.jhbm+'-'+a.jhrm+'-'+a.jhxm FROM sj_jhjc a JOIN
sjjc b ON a.jhrq = b.jcrq AND a.jhbm = b.bjbm AND a.jhrm = b.jcrm AND a.jhxm = b.bjxm)
如果你的字段jhrq,jhbm,jhrm,jhxm不是字符型,用cast(字段 as varchar)将它转换成字符型
SELECT COUNT(*) AS Expr1
FROM sj_jhjc a INNER JOIN
sjjc b ON a.jhrq = b.jcrq AND a.jhbm = b.bjbm AND a.jhrm = b.jcrm AND
a.jhxm = b.bjxm
WHERE (CONVERT(char(10), a.jhrq, 120) < CONVERT(char(10), GETDATE(), 120))余下不符合条件的记录数:49SELECT COUNT(*) AS Expr1
FROM sj_jhjc a
WHERE (CONVERT(char(10), jhrq, 120) < CONVERT(char(10), GETDATE(), 120)) AND
(NOT EXISTS
(SELECT *
FROM sjjc
WHERE a.jhrq = jcrq AND a.jhbm = bjbm AND a.jhrm = jcrm AND a.jhxm = bjxm))一共记录数:129SELECT COUNT(*) AS Expr1
FROM sj_jhjc
WHERE (CONVERT(char(10), jhrq, 120) < CONVERT(char(10), GETDATE(), 120)) 怎么多出10条记录呀。
sjjc b ON (a.jhrq = b.jcrq AND a.jhbm = b.bjbm AND a.jhrm = b.jcrm AND a.jhxm = b.bjxm)
where b.jcrq is null
sjjc b where not (a.jhrq = b.jcrq AND a.jhbm = b.bjbm AND a.jhrm = b.jcrm AND a.jhxm = b.bjxm)
SELECT COUNT(*) AS Expr1
FROM sj_jhjc
WHERE (CONVERT(char(10), jhrq, 120) < CONVERT(char(10), GETDATE(), 120)) AND
(键字段列表达式 in
(SELECT 键字段列表达式 FROM sj_jhjc a
JOIN sjjc b
ON (a.jhrq = b.jcrq AND a.jhbm = b.bjbm
AND a.jhrm = b.jcrm AND a.jhxm = b.bjxm)
WHERE CONVERT(char(10),jhrq,120)<CONVERT(char(10),GETDATE(),120)
)
)
求不符合条件的记录数:
SELECT COUNT(*) AS Expr1
FROM sj_jhjc
WHERE (CONVERT(char(10), jhrq, 120) < CONVERT(char(10), GETDATE(), 120)) AND
(NOT 键字段列表达式 in
(SELECT 键字段列表达式 FROM sj_jhjc a
JOIN sjjc b
ON (a.jhrq = b.jcrq AND a.jhbm = b.bjbm
AND a.jhrm = b.jcrm AND a.jhxm = b.bjxm)
WHERE CONVERT(char(10),a.jhrq,120)<CONVERT(char(10),GETDATE(),120)
)
)值得注意的是:这里的子集中也要加WHERE子句,否则可能出现统计数据错误。