麻烦哪个高手能给我一个SQL server 2005以上版本的多表查询的代码 越详细越好，谢谢！

Question

有存储过程的代码也一起给我吧

Answer 1

多表查询： 下面是同样效果的存储过程。 你应该先描述你的问题，看别人的也能解决你的问题，前提你是高手。
有具体问题可以在9-18点之间hi我联系，共同学习。

SELECT DISTINCT b.[billid], b.[billtype], b.[billdate], b.[billstates],
b.[billnumber], b.[inputman], b.[auditman], b.[note], b.[summary], b.[quantity],
b.[ysmoney], b.[ssmoney], b.[araptotal], b.[taxrate], b.[auditdate],
CASE WHEN b.[billtype] IN (44, 45) THEN b.[ssname] ELSE b.[cname] END AS [cname],
b.[ename] AS [employeename],
b.[auditmanname],
b.[inputmanname],
b.[aname] AS [accountname],
b.[departmentname],
b.[regionname]
FROM VW_C_BillIDX b LEFT JOIN
(SELECT DISTINCT bm.[Bill_ID], p.[Class_ID]
FROM BuyManageBill bm, Products p
WHERE bm.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT sm.[Bill_ID], p.[Class_ID]
FROM SaleManageBill sm, Products p
WHERE sm.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT st.[Bill_ID], p.[Class_ID] FROM StoreManageBill st, Products p
WHERE st.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT gc.[Bill_ID], p.[Class_ID] FROM GoodsCheckBill gc, Products p
WHERE gc.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT tm.[Bill_ID], p.[Class_ID] FROM TranManagebill tm, Products p
WHERE tm.[P_ID]=p.[Product_ID]) pd
ON b.[BillID]=pd.[Bill_ID]
WHERE (b.[billdate] BETWEEN '2009-08-02 00:00:00' AND '2009-08-02 23:59:59'
and b.billtype = 15 )

--万能单据查询
CREATE PROCEDURE TS_C_QrAllBill
( @lMode INT,--0 单据查询,1 草稿查询
@Begindate DATETIME,
@EndDate DATETIME,
@szC_id VARCHAR(30)='',--往来单位
@szZd_id VARCHAR(30)='', --制单人
@szE_id VARCHAR(30)='',--经手人
@szP_id VARCHAR(30)='',--商品ID
@szSh_id VARCHAR(30)='',--审核人
@szSs_id VARCHAR(30)='',--出仓库
@szSd_id VARCHAR(30)='',--入仓库
@szBillcode VARCHAR(60)='',
@szComment VARCHAR(300)='',
@nBilltype INT=0
)
WITH ENCRYPTION
AS
SET NOCOUNT ON
DECLARE @SQLScript VARCHAR(8000)

IF @szC_id<>'' SELECT @szC_id=ISNULL(@szC_id, '%')+'%'
IF @szZd_id<>'' SELECT @szZd_id=ISNULL(@szZd_id, '%')+'%'
IF @szE_id<>'' SELECT @szE_id=ISNULL(@szE_id, '%')+'%'
IF @szP_id<>'' SELECT @szP_id=ISNULL(@szP_id, '%')+'%'
IF @szSh_id<>'' SELECT @szSh_id=ISNULL(@szSh_id, '%')+'%'
IF @szSs_id<>'' SELECT @szSs_id=ISNULL(@szSs_id, '%')+'%'
IF @szSd_id<>'' SELECT @szSd_id=ISNULL(@szSd_id, '%')+'%'

