我有两个表:
First:
id | title
1 | aaa
2 | bbb
3 | ccc
Second:
id | first_id | one | two | three | four
1 | 1 | 3 | 1 | 4 | 6
2 | 2 | 4 | 4 | 1 | 2
3 | 3 | 1 | 2 | 3 | 4
我想展示一下:
id | title | min | max
1 | aaa | 1 | 6
2 | bbb | 1 | 4
3 | ccc | 1 | 4
SQL可以做到这一点吗?如何?:)
规范化数据库。以你目前的设置,这不是不可能的,但绝对不推荐。
-编辑-
如果必须,可以使用LEAST()和GREATEST()
-edit2-
SELECT
a.id,
a.title,
LEAST(b.one,b.two,b.three,b.four) min,
GREATEST(b.one,b.two,b.three,b.four) max
FROM first a
INNER JOIN second b ON a.id=b.first_id
阅读汤姆的答案,这将是最好的做法。
不管怎样,我很惭愧:
SELECT f.id, f.title
MIN(LEAST(s.one, s.two, s.three, s.four)) as min,
MAX(GREATEST(s.one, s.two, s.three, s.four)) as max
FROM First f
INNER JOIN Second s on f.id = s.first_id
GROUP BY f.id, f.title
如果Second不能有多个具有相同first_id的行,则可以删除MIN和MAX(以及Groupby)。
select first_id,F.title ,MIN(num) [min],MAX(num) [max] from (
select first_id,[one] [num]from [Second] union all
select first_id,[two] [num]from [Second] union all
select first_id,[three] [num]from [Second] union all
select first_id,[four] [num] from [Second] )[Second]
join [First] F
on Id=first_id
group by first_id,F.title
您可以使用UNION
来完成此操作。试试这个:
SELECT a.id, a.title, MIN(b.c) `Min`, MAX(b.c) `Max`
FROM First a INNER JOIN
(
SELECT first_id, `one` c FROM `Second`
UNION
SELECT first_id, `two` c FROM `Second`
UNION
SELECT first_id, `three` c FROM `Second`
UNION
SELECT first_id, `four` c FROM `Second`
) b on a.id = b.First_ID
GROUP BY a.id
请在此处查看演示