MSSQL

ms sql

Pyan 2019-08-19 11:55:47 ‧ 1371 瀏覽

分享至

登入發表討論

直播研討會

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

直播中

2 個回答

小魚

iT邦大師 1 級 ‧ 2019-08-19 13:18:17

最佳解答

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

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

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

執行出來像這樣

如果要篩選單一證照就用

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])

執行出來像這樣

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

感謝大大我有看懂了

登入發表回應

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