如何将sql中具有相同id的多行组合成一行php [英] How to combine multiple rows with same id in sql into one row php

问题描述

我在oracle sql视图中有这样的表



USER_ID日期LOGIN_COUNT

1 1 2

1 2 4

1 3 5

2 1 6

2 2 7



我想使用以下格式在php中显示它



USER ID 1 2 3。 。 。 31总平均登录

1 2 4 5 11 0.35483871

2 6 7 0 13 0.419354839



是有可能在php中创建这样的表吗？

任何帮助都会受到赞赏

I have a table like this in oracle sql view

USER_ID DATE LOGIN_COUNT
1 1 2
1 2 4
1 3 5
2 1 6
2 2 7

I want to display it in php using the following format

USER ID 1 2 3 . . . 31 TOTAL AVERAGE OF LOGIN
1 2 4 5 11 0.35483871
2 6 7 0 13 0.419354839

Is it possible to create a table like this in php ?
Any help would be appreciated

推荐答案

我当然希望你实际上没有存储日期为整数，但由于您的示例数据意味着，我已相应地制定了我的解决方案。

I certainly hope you're actually not storing dates as integers, but since your example data implies that, I have made my solution accordingly.

WITH avgview AS (
    SELECT  user_id
        ,date
        ,login_count
        ,Avg(login_count) OVER (PARTITION BY user_id) avgcount
    FROM    VIEW
    )
SELECT  *
FROM    avgview
PIVOT   (
    Sum(login_count)
    FOR DATE IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
    )

当你执行SQL命令时，使用AS例如



选择

t1.a为a1，

t1 .b为b1，

t1.c为c1，

t2.a为a2，

t2.b为b2，
t2.c as c2

from table1 t1

join table2 t2 on t2.id = t1.id //请注意此联接可能不正确你的情况

when you do the SQL command, use AS e.g.

Select
t1.a as a1,
t1.b as b1,
t1.c as c1,
t2.a as a2,
t2.b as b2,
t2.c as c2
from table1 t1
join table2 t2 on t2.id = t1.id //note this join may not be correct in your case

这篇关于如何将sql中具有相同id的多行组合成一行php的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！