看关联字段 示例 select mm_sheetno as 调拨单号,or_sheetno as 原始单据号,convert(char(8),occurdate,112) as 发生日期,convert(char(8),firmdate,112)as 确认日期, ms_product as 货号,pr_name as 名称,ms_amt as 数量,mi_dept as 调入门店编码,a.deptname as 调入门店名称,mo_dept as 调出门店编码,b.deptname as 调出门店名称 ,(case mm_state when 6 then '数量错误' when 5 then (case when mo_dept=input_dept then '缺调入单位' when mi_dept=input_dept then '缺调出单位' end)end ) as 出错原因 from td_move_outin_m left join td_move_outin_s on mm_sheetno=ms_sheetno left join td_product on ms_product=productno left join td_dept a on mi_dept=a.deptno left join td_dept b on mo_dept=b.deptno where mm_state in (5,6) order by mm_sheetno
a.* 就是a表所有列 A 是为了方便 对表Userlist起的别名 就是表Userlist left out join 是左外连接 有多种连接方式 inner join ,left out join right out join 看一下help吧
使用外联接 仅当至少有一个同属于两表的行符合联接条件时,内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行,只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行,以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。Microsoft® SQL Server™ 2000 对在 FROM 子句中指定的外联接使用以下 SQL-92 关键字: LEFT OUTER JOIN 或 LEFT JOIN RIGHT OUTER JOIN 或 RIGHT JOIN FULL OUTER JOIN 或 FULL JOIN SQL Server 支持 SQL-92 外联接语法,以及在 WHERE 子句中使用 *= 和 =* 运算符指定外联接的旧式语法。由于 SQL-92 语法不容易产生歧义,而旧式 Transact-SQL 外联接有时会产生歧义,因此建议使用 SQL-92 语法。使用左向外联接 假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。若要在结果中包括所有的作者,而不管出版商是否住在同一个城市,请使用 SQL-92 左向外联接。下面是 Transact-SQL 左向外联接的查询和结果:USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC使用右向外联接 假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。SQL-92 右向外联接运算符 RIGHT OUTER JOIN 指明:不管第一个表中是否有匹配的数据,结果将包含第二个表中的所有行。若要在结果中包括所有的出版商,而不管城市中是否还有出版商居住,请使用 SQL-92 右向外联接。下面是 Transact-SQL 右向外联接的查询和结果:USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors AS a RIGHT OUTER JOIN publishers AS p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC使用完整外部联接 若要通过在联接结果中包括不匹配的行保留不匹配信息,请使用完整外部联接。Microsoft® SQL Server™ 2000 提供完整外部联接运算符 FULL OUTER JOIN,不管另一个表是否有匹配的值,此运算符都包括两个表中的所有行。假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。SQL-92 FULL OUTER JOIN 运算符指明:不管表中是否有匹配的数据,结果将包括两个表中的所有行。若要在结果中包括所有作者和出版商,而不管城市中是否有出版商或者出版商是否住在同一个城市,请使用完整外部联接。下面是 Transact-SQL 完整外部联接的查询和结果:USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
我也给你一个 SELECT kh_optometry.c_checkno, kh_optometry.c_lball, kh_optometry.c_rball, kh_optometry.c_lpole, kh_optometry.c_rpole, kh_optometry.c_laxes, kh_optometry.c_raxes, kh_optometry.c_space, kh_optometry.c_hspace, kh_optometry.c_lcheck, kh_optometry.c_rcheck, kh_optometry.c_lsign, kh_optometry.c_rsign, kh_optometry.c_date, kh_optometry.c_empcode, kh_optometry.c_comment, kh_optometry.c_export_flag, kh_optometry.c_flag, kh_optometry.c_ladd, kh_optometry.c_radd, kh_client.c_clientname, kh_client.c_sex, kh_client.c_clientcard, kh_client.c_home_tel, kh_client.c_mt, kh_client.c_addr, kh_client.c_post, kh_optometry.c_lprism, kh_optometry.c_rprism, kh_optometry.c_lfloor, kh_optometry.c_rfloor, kh_optometry.c_llight, kh_optometry.c_rlight, kh_optometry.c_lfoot, kh_optometry.c_rfoot, kh_optometry.c_shift, kh_optometry.c_center, kh_client.c_office_tel, kh_optometry.c_lspace, kh_optometry.c_rspace, kh_optometry.c_lhspace, kh_optometry.c_rhspace, kh_client.c_clientcode, kh_optometry.c_clientcode, kh_optometry.c_lcurvature, kh_optometry.c_rcurvature, kh_optometry.c_advice FROM kh_optometry, kh_client WHERE ( kh_optometry.c_clientcode =* kh_client.c_clientcode) and (kh_client.c_clientcode = :cc_clientcode) AND (kh_optometry.c_checkno = (SELECT max(c_checkno) FROM kh_optometry where c_clientcode=:cc_clientcode)) ORDER BY kh_optometry.c_checkno ASC
Select k1.*,k2.Numb2,k3.numb3,k4.numb4,k5.numb5,k6.numb6 from (SELECT COUNT(*) AS Numb1,s1. fact, s1.punit FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate()) AS p_mon, fact, punit FROM dbo.p_man_input GROUP BY fact, punit, I_date) S1 WHERE (p_mon < 6) and (p_mon >=0) GROUP BY fact, punit) k1, (SELECT COUNT(*) AS Numb2, s2.fact, s2.punit FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate()) AS p_mon, fact, punit FROM dbo.p_man_input GROUP BY fact, punit, I_date) S2 WHERE (p_mon >=6) and (p_mon< 24) GROUP BY fact, punit) k2, (SELECT COUNT(*) AS Numb3, fact, punit FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate()) AS p_mon, fact, punit FROM dbo.p_man_input GROUP BY fact, punit, I_date) s3 WHERE (p_mon >=24) and (p_mon< 36) GROUP BY fact, punit) k3, (SELECT COUNT(*) AS Numb4,s4.fact, s4.punit FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate()) AS p_mon, fact, punit FROM dbo.p_man_input GROUP BY fact, punit, I_date) s4 WHERE (p_mon >=36) and (p_mon<48) GROUP BY fact, punit) k4, (SELECT COUNT(*) AS Numb5, fact, punit FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate()) AS p_mon, fact, punit FROM dbo.p_man_input GROUP BY fact, punit, I_date) s5 WHERE (p_mon >=48) and (p_mon<60) GROUP BY fact, punit) k5, (SELECT COUNT(*) AS Numb6, fact, punit FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate()) AS p_mon, fact, punit FROM dbo.p_man_input GROUP BY fact, punit, I_date) s6 WHERE (p_mon >=60) GROUP BY fact, punit) k6 where k1.fact=k2.fact and k1.punit=k2.punit and k1.fact=k3.fact and k1.punit=k3.punit and k2.punit=k3.punit and k2.fact=k3.fact and k3.punit=k4.punit and k4.fact=k3.fact and k4.punit=k5.punit and k4.fact=k5.fact and k6.punit=k5.punit and k5.fact=k6.fact and k1.punit=k6.punit and k1.fact=k6.fact
我给你举一个简单的例子:有两个表:学生信息表和系别信息表。 学生信息表: 系别信息表 学号 姓名 系编号 性别 系编号 系名 总人数 001 张一 A 男 A 计算机系 2000 002 李本 B 男 B 英语系 2555 003 韦三 B 女 C 工业系 2000 现在让你查询张一是哪个系的学生,你只从“学生信息表”中查不到,因为系名是放在“系别信息表”中的,这里你就必须把两个表进行关联后查询。 select 姓名,系名 /*表显示的两个字段*/ from 学生信息表,系别信息表/ *表示数据来源*/ where 学生信息表 .系编号 =系别信息表。系编号 and 姓名=‘张一’ /*关联条件*/ 程序执行时,指针在“学生信息表”中找到姓名为张一的那条记录,根据关联条件“学生信息表 .系编号 =系别信息表”,找到对应的系别信息表中的系名,就可以输出来了。像因为张一的系编是A,在“学生信息表”中可以知道,那么在“系别信息表” 中系编号为A的就对应上“计算机”系了,从而能正确地读出数据。 多表之间要进行关联,必须有相同的至少一个字段作为中间桥梁。
示例
select mm_sheetno as 调拨单号,or_sheetno as 原始单据号,convert(char(8),occurdate,112) as 发生日期,convert(char(8),firmdate,112)as 确认日期, ms_product as 货号,pr_name as 名称,ms_amt as 数量,mi_dept as 调入门店编码,a.deptname as 调入门店名称,mo_dept as 调出门店编码,b.deptname as 调出门店名称 ,(case mm_state when 6 then '数量错误' when 5 then (case when mo_dept=input_dept then '缺调入单位' when mi_dept=input_dept then '缺调出单位' end)end ) as 出错原因
from td_move_outin_m left join td_move_outin_s on mm_sheetno=ms_sheetno left join td_product on ms_product=productno
left join td_dept a on mi_dept=a.deptno left join td_dept b on mo_dept=b.deptno
where mm_state in (5,6)
order by mm_sheetno
left out join 是左外连接
有多种连接方式 inner join ,left out join right out join
看一下help吧
仅当至少有一个同属于两表的行符合联接条件时,内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行,只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行,以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。Microsoft® SQL Server™ 2000 对在 FROM 子句中指定的外联接使用以下 SQL-92 关键字: LEFT OUTER JOIN 或 LEFT JOIN
RIGHT OUTER JOIN 或 RIGHT JOIN
FULL OUTER JOIN 或 FULL JOIN
SQL Server 支持 SQL-92 外联接语法,以及在 WHERE 子句中使用 *= 和 =* 运算符指定外联接的旧式语法。由于 SQL-92 语法不容易产生歧义,而旧式 Transact-SQL 外联接有时会产生歧义,因此建议使用 SQL-92 语法。使用左向外联接
假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。若要在结果中包括所有的作者,而不管出版商是否住在同一个城市,请使用 SQL-92 左向外联接。下面是 Transact-SQL 左向外联接的查询和结果:USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC使用右向外联接
假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。SQL-92 右向外联接运算符 RIGHT OUTER JOIN 指明:不管第一个表中是否有匹配的数据,结果将包含第二个表中的所有行。若要在结果中包括所有的出版商,而不管城市中是否还有出版商居住,请使用 SQL-92 右向外联接。下面是 Transact-SQL 右向外联接的查询和结果:USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC使用完整外部联接
若要通过在联接结果中包括不匹配的行保留不匹配信息,请使用完整外部联接。Microsoft® SQL Server™ 2000 提供完整外部联接运算符 FULL OUTER JOIN,不管另一个表是否有匹配的值,此运算符都包括两个表中的所有行。假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。SQL-92 FULL OUTER JOIN 运算符指明:不管表中是否有匹配的数据,结果将包括两个表中的所有行。若要在结果中包括所有作者和出版商,而不管城市中是否有出版商或者出版商是否住在同一个城市,请使用完整外部联接。下面是 Transact-SQL 完整外部联接的查询和结果:USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
SELECT kh_optometry.c_checkno,
kh_optometry.c_lball,
kh_optometry.c_rball,
kh_optometry.c_lpole,
kh_optometry.c_rpole,
kh_optometry.c_laxes,
kh_optometry.c_raxes,
kh_optometry.c_space,
kh_optometry.c_hspace,
kh_optometry.c_lcheck,
kh_optometry.c_rcheck,
kh_optometry.c_lsign,
kh_optometry.c_rsign,
kh_optometry.c_date,
kh_optometry.c_empcode,
kh_optometry.c_comment,
kh_optometry.c_export_flag,
kh_optometry.c_flag,
kh_optometry.c_ladd,
kh_optometry.c_radd,
kh_client.c_clientname,
kh_client.c_sex,
kh_client.c_clientcard,
kh_client.c_home_tel,
kh_client.c_mt,
kh_client.c_addr,
kh_client.c_post,
kh_optometry.c_lprism,
kh_optometry.c_rprism,
kh_optometry.c_lfloor,
kh_optometry.c_rfloor,
kh_optometry.c_llight,
kh_optometry.c_rlight,
kh_optometry.c_lfoot,
kh_optometry.c_rfoot,
kh_optometry.c_shift,
kh_optometry.c_center,
kh_client.c_office_tel,
kh_optometry.c_lspace,
kh_optometry.c_rspace,
kh_optometry.c_lhspace,
kh_optometry.c_rhspace,
kh_client.c_clientcode,
kh_optometry.c_clientcode,
kh_optometry.c_lcurvature,
kh_optometry.c_rcurvature,
kh_optometry.c_advice
FROM kh_optometry,
kh_client
WHERE ( kh_optometry.c_clientcode =* kh_client.c_clientcode) and
(kh_client.c_clientcode = :cc_clientcode) AND
(kh_optometry.c_checkno = (SELECT max(c_checkno) FROM kh_optometry where c_clientcode=:cc_clientcode))
ORDER BY kh_optometry.c_checkno ASC
from
(SELECT COUNT(*) AS Numb1,s1. fact, s1.punit
FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate())
AS p_mon, fact, punit
FROM dbo.p_man_input
GROUP BY fact, punit, I_date) S1
WHERE (p_mon < 6) and (p_mon >=0)
GROUP BY fact, punit) k1, (SELECT COUNT(*) AS Numb2, s2.fact, s2.punit
FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate())
AS p_mon, fact, punit
FROM dbo.p_man_input
GROUP BY fact, punit, I_date) S2
WHERE (p_mon >=6) and (p_mon< 24)
GROUP BY fact, punit) k2,
(SELECT COUNT(*) AS Numb3, fact, punit
FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate())
AS p_mon, fact, punit
FROM dbo.p_man_input
GROUP BY fact, punit, I_date) s3
WHERE (p_mon >=24) and (p_mon< 36)
GROUP BY fact, punit) k3,
(SELECT COUNT(*) AS Numb4,s4.fact, s4.punit
FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate())
AS p_mon, fact, punit
FROM dbo.p_man_input
GROUP BY fact, punit, I_date) s4
WHERE (p_mon >=36) and (p_mon<48)
GROUP BY fact, punit) k4,
(SELECT COUNT(*) AS Numb5, fact, punit
FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate())
AS p_mon, fact, punit
FROM dbo.p_man_input
GROUP BY fact, punit, I_date) s5
WHERE (p_mon >=48) and (p_mon<60)
GROUP BY fact, punit) k5,
(SELECT COUNT(*) AS Numb6, fact, punit
FROM (SELECT COUNT(*) AS Numb, datediff(month,I_date,getdate())
AS p_mon, fact, punit
FROM dbo.p_man_input
GROUP BY fact, punit, I_date) s6
WHERE (p_mon >=60)
GROUP BY fact, punit) k6
where k1.fact=k2.fact and k1.punit=k2.punit and k1.fact=k3.fact and k1.punit=k3.punit and k2.punit=k3.punit and k2.fact=k3.fact and k3.punit=k4.punit and k4.fact=k3.fact
and k4.punit=k5.punit and k4.fact=k5.fact and k6.punit=k5.punit and k5.fact=k6.fact and k1.punit=k6.punit and k1.fact=k6.fact
学生信息表: 系别信息表
学号 姓名 系编号 性别 系编号 系名 总人数
001 张一 A 男 A 计算机系 2000
002 李本 B 男 B 英语系 2555
003 韦三 B 女 C 工业系 2000
现在让你查询张一是哪个系的学生,你只从“学生信息表”中查不到,因为系名是放在“系别信息表”中的,这里你就必须把两个表进行关联后查询。
select 姓名,系名 /*表显示的两个字段*/
from 学生信息表,系别信息表/ *表示数据来源*/
where 学生信息表 .系编号 =系别信息表。系编号 and 姓名=‘张一’
/*关联条件*/
程序执行时,指针在“学生信息表”中找到姓名为张一的那条记录,根据关联条件“学生信息表 .系编号 =系别信息表”,找到对应的系别信息表中的系名,就可以输出来了。像因为张一的系编是A,在“学生信息表”中可以知道,那么在“系别信息表” 中系编号为A的就对应上“计算机”系了,从而能正确地读出数据。
多表之间要进行关联,必须有相同的至少一个字段作为中间桥梁。