有没有办法优化这个mysql查询...？

发布于 2024-08-27 08:50:07 字数 3650 浏览 3 评论 0原文

比如说，我得到了这两个表...

Table 1 : Hotels
   hotel_id   hotel_name
       1          abc
       2          xyz
       3          efg

Table 2 : Payments
    payment_id     payment_date     hotel_id     total_amt    comission
       p1           23-03-2010          1          100           10
       p2           23-03-2010          2          50            5
       p3           23-03-2010          2          200           25
       p4           23-03-2010          1          40            2

现在，我需要从这两个表中获取以下详细信息

给定特定日期（例如 2010 年 3 月 23 日），每个酒店的 Total_amt 总和已在该特定日期付款。
根据酒店名称排序日期为 23-03-2010 的所有行

示例输出如下...

+------------+------------+------------+---------------+
| hotel_name |   date     |  total_amt |   commission  |
+------------+------------+------------+---------------+
| * abc      | 23-03-2010 |     140    |      12       |
+------------+------------+------------+---------------+
|+-----------+------------+------------+--------------+|
|| paymt_id  |   date     |  total_amt |   commission ||
|+-----------+------------+------------+--------------+|
||   p1      | 23-03-2010 |     100    |     10       ||
|+-----------+------------+------------+--------------+|
||   p4      | 23-03-2010 |     40     |     2        ||
|+-----------+------------+------------+--------------+|
+------------+------------+------------+---------------+
| * xyz      | 23-03-2010 |     250    |      30       |
+------------+------------+------------+---------------+
|+-----------+------------+------------+--------------+|
|| paymt_id  |   date     |  total_amt |   commission ||
|+-----------+------------+------------+--------------+|
||   p2      | 23-03-2010 |     50     |      5       ||
|+-----------+------------+------------+--------------+|
||   p3      | 23-03-2010 |     200    |      25      ||
|+-----------+------------+------------+--------------+|
+------------------------------------------------------+

上面是必须打印的表格示例...

这个想法首先要显示合并的详细信息每个酒店的名称，当点击酒店名称旁边的“*”时，付款详细信息的明细将变得可见......但这可以通过一些 jquery 来完成..！该表本身可以使用 php 生成...

现在我正在使用两个单独的查询：一个用于获取按酒店名称分组的金额和佣金的总和。接下来是获取表中具有该日期的每个条目的单独行。当然，这是因为对用于计算 sum() 的记录进行分组只为每个酒店返回一行以及金额之和...

有没有一种方法可以将这两个查询合并为一个查询并执行操作以更优化的方式...??

希望我说得清楚..感谢您的时间和回复...

编辑：也添加了查询

查询以获取总和()
<前> <代码> 选择 DATE_FORMAT($ payments. payment_date, '%d-%m-%Y %T') 作为 payment_date, $ payments. payment_id AS payment_id, $ payments. payment_amount AS payment_amount, $ payments.agent_commission AS 佣金， $ payments.comm_percentage AS百分比， $hotels.hotel_name AS 酒店 SUM($付款.付款金额) AS tot_amt SUM($ payments.agent_commission) AS tot_ag_comsn FROM $ 付款加入$酒店上$ payments.hotel_id = $hotels.hotel_id WHERE DATE_FORMAT(付款日期,'%d-%m-%Y') = '$date_report' 按 $付款.hotel_id 分组按 $ payment. payment_date ASC 排序
查询以获取各个行
<前> <代码> 选择 DATE_FORMAT($ payments. payment_date, '%d-%m-%Y %T') 作为 payment_date, $ payments. payment_id AS payment_id, $ payments. payment_amount AS payment_amount, $ payments.agent_commission AS 佣金， $ payments.comm_percentage AS百分比， $hotels.hotel_name AS 酒店 FROM $ 付款加入$酒店上$ payments.hotel_id = $hotels.hotel_id WHERE DATE_FORMAT(付款日期,'%d-%m-%Y') = '$date_report' 按 $ payment. payment_date 分组按 $ payment. payment_date ASC 排序
我将两个查询返回的行添加到两个单独的临时表中，然后使用 php 打印该表，如上所示...

