数据库中只有一个表,表名 Productsstring sql = @"SELECT p1.[ProductID] as ProductID, p1.[ProductName] as ProductName, p3.childNodesCount as ChildNodesCount, p1.[ParentID] as ParentID
FROM [Products] p1
INNER JOIN
(
SELECT COUNT(*) AS childNodesCount , p2.[ParentID] AS pId
FROM [Products] p2
GROUP BY p2.[ParentID]
) p3
ON p1.[ProductID] = p3.pId;"//这句是什么意思? DataTable dt = OleDbHelper.ExecuteDataset( base.NorthWindConnectionString, CommandType.Text, sql ).Tables[0];
以上sql语句是什么意思,"p1.","p3." p2,p3各是什么意思?
谁能解释下?
FROM [Products] p1
INNER JOIN
(
SELECT COUNT(*) AS childNodesCount , p2.[ParentID] AS pId
FROM [Products] p2
GROUP BY p2.[ParentID]
) p3
ON p1.[ProductID] = p3.pId;"//这句是什么意思? DataTable dt = OleDbHelper.ExecuteDataset( base.NorthWindConnectionString, CommandType.Text, sql ).Tables[0];
以上sql语句是什么意思,"p1.","p3." p2,p3各是什么意思?
谁能解释下?
FROM [Products] p2
GROUP BY p2.[ParentID])
表中有 ProductName ,ProductID ,ParentID列, 但没有"p3.childNodesCount "里的 childNodesCount列"p3.childNodesCount "是什么意思?
FROM [Products] as p1
中的P1代表 Products表的别名
FROM [Products] p1
INNER JOIN
(SELECT COUNT(*) AS childNodesCount , p2.[ParentID] AS pId
FROM [Products] p2
GROUP BY p2.[ParentID]
) p3
ON p1.[ProductID] = p3.pId;"p1,p2是Products的别名,p3是括号内SQL语句查出来的那个count(*)的别名
ON p1.[ProductID] = p3.pId;"//这句是什么意思?连接查询条件
p3为
SELECT COUNT(*) AS childNodesCount , p2.[ParentID] AS pId
FROM [Products] p2
GROUP BY p2.[ParentID]
以ParentID为父节点的子节点的个数 以及他们的信息
p1.[ProductID] = p3.pId;" 表连接的条件,查询出来的数据是两张表的公共部分
FROM [Products] p2
GROUP BY p2.[ParentID])p3
这条语句会返回一个字段为childNodesCount ,pId
的新表(虚表)
p3相当于这个虚表的表名称
这时就可以看成
SELECT p1.[ProductID] as ProductID, p1.[ProductName] as ProductName, p3.childNodesCount as ChildNodesCount, p1.[ParentID] as ParentID
FROM [Products] p1
INNER JOIN p3 ON p1.[ProductID] = p3.pId
这样就是一个普通的表连接了
FROM [Products] p1
INNER JOIN
(
SELECT COUNT(*) AS childNodesCount , p2.[ParentID] AS pId
FROM [Products] p2
GROUP BY p2.[ParentID]
) p3
ON p1.[ProductID] = p3.pId
找到p1.[ProductID]字段作为ProductID,p1.[ProductName]作为ProductName, p3.childNodesCount作为ChildNodesCount, p1.[ParentID]作为ParentID,
从表[Products]别名为p1
连接(
找到条数和作为childNodesCount,p2.[ParentID]作为pId
从表[Products]别名为p2中找
条件是按照p2的[ParentID]分组查询
)别名为p3连接条件为p1.[ProductID] = p3.pId其中p1为[Products],p2为[Products],
p3为
(
SELECT COUNT(*) AS childNodesCount , p2.[ParentID] AS pId
FROM [Products] p2
GROUP BY p2.[ParentID]
)
这样可以进行多个表数据的查询
还有left join也可以
表的别名.元素名 和 表明.元素名 是一个效果
SQL语句inner join,left join ,right join连接的不同之处219.133.205.* 1楼表A记录如下:
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115 表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
实验如下:
1.left join sql语句如下:
select * from A
left join B
on A.aID = B.bID 结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
(所影响的行数为 5 行) 结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL. 2.right join sql语句如下:
select * from A
right join B
on A.aID = B.bID 结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
(所影响的行数为 5 行) 结果说明: 仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充. 3.inner join sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID 结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404 结果说明: 很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.