时间戳

首页 > 解决方案 > sql查询不存在的条目

sql-server - sql查询不存在的条目

问题描述

我继承了一个网站及其相应的数据库（SQL Server）。该网站使用存储过程从数据库中提取数据。其中一个存储过程包含一个数据透视表，并且该数据透视表需要 4 多个小时才能运行。这是目前无法接受的。我正在寻求用标准 SQL 查询替换数据透视表的帮助，因为我认为这会更快并提供更好的性能。

这是有问题的支点：

SELECT *
FROM (
    SELECT ac.AID
        ,ac.CatName AS t
        ,convert(INT, ac.Code) AS c
    FROM categories AS ac
    ) AS s
Pivot(Sum(c) FOR t IN (
            [tob]
            ,[ecit]
            ,[tobwcom]
            ,[rnorm]
            ,[raddict]
            ,[rpolicy]
            ,[ryouth]
            ,[rhealth]
            ,…
            )) AS p;

和枢轴的结果

|    AID    |    tob    |    ecit    |    tobwcom    |    rnorm    |
|-----------|-----------|------------|---------------|-------------|
|    1      |    1      |    NULL    |    NULL       |    0        |
|    2      |    1      |    NULL    |    NULL       |    1        |
|    3      |    1      |    NULL    |    NULL       |    0        |
|    4      |    1      |    NULL    |    NULL       |    0        |
|    5      |    1      |    NULL    |    NULL       |    0        |
|    6      |    1      |    NULL    |    NULL       |    1        |

这是源表categories和一些示例数据：

CREATE TABLE categories(
   ArticleID   INTEGER  NOT NULL  
  ,ThemeID     INTEGER  NOT NULL
  ,ThemeName   VARCHAR(7) NOT NULL
  ,Code        BIT  NOT NULL
  ,CreatedTime VARCHAR(7) NOT NULL
);
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (1,1,'tob',1,'57:30.7');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (1,2,'ecig',1,'03:58.3');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (1,5,'rnorm',0,'42:56.5');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (2,1,'tob',1,'57:30.7');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (2,2,'ecig',0,'03:58.3');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (2,5,'rnorm',1,'42:56.5');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (2,6,'raddict',0,'42:59.8');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (3,1,'tob',1,'57:30.7');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (3,2,'ecig',0,'03:58.3');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (3,5,'rnorm',0,'42:56.5');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (21,1,'tob',1,'57:30.7');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (21,2,'ecig',0,'03:58.3');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (21,5,'rnorm',0,'42:56.5');
INSERT INTO categories(ArticleID,ThemeID,ThemeName,Code,CreatedTime) VALUES (21,6,'raddict',0,'42:59.8');

这是包含类别名称的表格——（现在是 mytable）

CREATE TABLE mytable(
   CatID       INTEGER  NOT NULL PRIMARY KEY 
  ,CatName     VARCHAR(7) NOT NULL
  ,CreatedTime DATETIME  NOT NULL
);
INSERT INTO mytable(CatID,CatName,CreatedTime) VALUES (1,'tob','2015-03-12 10:07:54.173');
INSERT INTO mytable(CatID,CatName,CreatedTime) VALUES (2,'ecig','2015-05-18 11:48:16.297');
INSERT INTO mytable(CatID,CatName,CreatedTime) VALUES (4,'tobwcom','2015-06-19 11:12:01.537');
INSERT INTO mytable(CatID,CatName,CreatedTime) VALUES (5,'rnorm','2015-06-22 14:24:02.317');
INSERT INTO mytable(CatID,CatName,CreatedTime) VALUES (6,'raddict','2015-06-22 14:24:13.957');
INSERT INTO mytable(CatID,CatName,CreatedTime) VALUES (7,'ecit','2015-06-22 14:26:18.437');

我需要的是一种方法来执行枢轴在categories. 输出将类似于：

|    AID    |    tob    |    ecit    |    tobwcom    |    rnorm    |
|-----------|-----------|------------|---------------|-------------|
|    1      |    1      |    NULL    |    NULL       |    0        |
|    2      |    1      |    NULL    |    NULL       |    1        |

或者AIDs 的列表和CatName没有任何值的 s。如：

| AID | CatName |
|-----|---------|
| 1   | ecit    |
| 1   | tobwcom |
| 2   | ecit    |
| 2   | tobwcom |

我试过了

select distinct(AID) FROM [categories]
 where [CatName] not in ( 'ecit', 'tobwcom')

但是由此得出的结果，数字似乎并没有相加，但这可能是我的错误。

标签： sql-serverstored-procedurespivot

解决方案

不确定对于这么大的桌子是否足够快。但是对于第二个预期结果，这可能有助于找到丢失的东西。

select a.ArticleID, c.CatName
from #myarticles a
cross join #mycategories c
left join categories ca on (ca.ArticleID = a.ArticleID and ca.ThemeID = c.CatID)
where ca.ArticleID is null;

可以在这里找到一个测试

请注意，此方法受益于 (ArticleID, ThemeID) 上的组合主键索引

作为替代方案，可以将带有 NULL 检查的 LEFT JOIN 更改为NOT EXISTS.

select a.ArticleID, c.CatName
from #myarticles a
join #mycategories c on c.CatID between 1 and 7
where NOT EXISTS
(
   select 1
   from categories ca 
   where ca.ArticleID = a.ArticleID 
     and ca.ThemeID = c.CatID
);

推荐阅读