我可以使用ALL運算子來測驗“組”的所有值是否存在于另一個查詢/集中？如果是這樣，怎么做？-有解無憂

我有這個資料庫用于假設的電影商店公司的 CS/資料庫理論作業問題：
- 對于那些可能不熟悉這個概念的人來說，電影商店是一個零售場所，顧客可以在其中使用 VHS 磁帶或這種稱為“DVD”的新格式拍攝作品。
  - _{誰知道，也許在遙遠的未來某個時候，我們可能能夠直接通過互聯網觀看電影？}
- DDL 和示例資料如下。
我需要撰寫一個查詢來顯示所有三個芝加哥商店中可用的所有電影：（WI01、、WI02和WI03）。
- 通過查看我們下面的原始資料，我們可以看到只有這 3 個movieId值（D00001、D00006和D00007）對于位于芝加哥的每個movie_store值都有行。 store

CREATE TABLE movie (
    movieId varchar(6)   NOT NULL PRIMARY KEY, 
    title   nvarchar(50) NOT NULL
);

CREATE TABLE store (
    storeId varchar(4)   NOT NULL PRIMARY KEY, 
    city    nvarchar(20) NOT NULL
);

CREATE TABLE movie_store (
    movieid varchar(6) FOREIGN KEY REFERENCES movie ( movieId ),
    storeid varchar(4) FOREIGN KEY REFERENCES store ( storeId ),

    PRIMARY KEY ( movieId, storeId )
);

GO

INSERT INTO movie ( movieId, title )
VALUES
('D00001', N'True Lies'),
('D00002', N'Predator'),
('D00003', N'Last Action Hero'),
('D00004', N'Red Heat'),
('D00005', N'Conan 1'),
('D00006', N'Conan 2'),
('D00007', N'Red Sonja');

INSERT INTO store ( storeId, city ) VALUES
('WI01', N'Chicago'),
('WI02', N'Chicago'),
('WI03', N'Chicago'),
('IL01', N'Atlanta'),
('IL02', N'Nashville');

INSERT INTO movie_store ( movieId, storeId ) VALUES
-- True Lies:
('D00001', 'WI01'),
('D00001', 'WI02'),
('D00001', 'WI03'),
-- 'Predator:
('D00002', 'IL01'),
('D00002', 'IL02'),
-- Last Action Hero:
('D00003', 'WI01'),
-- Red Heat:
('D00004', 'WI01'),
('D00004', 'WI02'),
('D00004', 'IL02'),
-- Conan 1:
('D00005', 'WI01'),
('D00005', 'WI02'),
-- Conan 2:
('D00006', 'WI01'),
('D00006', 'WI02'),
('D00006', 'WI03'),
-- Red Sonja:
('D00007', 'WI01'),
('D00007', 'WI02'),
('D00007', 'WI03');

在我解決問題的研究中，我發現了一個解釋操作員的網站ALL。
- 我的查詢在芝加哥變得獨一無二storeIds。
- 然后，它試圖為芝加哥的每個地點獲取title帶有記錄的電影。storeId

WITH chicagoStores AS (
    SELECT DISTINCT
        storeId
    FROM
        store 
    WHERE
        city = 'Chicago'
)
SELECT DISTINCT
    m.title
FROM
    movie AS m
    INNER JOIN movie_store AS y ON m.movieid = y.movieid
    INNER JOIN store       AS s ON y.storeid = s.storeid
WHERE
    s.storeId = ALL( SELECT storeId FROM chicagoStores )

但是我的查詢回傳零行（并且沒有錯誤），我誤解了ALL運算子嗎？

uj5u.com熱心網友回復：

嘗試這個

select city,title,count(*)
from 
store
inner join movie_store
on store.storeid = movie_store.storeid
inner join movie
on movie_store.movieid = movie.movieid
where city = 'Chicago'
group by city,title
having count(*) = 3

uj5u.com熱心網友回復：

看來我對 ALL 有錯誤的想法。我意識到我可以用這種方式撰寫查詢來獲取出現在芝加哥所有地點的電影。謝謝大家的幫助。

