drrhr20884
drrhr20884
2014-02-05 10:37
浏览 120
已采纳

有条件地将查询中的元素插入另一个数据库表

I am querying two tables that produce the desired results. I need to insert elements conditionally from this results array into another table. Please excuse my lack of understanding, but I am fairly new to php and mysql.

the results fields are: bus_id, age, race, and sex. I want to insert the elements of these fields conditionally into another table that has the fields: bus_id, no_people, males, females, and ethnicity%.

The bus_id in results array does = the bus_id in the Insert table.

For example I want to count the people that are assigned to a particular bus_id, and determine the number of people, how many males, females, and ethnicity %.

My thought process might seem convoluted but please excuse me being a novice. If you could put me in the right direction it would be greatly appreciated.

Ok this is the table of results from the query of the first two tables that I have:

bus_name bus_id     cus_name    age      race             sex 
 DHL    10          tom selleck      32 asian             male
Fedex   17      jennifer lopez   34 black           female
UPS 1        jet li              26 black            male
UPS 1       hugh douglas         40 black            male
UPS 1        ray lewis           40 white            male
UPS 1       michael vick         38 black            male
UPS 1        ed reed             44 white            male
UPS 1       joe flacco           17 white            male
UPS 1       bob dole             26 latin            male
UPS 1      deion sanders     15 white            male
UPS 1      bill clinton      15 latin            male
UPS 1      lebron james      41 white            male

I want to insert these elements conditionally in another table called "comp_struct" that has the following fields: bus_id, males, females, ethnicity%( possibly will change it to diversity percentages)forget the ethnicity% for now.

So I would like for it look like this once completed.

bus_id   no_people     males     females    

  1        10            10        0
  17        1             0        1
  10        1             1        0

This is how far I have made it with my code to produce the first table results.

$query1 = $db->query("SELECT bus.bus_name, bus.bus_id, bus_users.purch, bus_users.cus_name,  bus_users.age, bus_users.race, bus_users.sex FROM bus, bus_users WHERE bus_users.purch = bus.bus_id ORDER BY bus.bus_name") or die($db->error);


while($result = mysqli_fetch_assoc($query1))
{
(   "SELECT bus_id, 
        COUNT(*), 
        SUM(IF(sex='male', 1, 0)) AS males,
        SUM(IF(sex='female', 1, 0)) AS females
FROM @query1
GROUP BY bus_id



INSERT INTO comp_struct (bus_id, no_people, males, females)
SELECT bus_id, 
        COUNT(*) AS no_people, 
        SUM(IF(sex='male', 1, 0)) AS males,
        SUM(IF(sex='female', 1, 0)) AS females
FROM @query1
GROUP BY bus_id");

Needless to say this is not producing the desired results.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douye1876
    douye1876 2014-02-05 11:50
    已采纳

    Ignoring the ethnicity field for the moment, you can get the values you want with:-

    SELECT bus_id, 
            COUNT(*), 
            SUM(IF(sex='male', 1, 0)) AS males,
            SUM(IF(sex='female', 1, 0)) AS females
    FROM table_one
    GROUP BY bus_id
    

    and do an insert with:-

    INSERT INTO table_two(bus_id, no_people, males, females)
    SELECT bus_id, 
            COUNT(*) AS no_people, 
            SUM(IF(sex='male', 1, 0)) AS males,
            SUM(IF(sex='female', 1, 0)) AS females
    FROM table_one
    GROUP BY bus_id
    

    EDIT - based on the details you have given now a single statement will do the insert as follows (no need to select the rows separately first)

    INSERT INTO table_two(bus_id, no_people, males, females)
    SELECT bus.bus_id , 
            COUNT(*) AS no_people, 
            SUM(IF(bus_users.sex ='male', 1, 0)) AS males,
            SUM(IF(bus_users.sex ='female', 1, 0)) AS females
    FROM bus
    INNER JOIN bus_users 
    ON bus_users.purch = bus.bus_id 
    GROUP BY bus.bus_id 
    

    EDIT - this gets you the ethnicity as well:-

    SELECT bus_id, no_people, males, females, GROUP_CONCAT(CONCAT(race, race_percentage, '%'))
    FROM
    (
        SELECT Sub1.bus_id, Sub2.no_people, Sub2.males, Sub2.females, Sub1.race, (Sub1.race_count / Sub2.no_people) * 100 AS race_percentage
        FROM
        (
            SELECT bus.bus_id , 
                    bus_users.race, 
                    COUNT(*) AS race_count
            FROM bus
            INNER JOIN bus_users 
            ON bus_users.purch = bus.bus_id 
            GROUP BY bus.bus_id, race
        ) Sub1
        INNER JOIN
        (
            SELECT bus.bus_id , 
                    COUNT(*) AS no_people, 
                    SUM(IF(bus_users.sex ='male', 1, 0)) AS males,
                    SUM(IF(bus_users.sex ='female', 1, 0)) AS females
            FROM bus
            INNER JOIN bus_users 
            ON bus_users.purch = bus.bus_id 
            GROUP BY bus.bus_id
        ) Sub2
        ON Sub1.bus_id = Sub2.bus_id
    ) Sub0
    GROUP BY bus_id
    

    SQL fiddle here for it:-

    http://www.sqlfiddle.com/#!2/c8c9c/1

    点赞 评论

相关推荐