日期:2014-05-16  浏览次数:20415 次

关于查询引用单列多行值的问题
本帖最后由 u013985466 于 2014-04-26 22:27:35 编辑
  最近,遇到一个麻烦的问题:一个绩效考核工程,其绩效项目有将近40个,每个绩效项目分数(表2)的得出,来源于原始指标(表1)的值(有将近100个原始指标类别,每个绩效项目可能引用其中3-5个指标的值)。传统的做法是,有多少个原始指标类别,就建立多少列,这样引用列值计算就没有丝毫困难。但是,本工程由于种种原因,将所有类别的指标实际值,统一存放在表1的列index_value中,这就涉及表2的列eva_score需要引用表1对应年、月、部门以及多个对应指标的实际值。有如下解决思路,以计算项目1在2014年4月的项目分数为例,请各位兄弟评析一下:
  解决思路1:查询表1中指标1、指标2、指标3在2014年4月的原始数值,分别赋予其列名为指标1、指标2、指标3,然后带入表2的“eva_score=(指标1实际值+指标2实际值)/指标3实际值”运算公式,时间和部门的匹配通过where子句来控制。
 解决思路2:大致等同于思路1,不过是利用公用表达式来实现。

-------------------------------------------------------
表1
index_name m_year m_month dep_name index_value
指标1 2014 4 A部门 0.01
指标1 2014 4 B部门 0.03
指标1 2014 4 C部门 0.02
指标1 2014 4 D部门 0.04
指标1 2014 4 E部门 0.06
指标2 2014 4 A部门 0.133
指标2 2014 4 B部门 0.266
指标2 2014 4 C部门 0.185
指标2 2014 4 D部门 0.165
指标2 2014 4 E部门 0.178
指标3 2014 4 A部门 0.458
指标3 2014 4 B部门 0.666
指标3 2014 4 C部门 0.782
指标3 2014 4 D部门 0.638
指标3 2014 4 E部门 0.618

-----------------------------------------------------------------
表2
eva_item m_year m_month dep_name eva_score
项目1 2014 4 A部门
项目1 2014 4 B部门
项目1 2014 4 C部门
项目1 2014 4 D部门
项目1 2014 4 E部门
计算规则:项目1的 eva_score=(指标1实际值+指标2实际值)/指标3实际值,需要引用对应年、月、部门以及对应指标的实际值,完成混合运算

------------------------------------------------------------------
相关测试数据如下:由于无法实现数据库记录导出为insert into样式的sql脚本,请参照前面的文本作为测试数据。

CREATE TABLE [dbo].[table1](
[index_name] [nvarchar](255) NULL,
[m_year] [float] NULL,
[m_month] [float] NULL,
[dep_name] [nvarchar](255) NULL,
[index_value] [float] NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[table2](
[eva_item] [nvarchar](255) NULL,
[m_year] [float] NULL,
[m_month] [float] NULL,
[dep_name] [nvarchar](255) NULL,
[eva_score] [float] NULL
) ON [PRIMARY]

GO

------解决方案--------------------
表1 中全部是项目1的数据吗? 
表2 的列eva_item 与表1 有什么关联没?
------解决方案--------------------
你想要的结果是怎样的呢
------解决方案--------------------
引用:
Quote: 引用:

你想要的结果是怎样的呢


举例:A部门2014年4月的项目分数eva_score 为:=(指标1实际值+指标2实际值)/指标3实际值,这里需要引用表1在2014年4月,关于A部门的指标1、指标2和指标3的数据,即(0.01+0.133)/0.458=0.312227074

可见,业务逻辑相当简单,困难在于如何查询引用单列多行值的问题,实在没有办法,只有进行行转列处理了,将表1的100多个指标由行变列。


行专列确实是一个好办法,不过,100多个指标,稍微要多写一点代码。
------解决方案--------------------
引用:
如果行转列,则可能出现100多个指标列,总觉得表的可读性和可维护性较差,并且在每个绩效项目分数的计算公式中,只需要引用几个类别的指标数值。因此,还是希望保留现有的数据表结构。


那就写子查询把,比如:


select 
(select SUM(xxx) from 表 where index_name in ('指标1','指标2')) /
select SUM(xxx) from 表 where index_name in ('指标3'))
from tb

------解决方案--------------------
SQL Server 2000桌面引擎(MSDE 2000)。
这是一个可以打包到你的安装程序里,你百度一下,查查资料看看怎么打进去,都是比简单操作。

------解决方案--------------------
引用:
Quote: 引用:

SQL Server 2000桌面引擎(MSDE 2000)。
这是一个可以打包到你的安装程序里,你百度