with stores as (
    select count(distinct(storeid)) as count_store
    from store 
    where city = 'Chicago'
),
     count_movies as (
    select z.title, count(*) as count
    from movie z
    join movie_store y on (z.movieid = y.movieid)
    join store x on (y.storeid = x.storeid)
    where x.city = 'Chicago'
    group by z.title
    having count(*) = (select count_store from stores)
)
select title from count_movies

uj5u.com熱心網友回復：

這可以在沒有視窗且不提及chicago2 次的情況下完成

select m.title,
       s.city
from   store s
  inner join movie_store ms on s.storeid = ms.storeid
  inner join movie m on ms.movieid = m.movieid
  cross apply (select count(1) numbers from store s2 where s2.city = s.city group by s2.city) c
where  s.city = 'Chicago'
group by m.title, s.city, c.numbers
having  count(s.storeid) = c.numbers

在這個DBFiddle自己嘗試一下

uj5u.com熱心網友回復：

在插入更多內容并閱讀有關 Relational Division 的這篇文章后，我認為與原始答案相比，我發現了一個更短的查詢：

SELECT
    m.movieId,
    m.title
FROM
    movie AS m
WHERE
    NOT EXISTS (
        SELECT m.movieId, s.storeId FROM store       AS s WHERE city = 'Chicago'
        EXCEPT
        SELECT r.movieId, r.storeId FROM movie_store AS r WHERE r.movieId = m.movieId
    );

下面的解釋是基于這個稍微長一點的版本，在其他方面它的關系演算是相同的：

WITH chicagoStores AS (
    SELECT storeId FROM store WHERE city = 'Chicago'
)
SELECT
    m.movieId,
    m.title
FROM
    movie AS m
WHERE
    NOT EXISTS (
        -- Generate rows we'd *expect to exist* if a given `m.movieId` is present in every Chicago storeId:
        SELECT
            m.movieId,
            s.storeId
        FROM
            chicagoStores AS s

        -- Then subtract the `movie_store` rows that *actually exist* for this m.movieId:
        EXCEPT

        SELECT
            a.movieId,
            a.storeId
        FROM
            movie_store AS a
        WHERE
            a.movieId = m.movieId
    );

生成相同的chicagoStores集合。
然后，過濾(in ) 中的movieId值... movieWHERE
- 從...生成一組假設 movieId, storeId的元組（針對該特定movieId值）chicagoStores
- ...并從（使用）中的實際行中減去...movie_storeEXCEPT
  - This is the same thing as whatIfEveryChicagoStoreHadEveryMovie from my original answer, but computed on a a per-row basis, instead of (conceptually) generating the ( movies X stores ) EXCEPT movie_store Cartesian Product subtraction all-at-once.
    - ...with the implication that this might require less maximum total memory on the database-server, but that's entirely dependent on the execution-plan and DB engine.
- ...and if there are any hypothetical rows remaining (using EXISTS) after the EXCEPT then it means that the movieId is not available in all Chicagoan stores.
- 因此，如果我們反轉謂詞 ( NOT EXISTS)，那么這意味著我們可以測驗以查看特定movieId值是否在所有芝加哥商店中可用。
  - 但要小心chicagoStores，因為當/如果集合為空時它也會有錯誤的位置（NOT EXISTS ( x EXCEPT y )總是true當x為空時，即使y也是空的。
    - 作為一種解決方法，更改FROM movie為FROM anyChicagoStoreMovieIds（anyChicagoStoreMovieIds在我的其他答案中定義的位置）。

uj5u.com熱心網友回復：

x = ALL y運營商不是你想要的。
- 聽起來您想要一個運算子來測驗“x 中的所有值也在 y 中”，也就是一個“ ALL IN”運算子。
  - 不幸的是ALL IN，在 SQL 中不存在，盡管它具有明顯的實用性。
- 該x = ALL y運算子實際上測驗以查看y（右側單串列運算式）中的所有值是否等于單個標量值x。
  - 此功能與問題無關，因為我們不需要測驗某個結果串列是否等于某個單行的列。
  - 其他運算子也沒有多大用處（例如x != ALL y或x < ALL y）。
