subject_52429.htm

vc

<p>
序号：52429 发表者：希望的田野 发表日期：2003-09-10 22:15:17
<br>主题：请教如何用TSQL动态生成查询结果?
<br>内容：有这样一个表<BR>单位 重量 费用号1 费用号2 费用号3 费用1 费用2 费用3<BR>甲&nbsp;&nbsp; 10&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp;&nbsp;&nbsp;0<BR>甲&nbsp;&nbsp; 10&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp;&nbsp;&nbsp;0<BR>乙&nbsp;&nbsp; 20&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30&nbsp;&nbsp;&nbsp;&nbsp;40&nbsp;&nbsp;&nbsp;&nbsp;0<BR><BR>最终要生成这样一个表:<BR>单位 重量 费用号1 费用号2 费用号3 费用1 费用2 费用3<BR>甲&nbsp;&nbsp; 20&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;30&nbsp;&nbsp;&nbsp;&nbsp;20<BR>乙&nbsp;&nbsp; 20&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 30&nbsp;&nbsp;&nbsp;&nbsp;40<BR><BR>因为费用科目有几百项,所以不能用固定的一个列对应一个科目号来建表.<BR>不知各位有没有办法,请给小弟一个提示,不胜感激
<br><a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p>
<hr size=1>
<blockquote><p>
回复者：我的名字叫飞 回复日期：2003-09-11 09:00:48
<br>内容：说清楚吗，大哥
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：dongfanghong 回复日期：2003-09-11 09:06:00
<br>内容：为何不这样建表：<BR>单位 重量 费用号 费用<BR>甲&nbsp;&nbsp; 10&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;<BR>甲&nbsp;&nbsp; 10&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10 <BR>乙&nbsp;&nbsp; 20&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp; 
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：dongfanghong 回复日期：2003-09-11 10:18:04
<br>内容：CREATE TABLE [FeeTable] (<BR>&nbsp;&nbsp;&nbsp;&nbsp;[serial] [int] IDENTITY (1, 1) NOT NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[Weight] [int] NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[Kind1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[Kind2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[Kind3] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[Fee1] [int] NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[Fee2] [int] NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[Fee3] [int] NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;CONSTRAINT [PK_FeeTable] PRIMARY KEY&nbsp;&nbsp;CLUSTERED <BR>&nbsp;&nbsp;&nbsp;&nbsp;(<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[serial]<BR>&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;ON [PRIMARY] <BR>) ON [PRIMARY]<BR>GO<BR><BR><BR>SELECT E.*,D.KIND1,D.KIND2,D.KIND3,D.FEE1, D.FEE2,D.FEE3 FROM <BR>(SELECT NAME,<BR>SUM(CONVERT(INT,K1)) AS KIND1,<BR>SUM(CONVERT(INT,K2)) AS KIND2,<BR>SUM(CONVERT(INT,K3)) AS KIND3,<BR>SUM(F1) AS FEE1,SUM(F2) AS FEE2,SUM(F3) AS FEE3 FROM<BR>(<BR>SELECT NAME,<BR>CASE KIND WHEN 1 THEN KIND END AS k1,<BR>CASE KIND WHEN 2 THEN KIND END&nbsp;&nbsp;AS k2,<BR>CASE KIND WHEN 3 THEN KIND END AS k3 ,<BR>CASE KIND WHEN 1 THEN FEE END AS F1,<BR>CASE KIND WHEN 2 THEN FEE END&nbsp;&nbsp;AS F2,<BR>CASE KIND WHEN 3 THEN FEE END AS F3 <BR><BR>FROM<BR>(SELECT NAME,KIND1 AS KIND,SUM(FEE1) AS FEE&nbsp;&nbsp;FROM (<BR><BR>SELECT NAME,KIND1,FEE1 FROM FEETABLE<BR>UNION ALL<BR>SELECT NAME,KIND2, FEE2 FROM FEETABLE<BR>UNION ALL<BR>SELECT NAME,KIND3, FEE3 FROM FEETABLE<BR>) A<BR>WHERE KIND1&lt;&gt;0<BR>GROUP BY NAME,KIND1&nbsp;&nbsp;) B ) C<BR>GROUP BY NAME ) D,<BR><BR>(SELECT NAME,SUM(WEIGHT) AS WEIGHT<BR>FROM FEETABLE<BR>GROUP BY NAME) E<BR>WHERE D.NAME=E.NAME
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：coolcool888 回复日期：2003-09-11 23:15:33
<br>内容：首先表示感谢!!!<BR>&nbsp;&nbsp;&nbsp;&nbsp;我就是想用查询或存储过程返回一个记录集,记录集具有表2的结构.<BR>&nbsp;&nbsp;&nbsp;&nbsp;2楼说的修改建表的方法,可我实际的表有240个字段,不能那样做,不过你说的有道理,如果没其它的办法倒可以考虑将一个表分成两个表,用一个专门记录费用,就可以那样建表了.<BR>&nbsp;&nbsp;&nbsp;&nbsp;3楼的基本完成我的设想,但还是有点问题请教,在我实际的数据库中费用科目和费用有25个字段而不是3个,费用科目编号有900项之多,每条记录只有一部分费用,也就是说费用号的字段数比实际的费用项数少得多,不能简单的将每个费用号列出来比较,且每月有2万条新记录,我不想将巨大的记录集返回客户端程序处理,不知能不能再修改一下.
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
<font color=red>答案被接受</font><br>回复者：dongfanghong 回复日期：2003-09-12 08:51:31
<br>内容：实际的表有240个字段<BR>\\表结构的合理性值得怀疑。建议你看一看有关系数据库范式方面的东东。<BR><BR>就目前的表结构，可以通过建立存储过程解决。和在应用程序中编程差不多。但是移植性就差了。
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：希望的田野 回复日期：2003-09-13 22:22:34
<br>内容：还是谢谢你,我会去仔细看看存储过程的书.不过你说看一看有关系数据库范式方面的东东,能不能推荐几本?
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：dongfanghong 回复日期：2003-09-15 09:38:23
<br>内容：可以用GOOGLE飕飕；<BR>专门介绍的好书我也没有看过，我是从不同渠道看到的：软件工程的书和MSDN。<BR>
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>