自己搞了个业务管理系统,在数据查询上遇到了问题:表a: 单据表
JobID SheetType SheetNo
1 1 00776
2 2 445560
3 1 10980
4 1 90870
表b 费用表
JobID ChargeNote Check
1 00776 1
3 10980 5
表c 业务表
JobID JobNo 1 2010-001
2 2010-002
3 2010-003
4 2010-004得到满足条件: b。Check<5,a。SheetType=1,的所有结果(无论在表b中是否存在对应的数据,如有,必须满足在表b的ChargeNote里存在表a SheetNo的数据,b.ChargeNote LIKE a.SheetNo),即JobID SheetType SheetNo Check JobNo 1 1 00776 1 2010-001
4 1 90870 (null) 2010-004
JobID SheetType SheetNo
1 1 00776
2 2 445560
3 1 10980
4 1 90870
表b 费用表
JobID ChargeNote Check
1 00776 1
3 10980 5
表c 业务表
JobID JobNo 1 2010-001
2 2010-002
3 2010-003
4 2010-004得到满足条件: b。Check<5,a。SheetType=1,的所有结果(无论在表b中是否存在对应的数据,如有,必须满足在表b的ChargeNote里存在表a SheetNo的数据,b.ChargeNote LIKE a.SheetNo),即JobID SheetType SheetNo Check JobNo 1 1 00776 1 2010-001
4 1 90870 (null) 2010-004
LEFT JOIN C ON A.JOBID=C.JOBIDWHERE B.[CHECK]<5 AND A.SHEETTYPE=1
FROM DANJU a
LEFT JOIN FEIYONG b ON a.JobID = b.JobID
LEFT JOIN YEWU c ON c.JobID = a.JobID
WHERE b.[Check] < 5 OR b.[Check] is null AND a.SheetType = 1
1 1 776 1 2010-001
按照zhengtw的:JOBID SHEETTYPE SHEETNO CHECK JOBNO
1 1 776 1 2010-001
3 1 10980 NULL 2010-003
4 1 90870 NULL 2010-004 奇怪,2010-003 的check怎么会是 null ?
LEFT JOIN C ON A.JOBID=C.JOBIDWHERE B.[CHECK]<5 AND A.SHEETTYPE=1不好意思,是说这个应该是没问题的
表a: 单据表
JobID SheetType SheetNo
1 1 00776
2 2 445560
3 1 10980
4 1 90870
表b 费用表
JobID ChargeNote Check
1 00776 1
3 10980 5
表c 业务表
JobID JobNo 1 2010-001
2 2010-002
3 2010-003
4 2010-004if object_id('ta') is not null drop table ta
create table ta
(
jobid int,
sheettype int,
sheetno varchar(20)
)insert into ta
select 1, 1, '00776' union all
select 2, 2, '445560' union all
select 3, 1, '10980' union all
select 4, 1, '90870'if object_id('tb') is not null drop table tb
create table tb
(
JobID int,
ChargeNote varchar(20),
[Check] int
)insert into tb
select 1, '00776', 1 union all
select 3, '10980', 5 union all
select 4, '90800', 4
if object_id('tc') is not null drop table tc
create table tc
(
JobID int,
JobNo varchar(20)
)insert into tc
select 1, '2010-001' union all
select 2, '2010-002' union all
select 3, '2010-003' union all
select 4, '2010-004'select t.jobid, t.Sheettype, t.sheetNo, t.[check], t.jobno from (
select a.jobid, a.Sheettype, a.sheetNo,b.chargenote, b.[check], c.jobno from ta a
left join tb b on a.JobID=b.JobID
left join tc c on a.JobID=c.JobID
where b.[Check]<5 or b.[check] is null and a.SheetType=1) t where ChargeNote=sheetno
我在b表中加了一行 select 4, '90800', 4
select t.jobid, t.Sheettype, t.sheetNo, t.[check], t.jobno from (
select a.jobid, a.Sheettype, a.sheetNo,b.chargenote, b.[check], c.jobno from ta a
left join tb b on a.JobID=b.JobID
left join tc c on a.JobID=c.JobID
where b.[Check]<5 or b.[check] is null and a.SheetType=1) t where ChargeNote=sheetno or chargenote is null