雖然ALL INSQL 中沒有運算子，但該概念存在于關系代數中，稱為關系除法。
- 從概念上講（如果它存在于 SQL 中）然后x DIVIDE y ON y.foo = x.foo會喜歡GROUP BY x.foo HAVING x.foo ALL IN ( SELECT foo FROM y ) UNGROUP（當然，UNGROUP 也不是一回事）。
- 在 SQL 中實作關系除法是一種常見的 PITA。

從高層次來看，問題可以分解為 4 個步驟：

獲取storeId芝加哥商店的一組值。
- IESELECT storeId FROM store WHERE city = 'Chicago'
另外，獲取這些商店 movieId中所有電影的值集。
- IESELECT * FROM movie_store AS ms INNER JOIN chicagoStores AS cs ON ms.storeId = cs.storeId
chicagoMovies然后按各自的movieId值對集合進行分組/磁區。
然后過濾掉那些每個磁區的storeId值集不等于chicagoStores集合的組/磁區。

但這里是困難的部分：SQL 沒有提供一種方法來評估查詢中每個磁區的謂詞條件GROUP BY。

SQL 更像是關系微積分，您可以在其中描述您想要什么，而不是關系代數，您可以在其中描述您希望它如何完成。與 SQL 相比，Linq 是關系代數查詢語言的一個示例。

在 Linq 中（對于記憶體中的物件，而不是物體框架），你會這樣做：

HashSet<StoreId> chicagoStores = Stores
    .Where( s => s.City == "Chicago" )
    .Select( s => s.StoreId )
    .ToHashSet();

MovieStores
    .GroupBy( ms => ms.StoreId )
    .Where( grp => grp.All( ms => chicagoStores.Contains( ms.StoreId ) ) )
    .SelectMany( grp => grp )
    .Select( ms => ms.Movie )

所以我在 SQL 中采用了一種完全不同的方法：

獲取storeId芝加哥商店的一組值：

WITH chicagoStores AS (
    SELECT
        storeId
    FROM
        store
     WHERE
        city = 'Chicago'
)

StoreId
-------
WI01
WI02
WI03

Get the set of movieId values for movies that are in at least 1 Chicago store.

WITH anyChicagoStoreMovieIds AS (
    SELECT DISTINCT
        ms.movieId
    FROM
        movie_store AS ms
        INNER JOIN chicagoStores AS cs ON
            cs.storeId = ms.storeId
)

movieId
-------
D00001
D00003
D00004
D00005
D00006
D00007

Alternatively, the set of all movieId values in movie could be used, but doing that would make Step 3 potentially much slower.

Generate the CROSS APPLY of Step 1 and Step 2, which generates the Cartesian Product of every Chicagoan storeId with every movieId.

Hence why restricting it to a the smaller upper-bound set from Step 2 makes sense, as there's no point including movieId values that don't appear in any Chicago store.

WITH whatIfEveryChicagoStoreHadEveryMovie AS (
    SELECT
        m.movieId,
        cs.movieId
    FROM
        chicagoStores AS cs
        CROSS APPLY anyChicagoStoreMovieIds AS m
)

movieId  storeId
-------
D00001   WI01
D00001   WI02
D00001   WI03
D00003   WI01
D00003   WI02
D00003   WI03
D00004   WI01
D00004   WI02
D00004   WI03
D00005   WI01
D00005   WI02
D00005   WI03
D00006   WI01
D00006   WI02
D00006   WI03
D00007   WI01
D00007   WI02
D00007   WI03

Now the hard part:

Consider that if any given Chicago storeId had every movieId possible, then such a row would already-exist in movie_store...
...therefore it follows that if we then subtract actual rows in movie_store from Step 3's result, then we'll be left with the set of ( movieId, storeId ) tuples that don't exist but which would need to exist in order for every Chicago storeId to have that movie.

WITH chicagoanMoviesNotAvailableAtEveryChicagoanStore AS (
    SELECT
        w.movieId,
        w.storeId
    FROM
        whatIfEveryChicagoStoreHadEveryMovie AS w
        LEFT OUTER JOIN movie_store AS ms ON
            w.movieId = ms.movieId
            AND
            w.storeId = ms.storeId
    WHERE
        ms.storeId IS NULL
)

