跟上一篇類似,
你先試著做看看吧,
有遇到問題先說明一下你怎麼做的.
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 拿掉才行
數量可以用
select COUNT(1) from tableA
where tableA.cid = 'B證照'
也就是
group by id
having count(1)>=(select COUNT(1) from tableA
where tableA.cid = 'B證照')
我寫了一個通用的
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])
執行出來像這樣
感謝大大 我有看懂了
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