IF @lMode=0
BEGIN
SELECT @SQLScript='SELECT DISTINCT b.[billid], b.[billtype], b.[billdate], b.[billstates],
b.[billnumber], b.[inputman], b.[auditman], b.[note], b.[summary], b.[quantity],
b.[ysmoney], b.[ssmoney], b.[araptotal], b.[taxrate], b.[auditdate],
CASE WHEN b.[billtype] IN (44, 45) THEN b.[ssname] ELSE b.[cname] END AS [cname],
b.[ename] AS [employeename],
b.[auditmanname],
b.[inputmanname],
b.[aname] AS [accountname],
b.[departmentname],
b.[regionname]
FROM VW_C_BillIDX b LEFT JOIN
(SELECT DISTINCT bm.[Bill_ID], p.[Class_ID] FROM BuyManageBill bm, Products p
WHERE bm.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT sm.[Bill_ID], p.[Class_ID] FROM SaleManageBill sm, Products p
WHERE sm.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT st.[Bill_ID], p.[Class_ID] FROM StoreManageBill s
t, Products p
WHERE st.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT gc.[Bill_ID], p.[Class_ID] FROM GoodsCheckBill gc, Products p
WHERE gc.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT tm.[Bill_ID], p.[Class_ID] FROM TranManagebill tm, Products p
WHERE tm.[P_ID]=p.[Product_ID]
) pd ON b.[BillID]=pd.[Bill_ID]'
+' WHERE (b.[billdate] BETWEEN '+CHAR(39)+CONVERT(VARCHAR(10),@BeginDate,20)+CHAR(39)
+' AND '+CHAR(39)+CONVERT(VARCHAR(10),@EndDate,20)+CHAR(39)+')'

END
ELSE
BEGIn

SELECT @SQLScript='SELECT DISTINCT b.[billid], b.[billtype], b.[billdate], b.[billstates],
b.[billnumber], b.[inputman], b.[auditman], b.[note], b.[summary], b.[quantity],
b.[ysmoney], b.[ssmoney], b.[araptotal], b.[taxrate], b.[auditdate],
CASE WHEN b.[billtype] IN (44, 45) THEN b.[ssname] ELSE b.[cname] END AS [cname],
b.[ename] AS [employeename],
b.[auditmanname],
b.[inputmanname],
b.[aname] AS [accountname],
b.[departmentname],
b.[regionname]
FROM VW_C_BillDraftIDX b LEFT JOIN
(SELECT DISTINCT bm.[Bill_ID], p.[Class_ID] FROM BuyManageBillDrf bm, Products p
WHERE bm.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT sm.[Bill_ID], p.[Class_ID] FROM SaleManageBillDrf sm, Products p
WHERE sm.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT st.[Bill_ID], p.[Class_ID] FROM StoreManageBillDrf st, Products p
WHERE st.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT gc.[Bill_ID], p.[Class_ID] FROM GoodsCheckBillDrf gc, Products p
WHERE gc.[P_ID]=p.[Product_ID]
UNION
SELECT DISTINCT tm.[Bill_ID], p.[Class_ID] FROM TranManagebillDrf tm, Products p
WHERE tm.[P_ID]=p.[Product_ID]
) pd ON b.[BillID]=pd.[Bill_ID]'
+' WHERE (b.[billdate] BETWEEN '+CHAR(39)+CONVERT(VARCHAR(10),@BeginDate,20)+CHAR(39)
+' AND '+CHAR(39)+CONVERT(VARCHAR(10),@EndDate,20)+CHAR(39)+')'

END

IF @szC_id<>'' SELECT @SQLScript=@SQLScript+' AND b.[cclass_id] LIKE '+CHAR(39)+@szC_id+CHAR(39)
IF @szE_id<>'' SELECT @SQLScript=@SQLScript+' AND b.[eclass_id] LIKE '+CHAR(39)+@szE_id+CHAR(39)
IF @szZd_id<>'' SELECT @SQLScript=@SQLScript+' AND b.[inputmanclass_id] LIKE '+CHAR(39)+@szZd_id+CHAR(39)
IF @szSh_id<>'' SELECT @SQLScript=@SQLScript+' AND b.[auditmanclass_id] LIKE '+CHAR(39)+@szSh_id+CHAR(39)
IF @szP_id<>'' SELECT @SQLScript=@SQLScript+' AND pd.[class_id] LIKE '+CHAR(39)+@szP_id+CHAR(39)
IF @szSs_id<>'' SELECT @SQLScript=@SQLScript+' AND b.[ssclass_id] LIKE '+CHAR(39)+@szSs_id+CHAR(39)
IF @szSd_id<>'' SELECT @SQLScript=@SQLScript+' AND b.[sdclass_id] LIKE '+CHAR(39)+@szSd_id+CHAR(39)
IF @szBillcode<>'' SELECT @SQLScript=@SQLScript+' AND b.[billnumber] LIKE '+CHAR(39)+'%'+@szBillcode+'%'+CHAR(39)
IF @szcomment<>'' SELECT @SQLScript=@SQLScript+' AND b.[note] LIKE '+CHAR(39)+'%'+@szcomment+'%'+CHAR(39)
IF @nBilltype<>0 SELECT @SQLScript=@SQLScript+' AND b.[billtype]='+CAST(@nBilltype AS VARCHAR)
ELSE SELECT @SQLScript=@SQLScript+' AND b.[billtype] NOT IN (58)'
SELECT @SQLScript=@SQLScript+' ORDER BY b.[billdate]'

