将 2 个表中的数据组合到 1 个动态查询中

Question

我有两个表：

table 1
id    item     itemType 
-----------------------
1     book1    1
2     book2    1
3     laptop1  2

table 2
id    itemId    name    value
------------------------------------------
1     1         author  enid blyton
2     1         title   five 1
3     2         author  enid blyton
4     2         title   five 2
5     3         cpu     i7-940
6     3         ram     4 GB
7     3         vcard   nvidia quadro

当我使用过滤器 itemType = 1 进行查询时，结果应该是：

query 1
id    item    author          title
--------------------------------------------------------
1     book1   enid blyton     five 1
2     book2   enid blyton     five 2

使用过滤器 itemType = 2

query 2
id    item       cpu       ram        vcard
----------------------------------------------
1     laptop1    i7-940    4 GB       nvidia quadro

且不使用过滤器进行

query 3
id    item    author          title      cpu       ram         vcard
---------------------------------------------------------------------------
1     book1   enid blyton     five 1
2     book2   enid blyton     five 2
1     laptop1                            i7-940    4 GB        nvidia quadro

查询我使用表 2 的原因是因为每个 itemType 的参数都是在运行期间创建的，所以它是不可能有像查询 3 中那样的表。

此时，我可以通过以编程方式重建表（使用大量 linq 调用）在 C# 中解决此问题。表1（1K行）和表2（10K行）规模较小，性能不错，但现在表1的大小已经超过100K行，表2超过1M行，性能很差低的。

有没有使用SQL查询的函数可以解决这个问题？

Answer 1

不完全是动态的，但如果您的名字预先都知道，您可以使用PIVOT 来检索您的数据。

PIVOT 通过旋转唯一值来旋转表值表达式
从表达式中的一列到输出中的多列，
并在需要时对任何剩余的执行聚合
最终输出中所需的列值。

SQL语句

SELECT  t1.Id
        , t1.item
        , t2.author
        , t2.title
        , t2.cpu
        , t2.ram
        , t2.vcard
FROM    table1 t1
        INNER JOIN (        
          SELECT  *
          FROM    (
                    SELECT  itemId
                            , name
                            , value 
                    FROM    table2
                  ) s
                  PIVOT (
                    MAX(Value) 
                    FOR name IN (title, author, cpu, ram, vcard)
                  ) p
        ) t2 ON t2.itemId = t1.Id

测试脚本

;WITH table1 (id, item, itemtype) AS (
    SELECT 1, 'book1', 1
    UNION ALL SELECT 2, 'book2', 1
    UNION ALL SELECT 3, 'laptop1', 2
)
, table2 (id, itemId, name, value) AS (
    SELECT 1, 1, 'author', 'enid blyton'
    UNION ALL SELECT 2, 1, 'title', 'five 1'
    UNION ALL SELECT 3, 2, 'author', 'enid blyton'
    UNION ALL SELECT 4, 2, 'title', 'five 2'
    UNION ALL SELECT 5, 3, 'cpu', 'i7 940'
    UNION ALL SELECT 6, 3, 'ram', '4 GB'
    UNION ALL SELECT 7, 3, 'vcard', 'nvidia quadro'
)
SELECT  t1.Id
        , t1.item
        , t2.author
        , t2.title
        , t2.cpu
        , t2.ram
        , t2.vcard
FROM    table1 t1
        INNER JOIN (        
          SELECT  *
          FROM    (
                    SELECT  itemId
                            , name
                            , value 
                    FROM    table2
                  ) s
                  PIVOT (
                    MAX(Value) 
                    FOR name IN (title, author, cpu, ram, vcard)
                  ) p
        ) t2 ON t2.itemId = t1.Id

Answer 2

我建议运行一个查询，从 table2 中返回指定项类型的所有可能名称，如下所示：

select distinct name
from table2 t2
where exists (select null
              from table1 t1
              where t1.itemtype = @itemtype and
                    t1.id = t2.item_id)

在 C# 中，将名称连接到单个逗号分隔的字符串中，然后构造一个类似于 Lieven 的答案的新查询字符串，如下所示

SELECT  t1.item
        , t2.*
FROM    table1 t1
        INNER JOIN (SELECT  *
                    FROM    (SELECT  itemId,
                                     name,
                                     value 
                             FROM    table2) s
                            PIVOT (MAX(Value) 
                                   FOR name IN (/*insert names string here*/)) p
                   ) t2 ON t2.itemId = t1.Id
WHERE t1.itemtype = @itemtype;

：名称字符串替换括号内的注释）。

顺便说一句，如果可能的话，我建议将表 2 中的名称分离到一个单独的查找表中，如下所示：

name_table
----------
name_id
name
itemtype

- 这意味着第一个查询只需查询一个小的查找表，而不是查询整个表 2；它还可用于数据输入时名称值的一致性。