SQL Server 跨表子查詢的Query問題

#sql

whitefloor 2022-03-30 17:09:47 ‧ 843 瀏覽

分享至

各位SQL之神好，最近在寫SQL上碰到一個問題，用的是SQL server

現在有3個table如圖

1.目的是想從Doc取出ID,Title,Content
2.WHERE條件是從Doc找到對應的Title,Content，以及從Tag找到對應的TagName
3.最後再從MappingTable確認TagID是否有對到DocID

目前第三步卡住，不知道sub select怎麼下去做確認（囧
下面是完成到一半的SQL

另外想請問SQL該怎麼練習
買一些書或是網路資源都是教初階語法，看了都會
不過要用到複雜syntax就有問題了
一些意外好用的用法或設定要碰到了去查才知道
其他像是slow query監控/資料庫效率監控/歷史log之類的開發常用工具...
到google搜尋資源的時候通常都是一些基礎教學，資源不多
是不是只能去MSDN之類的官方啃文件才有用?

我SQL真的太爛了＝＝

CREATE PROCEDURE findDoc
    @title nvarchar(20) = NULL,
    @content nvarchar(20) = NULL,
    @TagName nvarchar(20) = NULL,
AS
BEGIN
    SELECT [ID],[Title],[Content] FROM findDoc
    WHERE [ID] = (
        SELECT [ID] FROM findDoc
        WHERE 1=1
        AND (1 = (CASE WHEN @title IS NULL THEN 1 ELSE 0 END) OR [Title] = @title)
        AND (1 = (CASE WHEN @content IS NULL THEN 1 ELSE 0 END) OR [Content] = @content)
    )

    -- Tag
    SELECT [TagName] FROM Tag
    WHERE [TagName] = @TagName
    
    -- MappingTable
    SELECT [DocID],[TagID] FROM MappingTable
    WHERE DocID=? AND TagID =?
END

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

一級屠豬士

iT邦大師 1 級 ‧ 2022-03-30 22:19:35

最佳解答

create table doc (
  id int
, title text
, content text
);

insert into doc values
(1, 'test title', '9527'),
(2, 'test2', '9528');

create table tag (
  id int
, tagname text
);

insert into tag values
(1, '周星馳'),
(2, '小島南');


create table mapping (
  docid int
, tagid int
);

insert into mapping values
(1,1);

create or replace function f_chk_available(text, text, text)
returns boolean
language sql
as
$code$
with t1 as (
select id
  from doc
 where title = $1
   and content = $2
),t2 as (
select id
  from tag
 where tagname = $3
)
select count(t1.id)::int::boolean
  from t1
  join mapping m
    on t1.id = m.docid
  join t2
    on m.tagid = t2.id;
$code$;

select a.*
     , f_chk_available(title, content, tag)::text as available
  from (values ('test title', '9527', '周星馳')
             , ('test title', '9527', '小島南')
             , ('test2', '9528', '周星馳')
             , ('test2', '9528', '小島南')
       ) as a(title, content, tag);
 
   title    | content |  tag   | available 
------------+---------+--------+-----------
 test title | 9527    | 周星馳 | true
 test title | 9527    | 小島南 | false
 test2      | 9528    | 周星馳 | false
 test2      | 9528    | 小島南 | false
(4 rows)

Demo

回應 2
分享
檢舉

whitefloor iT邦研究生 2 級 ‧ 2022-03-30 23:45:14 檢舉

太猛了...大大
還有那個mock SQL的網站，又長知識了
感謝你這麼晚了還解答我的蠢問題

whitefloor iT邦研究生 2 級 ‧ 2022-03-31 01:04:51 檢舉

這是看大大的解答自己想出來的
不過感覺還是差一截XD

select id,title,content from doc
  where title = 'test title'
  AND content = '9527'
  AND EXISTS (
       select count(*) from mapping
       where docid = (
          select id from doc
          where title = 'test title' AND content = '9527'
          ) 
          AND tagid = (
          select id from tag
          where tagname = '周星馳'
          )
      )

登入發表回應