步骤一：用Access软件建立一个名为Member的数据库，在其中建五个表，分别为：会员信息数据表member、会员身份表MemberIdentity、会员权限表MemberLevel、会员类别表MemberSort和会员婚姻状况表Wedlock。
●会员信息数据表member：
MemberID：自动编号，主键（ID号）
MemberSort：数字（会员类别）
MemberName：文本，会员姓名
Password：文本（会员密码）
MemberLevel：数字（会员权限）
MemberIdentity：数字（会员身份）
Wedlock：数字（婚姻状况）
MemberQQ：文本（QQ号码）
MemberEmail：文本（会员邮箱）
MemberDate：日期/时间（会员注册日期）
●会员身份表MemberIdentity：
MemberIdentity：自动编号，主键（ID号）
IdentityName：文本（会员身份名称）
●会员权限表MemberLevel：
MemberLevel：自动编号，主键（ID号）
LevelName：文本（会员权限名称）
●会员类别表MemberSort：
MemberSort：自动编号，主键（ID号）
SortName：文本（会员类别名称）
●会员婚姻状况表Wedlock
Wedlock：自动编号，主键（ID号）
WedlockName：文本（会员婚姻状况类别）
说明：五个表建好后，您可以自行设置您想要的类别，如会员权限，您可以设置两个类别－－“未付费会员”和“已付费会员”，编号分别为“1”、“2”，如您设置了三个选项，那么第三个选项的编号当然就是“3”了。
下面我们所要作的工作就是把“1”、“2”之类的编号显示为“未付费会员”和“已付费会员”，否则，大家谁会知道“1”代表的是“未付费会员”，“2”代表的是“已付费会员”？
步骤二：建DSN数据源，建记录集
●运行Dreamweaver MX软件，在会员注册信息显示页面建一个名为ConnMember（您也可以起其它的名称）的DSN数据源。
●点击服务器行为面板中的“绑定”，建一个名为MemberShow的数据集，“连接”选择ConnMember，“表格”选择Member，“列”全选，“排序”选择MemberDate，降序。点击“高级”按钮，修改SQL框中自动生成的代码：
原代码为：
SELECT *
FROM Member
ORDER BY MemberDate DESC
将代码修改为：
SELECT *
FROM (((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock
ORDER BY MemberDate DESC
修改完代码后，点击“确定”，大功告成！
现在，您可以打开记录集看一下，五个表中的字段全部集成在MemberShow记录集中，您只要将相应的字段绑定在该字段想显示的单元格中即可。这下好了，所有的数字编号全部变成了相应的名称，如会员权限，不再是“1”和“2”的数字形式了，而是变成了相应的名称“未付费会员”和“已付费会员”。其它的数字编号也变成了显示的文本名称，是不是很开心呢？
注意事项：
●在输入字母过程中，一定要用英文半角标点符号，单词之间留一半角空格；
●在建立数据表时，如果一个表与多个表联接，那么这一个表中的字段必须是“数字”数据类型，而多个表中的相同字段必须是主键，而且是“自动编号”数据类型。否则，很难联接成功。
●代码嵌套快速方法：如，想连接五个表，则只要在连接四个表的代码上加一个前后括号（前括号加在FROM的后面，后括号加在代码的末尾即可），然后在后括号后面继续添加“INNER JOIN 表名X ON 表1.字段号=表X.字段号”代码即可，这样就可以无限联接数据表了：）
语法格式：
其实 INNER JOIN ……ON的语法格式可以概括为：
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X ON Member.字段号=表X.字段号
您只要套用该格式就可以了。
现成格式范例：
虽然我说得已经比较明白了，但为照顾初学者，我还是以本会员注册系统为例，提供一些现成的语法格式范例，大家只要修改其中的数据表名称和字段名称即可。
连接两个数据表的用法：
FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort
语法格式可以概括为：
FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
连接三个数据表的用法：
FROM (Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel
语法格式可以概括为：
FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号
连接四个数据表的用法：
FROM ((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity
语法格式可以概括为：
FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号
连接五个数据表的用法：
FROM (((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock
语法格式可以概括为：
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号
连接六个数据表的用法：略，与上述联接方法类似，大家举一反三吧：）
[转载]公用表表达式的典型应用
作者: NinGoo(http://ningoo.itpub.net)发表于: 2006.11.18 09:32
分类: Oracle
出处: http://ningoo.itpub.net/post/2149/229942
---------------------------------------------------------------
转载自ASA SQL用户指南
公用表表达式（CTE）：Common Table Expression
使用 SELECT 语句的 WITH 前缀，您可以定义公用表表达式。您可以像使用临时视图一样在查询中使用这些公用表表达式。
===============================================================
NinGoo：本文虽然是基于Sybase的，但是公用表表达式在Oracle中的使用和语法基本相同，但是Oracle中的公用表表达式不支持列别名，也就是如下的语法在Sybase中是合法的，在oracle中会报错：
with test_table(col1,col2)
as (select col1,col2 from test)
select * from test_table;
ORA-32033: 不支持列别名
所以，在oracle中，语法如下：
with test_table
as (select col1,col2 from test)
select * from test;
MS SQL Server2005也支持CTE：http://msdn2.microsoft.com/zh-cn/library/ms190766.aspx
==============================================================
公用表表达式是使用 WITH 子句定义的，该子句在 SELECT 语句中的 SELECT 关键字前面。子句的内容定义了之后可能会在语句中的其它位置引用的一个或多个临时视图。此子句的语法模拟 CREATE VIEW 语句的语法。您可以使用公用表表达式来表达上一个查询，如下所示。
WITH CountEmployees(dept_id, n) AS
( SELECT dept_id, count(*) AS n
FROM employee GROUP BY dept_id )
SELECT dept_id, n
FROM CountEmployees
WHERE n = ( SELECT max(n)
FROM CountEmployees )
改为搜索雇员最少的部门说明这种查询可以返回多个行。
WITH CountEmployees(dept_id, n) AS
( SELECT dept_id, count(*) AS n
FROM employee GROUP BY dept_id )
SELECT dept_id, n
FROM CountEmployees
WHERE n = ( SELECT min(n)
FROM CountEmployees )
在示例数据库中，两个部门共享着最少数量（即 9 名）的雇员。
一般而言，只要表的表达式必须在一个查询中出现多次，公用表表达式就很有用。下面的典型情况适合于公用表表达式。
-
涉及多个集合函数的查询。
-
过程中必须包含对程序变量的引用的视图。
-
使用临时视图存储一组值的查询。
此列表并不完整。您可能会遇到许多其它要使用公用表表达式的情况。
多个集合函数
只要必须在一个查询中显示多个级别的集合，公用表表达式就很有用。这是上一部分使用的示例中的情况。任务是检索雇员数量最多的部门的部门 ID。为此，要使用 count 集合函数来计算每个部门的雇员数量，并使用 max 函数选择最大的部门。
在编写查询来确定哪个部门的工资额最多时，会出现类似的情况。sum 集合函数用于计算每个部门的工资额，而 max 函数用于确定哪个部门最大。查询中同时出现这两个函数表明公用表表达式可能有用。
WITH DeptPayroll( dept_id, amt ) AS
( SELECT dept_id, sum(salary) AS amt
FROM employee GROUP BY dept_id )
SELECT dept_id, amt
FROM DeptPayroll
WHERE amt = ( SELECT max(amt)
FROM DeptPayroll )
引用程序变量的视图
有时，公用表表达式对创建包含对程序变量的引用的视图可能会很方便。例如，您可以定义过程中标识特定客户的变量。您要查询该客户的购买历史记录，并且如果您要多次访问类似信息或者可能会使用多个集合函数，则您需要创建一个包含有关该特定客户的信息的视图。
您无法创建引用程序变量的视图，因为无法将视图范围限制为您的过程的范围。一旦创建了视图，就可以在其它环境中使用它。但是，您可以在您的过程中的查询内使用公用表表达式。因为公用表表达式的范围限制到语句，所以变量引用不会造成任何多义性，因此可以使用变量引用。
下面的语句在示例数据库中选择各位不同销售代表的销售总额。
SELECT emp_fname || ' ' || emp_lname AS sales_rep_name,
sales_rep AS sales_rep_id,
sum( p.unit_price * i.quantity ) AS total_sales
FROM employee LEFT OUTER JOIN sales_order AS o
INNER JOIN sales_order_items AS i
INNER JOIN product AS p
WHERE '2000-01-01' <= order_date
AND order_date < '2001-01-01'
GROUP BY sales_rep, emp_fname, emp_lname
上面的查询是出现在下面过程中的公用表表达式的基础。销售代表的 ID 号和所讨论的年份是即将使用的参数。如此过程所示，可在 WITH 子句中引用过程参数和任何已声明的局部变量。
CREATE PROCEDURE sales_rep_total (
IN rep INTEGER,
IN yyyy INTEGER )
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
SET start_date = YMD( yyyy, 1, 1 );
SET end_date = YMD( yyyy, 12, 31 );
WITH total_sales_by_rep ( sales_rep_name,
sales_rep_id,
month,
order_year,
total_sales ) AS
( SELECT emp_fname || ' ' || emp_lname AS sales_rep_name,
sales_rep AS sales_rep_id, month( order_date),
year(order_date),
sum( p.unit_price * i.quantity ) AS total_sales
FROM employee LEFT OUTER JOIN sales_order o
INNER JOIN sales_order_items i
INNER JOIN product p
WHERE start_date <= order_date AND
order_date <= end_date AND
sales_rep = rep
GROUP BY year(order_date), month(order_date),
emp_fname, emp_lname, sales_rep )
SELECT sales_rep_name,
monthname( YMD(yyyy, month, 1) ) AS month_name,
order_year,
total_sales
FROM total_sales_by_rep
WHERE total_sales =
( SELECT max( total_sales) FROM total_sales_by_rep )
ORDER BY order_year ASC, month ASC;
END;
下面的语句说明了如何调用上面的过程。
CALL sales_rep_total(129, 2000);
存储值的视图
有时，公用表表达式在某个 SELECT 语句或某个过程中存储一组特定的值会很有用。例如，假定一家公司要按三分之一年度而不是按季度分析它的销售人员的结果。由于没有代表三分之一的内置日期部分（虽然有代表季度的内置日期部分），所以有必要将这些日期存储在过程中。
WITH thirds (q_name, q_start, q_end) AS
( SELECT 'T1', '2000-01-01', '2000-04-30' UNION
SELECT 'T2', '2000-05-01', '2000-08-31' UNION
SELECT 'T3', '2000-09-01', '2000-12-31' )
SELECT q_name,
sales_rep,
count(*) AS num_orders,
sum( p.unit_price * i.quantity ) AS total_sales
FROM thirds LEFT OUTER JOIN sales_order AS o
ON q_start <= order_date AND order_date <= q_end
INNER JOIN sales_order_items AS i
INNER JOIN product AS p
GROUP BY q_name, sales_rep
ORDER BY q_name, sales_rep
使用此方法时应该小心，因为值可能需要定期维护。例如，如果要为任何其它年度使用上面的语句，则必须对它进行修改。
您还可以在过程中应用此技术。下面的示例声明了一个过程，该过程将所讨论的年份作为参数。
CREATE PROCEDURE sales_by_third ( IN y INTEGER )
BEGIN
WITH thirds (q_name, q_start, q_end) AS
( SELECT 'T1', YMD( y, 01, 01), YMD( y, 04, 30) UNION
SELECT 'T2', YMD( y, 05, 01), YMD( y, 08, 31) UNION
SELECT 'T3', YMD( y, 09, 01), YMD( y, 12, 31) )
SELECT q_name,
sales_rep,
count(*) AS num_orders,
sum(p.unit_price * i.quantity) AS total_sales
FROM thirds JOIN sales_order AS o
ON q_start <= order_date AND order_date <= q_end
KEY JOIN sales_order_items AS i
KEY JOIN product AS p
GROUP BY q_name, sales_rep
ORDER BY q_name, sales_rep;
END;
CALL sales_by_third (2000);
(需要引用, 请注明出处: http://ningoo.itpub.net)
Oracle 9i表连接
原文地址：http://blog.youkuaiyun.com/xuanxingmin/archive/2006/10/31/1358159.aspx本文Tag：oracle 9i, 表连接, 连接, ... 作者：轩兴民 来源：youkuaiyun.com 发表于29天以前一般的相等连接(内连接)： select a.*,b.* from a, b where a.id = b.id; 或 select * from a, b where a.id = b.id; 以上这两个是等价的。一般的相等连接也可以写成这样： select * from a inner join b on a.id = b.id 外连接： Oracle中可以使用“(+) ”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN LEFT OUTER JOIN：左外关联 SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id); 等价于 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE 点击数量：6 |
以下的SQL语句在服务器需要运行长达30分钟才能完成:
SELECT dbo.ComFlow.ComFlowCode, dbo.ComFlow.ComCode, dbo.ComFlow.CustCode, dbo.ComFlow.DepartCode, dbo.ComFlow.SaleCode,
dbo.ComFlow.EmpCode, dbo.ComFlow.Quantity * dbo.Commodity.ConvertRate AS Quantity, CONVERT(datetime, DATENAME(yyyy,
dbo.ComFlow.FlowDate) + '-' + DATENAME(mm, dbo.ComFlow.FlowDate) + '-' + DATENAME(dd, dbo.ComFlow.FlowDate)) AS FlowDate,
dbo.ComFlow.SalType, dbo.Employee.DepartCode AS DepartIn, dbo.Sale.DepartCode AS DepartOut,
dbo.ComFlow.Quantity * dbo.Commodity.TradePrice * dbo.Commodity.Discount / 100 AS Total, dbo.Department.GrpCode AS GrpCodeIn,
Department1.GrpCode AS GrpCodeOut
FROM dbo.ComFlow INNER JOIN
dbo.Customer ON
dbo.ComFlow.SalType IN (N'促销', N'流向退货', N'多级流向') AND dbo.ComFlow.CustCode = dbo.Customer.CustCode
Or ComFlow_1.SalType IN (N'自然流向', N'自然流向退货') AND ComFlow_1.OutCustCode = Customer_1.CustCode
INNER JOIN
dbo.CustomerRelation ON dbo.ComFlow.ComCode = dbo.CustomerRelation.ComCode AND
dbo.CustomerRelation.CustCode = dbo.Customer.CustCode INNER JOIN
dbo.Employee ON dbo.CustomerRelation.EmpCode = dbo.Employee.EmpCode INNER JOIN
dbo.Sale ON dbo.ComFlow.SaleCode = dbo.Sale.SaleCode INNER JOIN
dbo.Department ON dbo.Department.DepartCode = dbo.Employee.DepartCode INNER JOIN
dbo.Department AS Department1 ON Department1.DepartCode = dbo.Sale.DepartCode AND
dbo.Department.GrpCode <> Department1.GrpCode INNER JOIN
dbo.Commodity ON dbo.ComFlow.ComCode = dbo.Commodity.ComCode
WHERE (NOT (dbo.ComFlow.SalType = N'流向退货')) OR
(NOT (dbo.Customer.Type = N'医药公司'))
虽然说，我们使用这个语句的应用是一个BI应用，实时性要求不高，但是，我觉得没有道理会运行这么久，应该有办法优化。
第一步，我看了看索引，好像没有问题，都有
第二步，检查关系，有没有错，没有错，和应用要求是一致的，尤其计算出来的结果和同事使用另外一种方法的计算结果是一致的（同事使用多个视图分步累加）。
第三步，看看这个语句有没有什么特别之处？
我注意到特别之处就是使用Pink底色标出的部分：
dbo.ComFlow.SalType IN (N'促销', N'流向退货', N'多级流向') AND dbo.ComFlow.CustCode = dbo.Customer.CustCode
Or ComFlow_1.SalType IN (N'自然流向', N'自然流向退货') AND ComFlow_1.OutCustCode = Customer_1.CustCode
这是一个Or关系的关联？就是这个问题？
分析这个语句可以看出，这个Or语句其实是可以分解成Union语句的，所以把它变成下面的：
SELECT dbo.ComFlow.ComFlowCode, dbo.ComFlow.ComCode, dbo.ComFlow.CustCode, dbo.ComFlow.DepartCode, dbo.ComFlow.SaleCode,
dbo.ComFlow.EmpCode, dbo.ComFlow.Quantity * dbo.Commodity.ConvertRate AS Quantity, CONVERT(datetime, DATENAME(yyyy,
dbo.ComFlow.FlowDate) + '-' + DATENAME(mm, dbo.ComFlow.FlowDate) + '-' + DATENAME(dd, dbo.ComFlow.FlowDate)) AS FlowDate,
dbo.ComFlow.SalType, dbo.Employee.DepartCode AS DepartIn, dbo.Sale.DepartCode AS DepartOut,
dbo.ComFlow.Quantity * dbo.Commodity.TradePrice * dbo.Commodity.Discount / 100 AS Total, dbo.Department.GrpCode AS GrpCodeIn,
Department1.GrpCode AS GrpCodeOut
FROM dbo.ComFlow INNER JOIN
dbo.Customer ON dbo.ComFlow.SalType IN (N'促销', N'流向退货', N'多级流向') AND dbo.ComFlow.CustCode = dbo.Customer.CustCode INNER JOIN
dbo.CustomerRelation ON dbo.ComFlow.ComCode = dbo.CustomerRelation.ComCode AND
dbo.CustomerRelation.CustCode = dbo.Customer.CustCode INNER JOIN
dbo.Employee ON dbo.CustomerRelation.EmpCode = dbo.Employee.EmpCode INNER JOIN
dbo.Sale ON dbo.ComFlow.SaleCode = dbo.Sale.SaleCode INNER JOIN
dbo.Department ON dbo.Department.DepartCode = dbo.Employee.DepartCode INNER JOIN
dbo.Department AS Department1 ON Department1.DepartCode = dbo.Sale.DepartCode AND
dbo.Department.GrpCode <> Department1.GrpCode INNER JOIN
dbo.Commodity ON dbo.ComFlow.ComCode = dbo.Commodity.ComCode
WHERE (NOT (dbo.ComFlow.SalType = N'流向退货')) OR
(NOT (dbo.Customer.Type = N'医药公司'))
UNION ALL
SELECT ComFlow_1.ComFlowCode, ComFlow_1.ComCode, ComFlow_1.CustCode, ComFlow_1.DepartCode, ComFlow_1.SaleCode, ComFlow_1.EmpCode,
ComFlow_1.Quantity * Commodity_1.ConvertRate AS Quantity, CONVERT(datetime, DATENAME(yyyy, ComFlow_1.FlowDate) + '-' + DATENAME(mm,
ComFlow_1.FlowDate) + '-' + DATENAME(dd, ComFlow_1.FlowDate)) AS FlowDate, ComFlow_1.SalType, Employee_1.DepartCode AS DepartIn,
Sale_1.DepartCode AS DepartOut, ComFlow_1.Quantity * Commodity_1.TradePrice * Commodity_1.Discount / 100 AS Total,
Department_1.GrpCode AS GrpCodeIn, Department1.GrpCode AS GrpCodeOut
FROM dbo.ComFlow AS ComFlow_1 INNER JOIN
dbo.Customer AS Customer_1 ON ComFlow_1.SalType IN (N'自然流向', N'自然流向退货') AND
ComFlow_1.OutCustCode = Customer_1.CustCode INNER JOIN
dbo.CustomerRelation AS CustomerRelation_1 ON ComFlow_1.ComCode = CustomerRelation_1.ComCode AND
CustomerRelation_1.CustCode = Customer_1.CustCode INNER JOIN
dbo.Employee AS Employee_1 ON CustomerRelation_1.EmpCode = Employee_1.EmpCode INNER JOIN
dbo.Sale AS Sale_1 ON ComFlow_1.SaleCode = Sale_1.SaleCode INNER JOIN
dbo.Department AS Department_1 ON Department_1.DepartCode = Employee_1.DepartCode INNER JOIN
dbo.Department AS Department1 ON Department1.DepartCode = Sale_1.DepartCode AND Department_1.GrpCode <> Department1.GrpCode INNER JOIN
dbo.Commodity AS Commodity_1 ON ComFlow_1.ComCode = Commodity_1.ComCode
WHERE (NOT (ComFlow_1.SalType = N'流向退货')) OR
(NOT (Customer_1.Type = N'医药公司'))
没有想到，效果太明显了，之前需要30分钟才能运行完毕的语句只要30几秒就完成了。
这里可以看出，Or的语句可能破坏了索引的作用。使用Or进行关联虽然逻辑非常清楚，但是效率低。
使用Union虽然冗长，但是用在这里效率要高。
http://www.cnblogs.com/cleo/archive/2006/11/01/547079.html
扫一扫
7536
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