movieId  storeId
----------------
D00003   WI02
D00003   WI03
D00004   WI03
D00005   WI03

Then it's just a matter of subtracting chicagoanMoviesNotAvailableAtEveryChicagoanStore from anyChicagoStoreMovieIds (from Step 3), which gives us the set of movieId values that are available at every Chicagoan storeId:

WITH moviesNotIn_moviesNotInAtLeast1ChicagoStore AS (
    SELECT movieId FROM anyChicagoStoreMovieIds
    EXCEPT
    SELECT movieId FROM moviesNotInAtLeast1ChicagoStore
)

movieId  storeId
----------------
D00003   WI02
D00003   WI03
D00004   WI03
D00005   WI03

Which can then be INNER JOINed with movie to get their title information, etc:

 SELECT
     m.movieId,
     m.title
 FROM
     moviesNotIn_moviesNotInAtLeast1ChicagoStore AS ffs
     INNER JOIN movie AS m ON
         ffs.movieId = m.movieId;

movieId  title
----------------
D00001   'True Lies'
D00006   'Conan 2'
D00007   'Red Sonja'

Thus giving the full final query:

WITH
chicagoStores AS (
    SELECT storeId FROM store WHERE city = 'Chicago'
),
anyChicagoStoreMovieIds AS (
    SELECT DISTINCT
        ms.movieId
    FROM
        movie_store AS ms
        INNER JOIN chicagoStores AS cs ON cs.storeId = ms.storeId
),
expectedMovieStores AS (
  SELECT
    m.movieId,
    cs.storeId
  FROM
    chicagoStores AS cs
    CROSS JOIN anyChicagoStoreMovieIds AS m
),
moviesNotInAtLeast1ChicagoStore AS (
  SELECT
    e.*
  FROM
    expectedMovieStores AS e
    LEFT OUTER JOIN movie_store AS ms ON
      e.movieId = ms.movieId
      AND
      e.storeId = ms.storeId
  WHERE
    ms.storeId IS NULL
),
moviesNotIn_moviesNotInAtLeast1ChicagoStore AS (
    SELECT
        movieId
    FROM
        anyChicagoStoreMovieIds
         
    EXCEPT
         
    SELECT
        movieId
    FROM
        moviesNotInAtLeast1ChicagoStore
)
SELECT
    m.movieId,
    m.title
FROM
    moviesNotIn_moviesNotInAtLeast1ChicagoStore AS ffs
    INNER JOIN movie AS m ON
        ffs.movieId = m.movieId;

The CTEs that are only used once can be inlined to shorten the query somewhat:

WITH
chicagoStores AS (
    SELECT storeId FROM store WHERE city = 'Chicago'
),
anyChicagoStoreMovieIds AS (
    SELECT DISTINCT
        ms.movieId
    FROM
        movie_store AS ms
        INNER JOIN chicagoStores AS cs ON cs.storeId = ms.storeId
),
expectedMovieStores AS (
    SELECT
        m.movieId,
        cs.storeId
    FROM
        chicagoStores AS cs
        CROSS JOIN anyChicagoStoreMovieIds AS m
)
SELECT
    m.movieId,
    m.title
FROM
    (
        SELECT
            movieId
        FROM
            anyChicagoStoreMovieIds

        EXCEPT

        SELECT
            e.movieId
        FROM
            expectedMovieStores AS e
            LEFT OUTER JOIN movie_store AS ms ON
                e.movieId = ms.movieId
                AND
                e.storeId = ms.storeId
        WHERE
            ms.storeId IS NULL
    ) AS ffs
    INNER JOIN movie AS m ON
        ffs.movieId = m.movieId;

...which is stioll rather long and complex for what's described as a CS/SQL homework - this took me over 2 hours to figure out because it was driving me mad.

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/441793.html

標籤：sql sql服务器

上一篇：計算列中的城市數-SQLServer

下一篇：SQL-有沒有辦法檢查表的所有列中的重復行