iT邦幫忙

0

MSSQL

Pyan 2019-08-19 11:55:471344 瀏覽
  • 分享至 

  • xImage
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

1
小魚
iT邦大師 1 級 ‧ 2019-08-19 13:18:17
最佳解答

跟上一篇類似,
你先試著做看看吧,
有遇到問題先說明一下你怎麼做的.

看更多先前的回應...收起先前的回應...
Pyan iT邦研究生 5 級 ‧ 2019-08-19 13:37:44 檢舉
select id 
from tableB
where tableB.cid in
(select condition from tableA
where tableA.cid = 'B證照' ) 
group by id
having count(1)>=2

我第二個問題是這樣做的 不過這只能用在條件必須有兩個證照以上的
如果只有一個我就要把 group by 和 having 拿掉才行

小魚 iT邦大師 1 級 ‧ 2019-08-19 14:10:29 檢舉

數量可以用

select COUNT(1) from tableA
where tableA.cid = 'B證照'

也就是

group by id
having count(1)>=(select COUNT(1) from tableA
where tableA.cid = 'B證照')
小魚 iT邦大師 1 級 ‧ 2019-08-19 14:17:29 檢舉

我寫了一個通用的

SELECT b.[cid], a.[ID], COUNT(1) AS num
FROM [person] AS a
INNER JOIN [condition] AS b ON a.[cid] = b.[condition]
WHERE a.[cid] IN (SELECT [condition] FROM [condition] WHERE [cid] = b.[cid])
GROUP BY b.[cid], a.[ID]
HAVING COUNT(1) >= (SELECT COUNT(1) FROM [condition] WHERE [cid] = b.[cid]);

其中
condition資料表是tableA,
person資料表是tableB

執行出來像這樣
https://ithelp.ithome.com.tw/upload/images/20190819/201056942mMEHpiuf3.png

如果要篩選單一證照就用

SELECT b.[cid], a.[ID], COUNT(1) AS num 
FROM [person] AS a 
INNER JOIN [condition] AS b ON a.[cid] = b.[condition]
WHERE a.[cid] IN (SELECT [condition] FROM [condition] WHERE [cid] = b.[cid]) AND b.[cid] = N'E證照'
GROUP BY b.[cid], a.[ID]
HAVING COUNT(1) >= (SELECT COUNT(1) FROM [condition] WHERE [cid] = b.[cid])

執行出來像這樣
https://ithelp.ithome.com.tw/upload/images/20190819/20105694yHCoEK8lIG.png

Pyan iT邦研究生 5 級 ‧ 2019-08-19 14:54:49 檢舉

感謝大大 我有看懂了

2
rogeryao
iT邦超人 7 級 ‧ 2019-08-19 13:24:14
SELECT TableTemp.cid,TableTemp.ID
FROM (
SELECT TableA.cid,TableA.condition,TableB.ID,count(1) as count_x
FROM TableA
INNER
JOIN TableB ON TableA.condition=TableB.cid
WHERE 1=1
AND TableA.cid=N'B證照'
GROUP BY TableA.cid,TableA.condition,TableB.ID
) AS TableTemp
WHERE 1=1
GROUP BY TableTemp.cid,TableTemp.ID
HAVING sum(TableTemp.count_x) = (
SELECT COUNT(1) count_y
FROM TableA
WHERE 1=1
AND TableA.cid=N'B證照'
GROUP BY TableA.cid
)
ORDER BY TableTemp.ID

Demo

我要發表回答

立即登入回答