group concat在mysql中的不同表中划分两个值

I would like to divide total value of table1 with value of table2 in mysql with group concate(Auto Column).

我想将table1的总值与mysql中table2的值除以group concate(Auto Column)。

Manual like this: 
Select  A01-07-2013,B02-07-2013,C03-07-2013 INTO @d1,@d2,@d3 FROM Table1;
Update Table2 set Table2.A01-07-2013/@d1, 
Table2.B02-07-2013/@d2,Table2.C03-07-2013/@d3;

But i don't know to do like this in group concate in mysql.

但是我不知道在mysql的group concate中这样做。

In example, i have two tables as the following:

在示例中,我有两个表如下:

Table1
A01-07-2013 C02-07-2013 C03-07-2013
1000       2000       30000

Table2
A01-07-2013 B02-07-2013 C03-07-2013
100         40          50  
55          33          90

Coding:

SET @actb_pmsum = NULL;
SELECT
    GROUP_CONCAT(DISTINCT CONCAT(
        "sum(`",DATE_FORMAT(`currentdate`,'%d-%m-%Y'),"`) as '",
        DATE_FORMAT(`currentdate`,'%d-%m-%Y'),"'"
        )
    ) INTO @actb_pmsum
FROM tabl1;

1 个解决方案

#1

Assuming your table structure as

假设您的表结构为

Table1
A01-07-2013 1000
C02-07-2013 2000
C03-07-2013 30000

Table2
A01-07-2013 100
A01-07-2013 55
B02-07-2013 40
B02-07-2013 33
C03-07-2013 50
C03-07-2013 90

The following should work

以下应该有效

SELECT 
  table1.date,
  SUM(table1.value) / SUM(table2.value)
FROM
  table1 
  JOIN table2 ON ( table1.date = table2.date )
GROUP by
  table1.date

Since mysql does not have crosstab, you can then format your query as per your requirements, also note that you would probably need to do a cross join and handle no rows from either of the tables

由于mysql没有交叉表,您可以根据需要格式化查询,还要注意您可能需要进行交叉连接并且不处理任何一个表中的行

#1