--PRINT @SQLScript
EXEC (@SQLScript)
GOTO SUCCEE

SUCCEE:
RETURN 0

Answer 2

<%@ page language="java" import="java.util.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">

<title>测试</title>
<style type="text/css">
*{
margin:0; padding:0;
}/*所有对象外边距和内边距设置为零*/
body{
font-family: arial, 宋体, serif;
font-size:12px;
padding:50px;
}/*设置页面字体为“宋体”，大小为“norma”,内边距
为"padding"l*/
span{
font-family: arial, 宋体, serif;
font-size:18px;
padding:16px;
}
#item{
line-height: 24px;
list-style-type: none;
background:#666;
}
#item a{
display: block;
width: 80px;
text-align:center;
text-decoration:none; /*除去下划线*/
}
#item a:link { /*访问之前的样式*/
color:#666;
text-decoration:none;
}
#item a:visited { /*访问之后的样式*/
color:#666;
text-decoration:none;
}
#item a:hover { /*鼠标滑过*/
color:#FFF;
text-decoration:none;
font-weight:bold; /*设置字体的粗细*/
background:#999;
}
#item li {
float: left; /*让LI标签浮动到左边，一级菜单并排*/
width: 80px;
background:#CCC;
}
#item li ul {
line-height: 27px; /*行高*/
list-style-type: none; /*去除UL前面的点*/
text-align:left;
left: -999em;
width: 180px;
position: absolute;
}
#item li ul li{
float: left;
width: 180px;
background: #F6F6F6;
}
#item li ul a{
wedisplay: block;
width: 180px;
text-align:left;
padding-left:24px;
}
#item li ul a:link {
color:#666;
text-decoration:none;
}
#item li ul a:hover {
color:#F3F3F3;
text-decoration:none;
font-weight:normal; /*字体的粗细*/
background:#C00;
}
#item li:hover ul {
left: auto;
}
#item li.iehover ul {
left: auto;
}
#content {
clear: left;
}

</style>
<script type="text/JavaScript">
navHover = function() {
var lis = document.getElementById("item").getElementsByTagName("LI");
for (var i=0; i<lis.length; i++) {
lis[i].onmouseover=function() {

this.className+=" iehover";
}
lis[i].onmouseout=function(){
this.className=this.className.replace(new RegExp(" iehover\\b"), "");//这句是把类名是iehover的变为空
}
}
}
if (window.attachEvent) window.attachEvent("onload", navHover);
</script>

</head>

<body>

<div>
<span>下拉菜单的demo</span>
<ul id=item>
<li class=menu><a href=#>菜单一</a>
<ul class=menu1>
<li><a href=#>选项一</a></li>
<li><a href=#>选项二</a></li>
<li><a href=#>选项三</a></li>
</ul>
</li>
<li class=menu><a href=#>菜单二</a>
<ul class=menu1>
<li><a href=#>选项一</a></li>
<li><a href=#>选项二</a></li>
<li><a href=#>选项三</a></li>
</ul>
</li>
</ul>

</div>

</body>
</html>

如果对您有帮助，请记得采纳为满意答案，谢谢！祝您生活愉快！

vaela

Answer 3

两表查询的:

2> SELECT
3> test_main.id AS main_id,
4> test_main.value AS main_value,
5> test_sub.id AS sub_id,
6> test_sub.value AS sub_value
7> FROM
8> test_main, test_sub
9> WHERE
10> test_main.id = test_sub.main_id;

存储过程
1> CREATE PROCEDURE HelloWorld AS
2> BEGIN
3> PRINT 'Hello World';
4> END;
5> go

1> DECLARE @RC int
2> EXECUTE @RC = HelloWorld
3> PRINT @RC
4> go
Hello World
0本回答被提问者和网友采纳

Answer 4

多表连接查询 有 内连接 外连接。。

select * from a,b where a.aid=b.bid

select * from a left join b on a.aid=b.bid

......

自己在 去 研究下吧。其实 很简单的。

Answer 5

需求在哪里~