如何从属性值表结构中获取数据? [英] How to get data from a property-value table structure?

问题描述

我得到了如下 3 个表:

I got 3 tables as follow:

Table 1: FormFields (FieldID, FormID, Title, DateModified, ...)
Table 2: ReportItems (ReportItemID, ReportID, FormID, Title, DateModified, ...)
Table 3: FormValues (ValueID, FormID, FieldID, Value, UserName, DateModified, ...)

例如，我选择了 2 个 FormFields 字段作为我的 ReportItems.这两个字段是一对问答.每个问题可以有 1 个答案，也可以为 NULL.关键是结果应该是这样一个带有'dt's和'dd's的html数据列表(dl)，如下所示:

For example I have selected 2 Fields of FormFields as my ReportItems. These 2 fields are a question and answer couple. Each question can have 1 answer or it could be NULL. The key is the result should be such a html data list (dl) with 'dt's and 'dd's like this:

ReportID    FormID  FieldID Value (Question1)   UserName
ReportID    FormID  FieldID Value (Answer1)     UserName
ReportID    FormID  FieldID Value (Question2)   UserName
ReportID    FormID  FieldID Value (Answer2)     UserName
ReportID    FormID  FieldID Value (Question3)   UserName
ReportID    FormID  FieldID Value (Answer3)     UserName

Question 1
    Answer 1
Question 2

Question 3
    Answer 3
Question 4

Question 5
    Answer 5

我使用了这样的查询，但它没有返回我希望的结果:

I used a query like this, but it doesn't return the result I wish:

select rf.FormID, rf.FieldID, rf.ReportItemTitle, v.Value from 
    (select r.ReportItemID, r.FormID, r.FieldID, r.Title as ReportItemTitle from 
        (select ReportItemID, ReportID, FormID, FieldID, Title 
            from ReportItems 
            where FormID = '2D6C42E1-0C95-4E40-B792-A17E00C001BF' and ShowInList = 0) r
    JOIN
        (select FormID, FieldID, Title 
            from FormFields 
            where FormID = '2D6C42E1-0C95-4E40-B792-A17E00C001BF') f
        ON r.FormID = f.FormID AND r.FieldID = f.FieldID) rf
LEFT JOIN
    (select FormID, FieldID, Value
        from FormValues 
        where FormID = '2D6C42E1-0C95-4E40-B792-A17E00C001BF' and UserName = '0000000000') v
ON rf.FormID = v.FormID AND rf.FieldID = v.FieldID

FormID                                  FieldID                                 ReportItem's Title  Values
2D6C42E1-0C95-4E40-B792-A17E00C001BF    E7A78AC7-10A4-4752-85D6-A17E00C22EF9    Question's Title    user's 1st question goes here...
2D6C42E1-0C95-4E40-B792-A17E00C001BF    E7A78AC7-10A4-4752-85D6-A17E00C22EF9    Question's Title    user's 2nd question goes here...
2D6C42E1-0C95-4E40-B792-A17E00C001BF    E7A78AC7-10A4-4752-85D6-A17E00C22EF9    Question's Title    user's 2nd question goes here...
2D6C42E1-0C95-4E40-B792-A17E00C001BF    8E1FAC2A-02F1-4D0D-A3E0-A184001484D8    Answer's Title  admin's answer to user's 1st question

注意:

• 空答案是指尚未回答的问题(NULL).
• 结果应基于 UserName 和 ReportID
• 所有 ID 均为 GUID
• 问题和答案都存储在 FormValues 表中

也许我应该使用游标，但我真的很困惑该怎么做.如果有人帮助我，我将不胜感激.

Maybe I should use cursor, but I'm really confused what to do. I'd highly appreciate if someone help me.

非常感谢，

卡多

推荐答案

我想我知道你的意思

ReportID    FormID  FieldID Value (Question1)   UserName
ReportID    FormID  FieldID Value (Answer1)     UserName

您希望问题和答案在同一选择栏中吗?您需要按 reportID、FormID、FieldID 进行排序，并让表示逻辑跟踪当前 id 以嵌套方式显示它(或者您需要一个 orm 工具来为您和用户制作对象模型的映射)

do you want questions and answers in the same selection column? you need to order by reportID, FormID, FieldID and let the presentation logic keep track of the current ids to display it in a nested way like (or you need an orm-tool to make the mapping for you and user an object model)

 <ul>
    <li>report1
        <ul>
           <li>form1
               <ul>
                  <li> question1
                      <ul>
                          <li>answer 1 by user 1</li>
                          <li>answer 2 by user 2</li>

....

这篇关于如何从属性值表结构中获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！