MySQL查询从一个查询中的第二个表中选择一些数据[关闭]

问题描述：

Basically, I have a function that exports data to a csv format for excel... I'm trying to have a single query to select all of the sales record within the selected time period, but I need the first column that is queried to come from another table. The current query I have is as follows:

$select = "SELECT `record_date` AS 'Sale Date',
                  `record_order_number` AS 'Order #',
                  `record_activity_number` AS 'Activity #',
                  `record_total_diy_revenue` AS 'Revenue $'
             FROM st_entered_records 
            WHERE `record_user_id` = '$this->user_id' AND 
                  `record_date` BETWEEN '$this->start_date' AND '$this->end_date' ";

Before

`record_date` AS 'Sale Date'

I need it to run a second select to get the users first and last name in format:

"LastName, FirstName"
 (user_last_name)", "(user_first_name) from a second table (st_users) 
 where `user_id` = '$this->user_id'

Any help would be appreciated! :)

答

You could do a join example of one:

    SELECT t2.lastName,t2.firstName,
           t1.record_date AS 'Sale Date',
           t1.record_order_number AS 'Order #',
           t1.record_activity_numbe AS 'Activity #',
           t1.record_total_diy_revenue AS 'Revenue $' 

    FROM st_entered_records1 t1

    INNER JOIN stusers t2 ON t1.record_user_id=t2.userid

    WHERE t1.userid = $this->user_id AND
    t1.record_date BETWEEN $this->start_date AND $this->end_date

Be sure you match the table names and field names I'm sure you already know this :)

EDIT: Stongly advise you do not use MYSQL_*

Reason:

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used.

答

Try

SELECT
    `st_entered_records`.`record_date` AS 'Sale Date',
    `st_entered_records`.`record_order_number` AS 'Order #',
    `st_entered_records`.`record_activity_number` AS 'Activity #',
    `st_entered_records`.`record_total_diy_revenue` AS 'Revenue $',
    `st_users`.`user_last_name` AS 'LastName',
    `st_users`.`user_first_name` AS 'FirstName',
FROM `st_entered_records`
INNER JOIN `st_users` ON `st_entered_records`.`record_user_id` = `st_users`.`user_id`
WHERE
    `record_user_id` = :uid AND
    `record_date` BETWEEN :startDate AND :endDate;

By the way I've replaced the variables with named parameters. I strongly recommend the use of prepared statements, but if you insist on using concatenated queries (whether with mysql, mysqli, or PDO), remember to escape the variables!

MySQL查询从一个查询中的第二个表中选择一些数据[关闭]

相关推荐