英文:
SQL Multiple records with foreign key. Need to verify that all are present with conditions to return answer
问题
我有一个名为t1的表,我正在尝试返回所有具有状态a-e记录的外键,其中a-d有日期而e没有。每个外键将与大约10条记录相关联(为了简化而缩短)。状态不是唯一的,因此每个FK可以具有多个状态的重复。
尝试1:
Select FK, sum(case when status = 'a' and date is not null then 1 else 0 end +
case when status = 'b' and date is not null then 1 else 0 end +
case when status = 'c' and date is not null then 1 else 0 end +
case when status = 'd' and date is not null then 1 else 0 end +
case when status = 'e' and date is null then 100 else 0 end) as sum_status
from t1
group by FK
order by sum_status desc;
在这个示例中,我得到了:
FK = 123, 456
sum_status = 104, 2
这不是一个理想的解决方案,因为它显示了FK的每个记录,而我只想要符合条件的记录,因此我仍然需要使用试错方法来获取正确的记录。
我目前正在尝试编写一个游标来循环遍历并检查是否有每个所需状态的记录。我是SQL的相对初学者,所以我相信有一种简单的方法来做到这一点,如果我能得到一些提示或指导就好了。
英文:
I have a table t1 that I'm trying to return all foreign keys that have a record with status a-e where a-d have dates and e doesn't. Each foreign key will relate to roughly 10 records(shortened for ease). The status' are not unique so each FK can have multiple repeats of the status.
FK | Status | Date |
---|---|---|
123 | a | date1 |
123 | b | date2 |
123 | c | date3 |
123 | d | date4 |
123 | e | null |
456 | a | null |
456 | b | date6 |
456 | c | date7 |
Attempt 1:
Select FK, sum(case when status = 'a' and date is not null then 1 else 0 end +
case when status = 'b' and date is not null then 1 else 0 end +
case when status = 'c' and date is not null then 1 else 0 end +
case when status = 'd' and date is not null then 1 else 0 end +
case when status = 'e' and date is null then 100 else 0 end) as sum_status
from t1
group by FK
order by sum_status desc;
In this example I get
FK = 123, 456
sum_status = 104, 2
This is a less than ideal solution as its showing every record for the FK whereas I only want the ones that match the criteria so I still need to use trial and error to get the right one.
I'm trying at the moment to write a cursor to loop through and check that there's one of each required status.
I'm a relative beginner with SQL so I'm sure there's an easy way to do this so if I could get a tip or a point in the right direction please.
答案1
得分: 1
一个相对简单的解决方案是仅使用一些存在的子查询来确认您的各种条件 - 唯一的问题可能是您实际拥有多少数据以及索引设置如何,以确定您是否会有性能不佳的查询。 (您还可以通过将一组表连接在一起,并将“FK”列作为连接谓词来实现几乎相同的结果)
示例:
SELECT
DISTINCT
FK
FROM t1 t1a
WHERE [Status] = 'a' AND [Date] Is Not Null
AND EXISTS
(
SELECT 1 FROM t1 t1b WHERE t1b.FK = t1a.FK
AND t1b.[Status] = 'b'
AND t1b.[Date] Is Not Null
)
AND EXISTS
(
SELECT 1 FROM t1 t1c WHERE t1c.FK = t1a.FK
AND t1c.[Status] = 'c'
AND t1c.[Date] Is Not Null
)
AND EXISTS
(
SELECT 1 FROM t1 t1d WHERE t1d.FK = t1a.FK
AND t1d.[Status] = 'd'
AND t1d.[Date] Is Not Null
)
AND EXISTS
(
SELECT 1 FROM t1 t1e WHERE t1e.FK = t1a.FK
AND t1e.[Status] = 'e'
AND t1e.[Date] Is Null
)
英文:
A simple enough solution is to just use some EXISTS sub-queries to confirm your various conditions - the only question might be how much data you actually have, and how the indexing is set, as to whether you'll have a poor-performing query or not. (You could also achieve pretty much the same results by JOIN-ing a set of the tables together, with the "FK" column as the JOIN predicate)
Example:
SELECT
DISTINCT
FK
FROM t1 t1a
WHERE [Status] = 'a' AND [Date] Is Not Null
AND EXISTS
(
SELECT 1 FROM t1 t1b WHERE t1b.FK = t1a.FK
AND t1b.[Status] = 'b'
AND t1b.[Date] Is Not Null
)
AND EXISTS
(
SELECT 1 FROM t1 t1c WHERE t1c.FK = t1a.FK
AND t1c.[Status] = 'c'
AND t1c.[Date] Is Not Null
)
AND EXISTS
(
SELECT 1 FROM t1 t1d WHERE t1d.FK = t1a.FK
AND t1d.[Status] = 'd'
AND t1d.[Date] Is Not Null
)
AND EXISTS
(
SELECT 1 FROM t1 t1e WHERE t1e.FK = t1a.FK
AND t1e.[Status] = 'e'
AND t1e.[Date] Is Null
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论