如何使sql
对select
的陈述,table
依赖于另一个table
的值:
SELECT b.*, detail.*, t.date
FROM board AS b, transaction AS t,
(CASE t.type
WHEN 0 THEN (SELECT * FROM claim)
WHEN 1 THEN (SELECT * FROM retrieve)
END) AS detail
WHERE b.sn='D92AD006325' AND b.id=t.board_id AND detail.id=t.transaction_id
表的某些部分看起来像这样:
| board | | transaction | | claim | | retrieve |
|-------| |-----------------| |-----------| |----------|
| ... | | board_id | | id | | id |
|_______| | transaction_id | | sender | | code |
| type | | location | |__________|
| date | |___________|
|_________________|
如果type = 0
, transaction_id
表示claim.id
,则retrieve.id
,因此输出可能如下:
| board.id | ... | claim.id | claim.sender | location | retrieve.id | retrieve.code | date |
|----------|-----|----------|--------------|-----------|-------------|---------------|------------------|
| 19 | ... | 10 | SenderA | locationA | | | 10/09/2015 18:09 |
| 19 | ... | | | | 8 | 58/03165 | 14/09/2015 11:10 |
| 19 | ... | 14 | SenderB | locationA | | | 20/09/2015 08:10 |
两个简单连接和一个简单过滤器比一个复杂连接要好得多。
SELECT b.*, c.number, c.location, t.type, t.date
FROM board AS b, transaction AS t, claim AS c
WHERE b.sn='D92AD006325' AND b.id=t.board_id AND c.id=t.transaction_id AND t.type=0
UNION ALL
SELECT b.*, r.number, NULL AS location, t.type, t.date
FROM board AS b, transaction AS t, retrieve AS r
WHERE b.sn='D92AD006325' AND b.id=t.board_id AND r.id=t.transaction_id AND t.type=1
Alternative-使用外连接连接两个表,并在列中使用过滤器:
SELECT b.*,
case when t.type = 0 then c.number else r.number end as number,
case when t.type = 0 then c.location else null end as location,
t.type,
t.date
FROM board AS b
INNER JOIN transaction AS t,
ON (b.id=t.board_id)
LEFT OUTER JOIN claim c
ON (c.id=t.transaction_id and t.type = 0)
LEFT OUTER JOIN retrieve r
ON (r.id=t.transaction_id and t.type = 1)
WHERE b.sn='D92AD006325'
我建议使用动态SQL查询。这将使SQL运行逻辑更简单、可读且易于维护。
我尝试指定并命名要匹配的两个表中的每个列,并且还将retrieve
表中的缺失列替换为NULL
,如下所示:
SELECT b.*, c.number, c.location, t.type, t.date
FROM board AS b, transaction AS t, claim AS c
WHERE b.sn='D92AD006325' AND b.id=t.board_id AND c.id=t.transaction_id AND t.type=0
UNION ALL
SELECT b.*, r.number, NULL AS location, t.type, t.date
FROM board AS b, transaction AS t, retrieve AS r
WHERE b.sn='D92AD006325' AND b.id=t.board_id AND r.id=t.transaction_id AND t.type=1
这就是我现在的工作。
有更好的方法吗?