如何在sql和php中填充min值？ [关闭]

Question

I have a table in SQL:

Table 1

date       name  s1   s2   s3   s4   min_value
7/10/2017  ram   10   11   12   13   11
7/10/2017  tom   17   16   15   14   16
7/10/2017  ara   13   9    26   22   9

If I total all fields in a single date then

s1 total value 40
s2 total value 36
s3 total value 53
s4 total value 49

Here minimum value is 36. It means minimum value column is s2.

I inserted s2 field value in min_value field respectively.

My question is how will I fill min_value field?

Help me please.

Thanks in advance.

Answer 1

Here's code to achieve that:

SELECT [date], s1, s2, s3, s4,
    case when s1 < s2 and s1 < s3 and s1 < s4 then s1
         when s2 < s1 and s2 < s3 and s2 < s4 then s2
         when s3 < s1 and s3 < s2 and s3 < s4 then s3
         else s4 end as [min_value]
FROM (
    SELECT [date], sum(s1) AS s1, sum(s2) AS s2, sum(s3) AS s3, sum(s4) AS s4 FROM [Table1]
    GROUP BY [date]
) AS A