编辑 2：另外，如果有人为这篇文章建议一个更好的标题，我们将不胜感激...;)

原文

Say, I got these two tables....

Table 1 : Hotels
   hotel_id   hotel_name
       1          abc
       2          xyz
       3          efg

Table 2 : Payments
    payment_id     payment_date     hotel_id     total_amt    comission
       p1           23-03-2010          1          100           10
       p2           23-03-2010          2          50            5
       p3           23-03-2010          2          200           25
       p4           23-03-2010          1          40            2

Now, I need to get the following details from the two tables

Given a particular date (say, 23-03-2010), the sum of the total_amt for each of the hotel for which a payment has been made on that particular date.
All the rows that has the date 23-03-2010 ordered according to the hotel name

A sample output is as follows...

+------------+------------+------------+---------------+
| hotel_name |   date     |  total_amt |   commission  |
+------------+------------+------------+---------------+
| * abc      | 23-03-2010 |     140    |      12       |
+------------+------------+------------+---------------+
|+-----------+------------+------------+--------------+|
|| paymt_id  |   date     |  total_amt |   commission ||
|+-----------+------------+------------+--------------+|
||   p1      | 23-03-2010 |     100    |     10       ||
|+-----------+------------+------------+--------------+|
||   p4      | 23-03-2010 |     40     |     2        ||
|+-----------+------------+------------+--------------+|
+------------+------------+------------+---------------+
| * xyz      | 23-03-2010 |     250    |      30       |
+------------+------------+------------+---------------+
|+-----------+------------+------------+--------------+|
|| paymt_id  |   date     |  total_amt |   commission ||
|+-----------+------------+------------+--------------+|
||   p2      | 23-03-2010 |     50     |      5       ||
|+-----------+------------+------------+--------------+|
||   p3      | 23-03-2010 |     200    |      25      ||
|+-----------+------------+------------+--------------+|
+------------------------------------------------------+

Above the sample of the table that has to be printed...

The idea is first to show the consolidated detail of each hotel, and when the '*' next to the hotel name is clicked the breakdown of the payment details will become visible... But that can be done by some jquery..!!! The table itself can be generated with php...

Right now i am using two separate queries : One to get the sum of the amount and commission grouped by the hotel name. The next is to get the individual row for each entry having that date in the table. This is, of course, because grouping the records for calculating sum() returns only one row for each of the hotel with the sum of the amounts...

Is there a way to combine these two queries into a single one and do the operation in a more optimized way...??

Hope i am being clear.. Thanks for your time and replies...

EDIT : Added Queries Too

Query to get the sum()

 
SELECT DATE_FORMAT($payments.payment_date, '%d-%m-%Y %T') as payment_date,
        $payments.payment_id AS payment_id,
        $payments.payment_amount AS payment_amount,
        $payments.agent_commision AS commision,
        $payments.comm_percentage AS percentage,
        $hotels.hotel_name AS hotel
        SUM($payments.payment_amount)  AS tot_amt
        SUM($payments.agent_commision) AS tot_ag_comsn
FROM $payments
JOIN $hotels
ON   $payments.hotel_id = $hotels.hotel_id
WHERE DATE_FORMAT(payment_date,'%d-%m-%Y') = '$date_report'
GROUP BY $payments.hotel_id
ORDER BY $payments.payment_date ASC

Query to get the individual rows

   
SELECT DATE_FORMAT($payments.payment_date, '%d-%m-%Y %T') as payment_date,
            $payments.payment_id AS payment_id,
            $payments.payment_amount AS payment_amount,
            $payments.agent_commision AS commision,
            $payments.comm_percentage AS percentage,
            $hotels.hotel_name AS hotel
FROM $payments
JOIN $hotels
ON   $payments.hotel_id = $hotels.hotel_id
WHERE DATE_FORMAT(payment_date,'%d-%m-%Y') = '$date_report'
GROUP BY $payments.payment_date
ORDER BY $payments.payment_date ASC

I add the rows returned by the two queries in two separate temporary tables and then use php to print the table as shown above...

EDIT 2 : And also, appreciate if someone suggests a better title for this post... ;)

分享到QQ

分享到微博