SELECT pr.PRODUCT_ID, pr.PRODUCT_NAME, pr.ORIGINAL_PRICE, pr.ONLINE_PRICE, pr.COMMISSION_PERCENT, pr.TOTAL_SALES, pr.SHOP, dict.label AS COMMERCE_TYPE_LABEL, pr.SERVICE_RATE, ord.ORDER_ID, date.label AS SEND_FLG_LABEL, pro.CONNECT_BUSINESS_FLG, address.APPLY_REMARK, address.CREATE_TIME, users.nicknameFROM t_product AS prLEFT JOIN t_order AS ord ON ord.PRODUCT_ID = pr.IDLEFT JOIN ( SELECT label, `value` FROM system_dict_data WHERE dict_type = 'COMMERCE_TYPE' ) AS dict ON dict.`value` = pr.COMMERCE_TYPE LEFT JOIN ( SELECT label, `value` FROM system_dict_data WHERE dict_type = 'trade_after_sample_type' ) AS date ON date.`value` = ord.SEND_FLGLEFT JOIN t_apply_pro AS pro ON pro.PRODUCT_ID = pr.IDLEFT JOIN t_apply_pro_user_address AS address ON address.t_apply_pro_id = pro.IDLEFT JOIN system_users AS users ON users.id = pro.OPERATE_USER_ID WHERE users.id = '1'

这是一个SQL语句，查询了多张表的数据并进行了联合查询，最终筛选出符合条件的数据。具体查询条件为：查询商品表t_product中的PRODUCT_ID、PRODUCT_NAME、ORIGINAL_PRICE、ONLINE_PRICE、COMMISSION_PERCENT、TOTAL_SALES、SHOP、COMMERCE_TYPE_LABEL、SERVICE_RATE字段，同时关联订单表t_order，查询ORDER_ID、SEND_FLG_LABEL字段，关联申请表t_apply_pro，查询CONNECT_BUSINESS_FLG字段，关联申请地址表t_apply_pro_user_address，查询APPLY_REMARK、CREATE_TIME字段，同时关联用户表system_users，查询nickname字段。其中，使用LEFT JOIN进行表的关联，使用WHERE进行条件筛选，筛选条件为users.id = '1'。

相关问题

修改 错误 SELECT pr.PRODUCT_ID, pr.PRODUCT_NAME, pr.ORIGINAL_PRICE, pr.ONLINE_PRICE, pr.COMMISSION_PERCENT, pr.TOTAL_SALES, pr.SHOP, dict.label AS COMMERCE_TYPE_LABEL, pr.SERVICE_RATE, ord.ORDER_ID, date.label AS SEND_FLG_LABEL, pro.CONNECT_BUSINESS_FLG, address.APPLY_REMARK, address.CREATE_TIME, users.nicknameFROM t_product AS prLEFT JOIN t_order AS ord ON ord.PRODUCT_ID = pr.IDLEFT JOIN ( SELECT label, `value` FROM system_dict_data WHERE dict_type = 'COMMERCE_TYPE' ) AS dict ON dict.`value` = pr.COMMERCE_TYPE LEFT JOIN ( SELECT label, `value` FROM system_dict_data WHERE dict_type = 'trade_after_sample_type' ) AS date ON date.`value` = ord.SEND_FLGLEFT JOIN t_apply_pro AS pro ON pro.PRODUCT_ID = pr.IDLEFT JOIN t_apply_pro_user_address AS address ON address.t_apply_pro_id = pro.IDLEFT JOIN system_users AS users ON users.id = pro.OPERATE_USER_ID WHERE users.id = '1'

以下是修改后的SQL语句，主要是为了使语句更加易读和易懂：

SELECT 
    pr.PRODUCT_ID, 
    pr.PRODUCT_NAME, 
    pr.ORIGINAL_PRICE, 
    pr.ONLINE_PRICE, 
    pr.COMMISSION_PERCENT, 
    pr.TOTAL_SALES, 
    pr.SHOP, 
    dict.label AS COMMERCE_TYPE_LABEL, 
    pr.SERVICE_RATE, 
    ord.ORDER_ID, 
    date.label AS SEND_FLG_LABEL, 
    pro.CONNECT_BUSINESS_FLG, 
    address.APPLY_REMARK, 
    address.CREATE_TIME, 
    users.nickname
FROM 
    t_product pr
    LEFT JOIN t_order ord ON ord.PRODUCT_ID = pr.ID
    LEFT JOIN (
        SELECT label, `value` 
        FROM system_dict_data 
        WHERE dict_type = 'COMMERCE_TYPE'
    ) AS dict ON dict.`value` = pr.COMMERCE_TYPE 
    LEFT JOIN (
        SELECT label, `value` 
        FROM system_dict_data 
        WHERE dict_type = 'trade_after_sample_type'
    ) AS date ON date.`value` = ord.SEND_FLG
    LEFT JOIN t_apply_pro pro ON pro.PRODUCT_ID = pr.ID
    LEFT JOIN t_apply_pro_user_address address ON address.t_apply_pro_id = pro.ID
    LEFT JOIN system_users users ON users.id = pro.OPERATE_USER_ID 
WHERE 
    users.id = '1'

修改后的SQL语句对语句进行了格式化和缩进，使得语句更加易读和易懂。同时，还对表名和字段名进行了简化，以减少语句的长度，使其更加简洁。

SELECT bs.sample_id, bs.item_id, bs.report_id, bs.order_no, bs.order_id, bs.order_business_type, bs.commission_date, bs.customer_name, bs.applicant, bs.phone, bs.receive_user_name, bs.contract_no, bs.special_requirements, bs.report_org_name, bs.report_org_address, bs.sample_name, bs.standard_instrument_name, bs.complete_day, bs.sample_remark AS remark, bs.standard_instrument_id, bs.sample_no, bs.factory_number, bs.item_name, /*bs.item_quantity,*/ bs.inspection_type, bs.mandatory_flag, bs.test_quantity, bs.sample_state, bs.current_site, bs.plan_complete_date, bs.affix, bs.ranges, bs.grade, bs.factory, bs.calibrat_point, bs.apply_dept, bs.specification, bs.final_fee, bs.service_type, CASE WHEN bs.actual_complete_date IS NOT NULL THEN DATEDIFF( bs.plan_complete_date, bs.actual_complete_date ) ELSE datediff( bs.plan_complete_date, now()) END AS surplus_days, bs.report_no, bs.is_report_back, bs.back_reason AS report_back_reason, bs.is_just_certificate, bs.report_state, bs.temper, bs.humidity, bs.test_result, bs.test_date, bs.next_test_date, bs.test_cycle, bs.test_address, bs.generate_time, bs.point_report_id, bs.is_merge, bs.circulation_flag, bs.item_proposal_fee AS proposal_fee, bs.change_price_reason, bs.test_user_name, bs.group_id, bs.group_name, bs.charging_num, bs.other_fee, bs.receivable_fee, bs.affix_quantity, bs.test_org, bs.out_org_order_no, bs.out_org_sample_no, bs.business_user_name, bs.pdf_path, bs.settlement_state, bs.result_describe, bsa.attach_id FROM view_sample_info bs JOIN bus_sample_report bsr ON bs.report_id = bsr.id JOIN bus_sample sa ON bsr.sample_id = sa.id JOIN bus_sample_attr bsa ON sa.id = bsa.id 根据bs.commission_date 进行排序最近的排上面 bs.commission_date

你可以使用 `ORDER BY` 子句根据 `bs.commission_date` 字段进行排序，以将最近的日期排在前面。以下是示例代码：

SELECT
    bs.sample_id,
    bs.item_id,
    bs.report_id,
    bs.order_no,
    bs.order_id,
    bs.order_business_type,
    bs.commission_date,
    bs.customer_name,
    bs.applicant,
    bs.phone,
    bs.receive_user_name,
    bs.contract_no,
    bs.special_requirements,
    bs.report_org_name,
    bs.report_org_address,
    bs.sample_name,
    bs.standard_instrument_name,
    bs.complete_day,
    bs.sample_remark AS remark,
    bs.standard_instrument_id,
    bs.sample_no,
    bs.factory_number,
    bs.item_name,
    /*bs.item_quantity,*/
    bs.inspection_type,
    bs.mandatory_flag,
    bs.test_quantity,
    bs.sample_state,
    bs.current_site,
    bs.plan_complete_date,
    bs.affix,
    bs.ranges,
    bs.grade,
    bs.factory,
    bs.calibrat_point,
    bs.apply_dept,
    bs.specification,
    bs.final_fee,
    bs.service_type,
    CASE
        WHEN bs.actual_complete_date IS NOT NULL THEN
            DATEDIFF(bs.plan_complete_date, bs.actual_complete_date)
        ELSE
            DATEDIFF(bs.plan_complete_date, now())
        END AS surplus_days,
    bs.report_no,
    bs.is_report_back,
    bs.back_reason AS report_back_reason,
    bs.is_just_certificate,
    bs.report_state,
    bs.temper,
    bs.humidity,
    bs.test_result,
    bs.test_date,
    bs.next_test_date,
    bs.test_cycle,
    bs.test_address,
    bs.generate_time,
    bs.point_report_id,
    bs.is_merge,
    bs.circulation_flag,
    bs.item_proposal_fee AS proposal_fee,
    bs.change_price_reason,
    bs.test_user_name,
    bs.group_id,
    bs.group_name,
    bs.charging_num,
    bs.other_fee,
    bs.receivable_fee,
    bs.affix_quantity,
    bs.test_org,
    bs.out_org_order_no,
    bs.out_org_sample_no,
    bs.business_user_name,
    bs.pdf_path,
    bs.settlement_state,
    bs.result_describe,
    bsa.attach_id
FROM
    view_sample_info bs
    JOIN bus_sample_report bsr ON bs.report_id = bsr.id
    JOIN bus_sample sa ON bsr.sample_id = sa.id
    JOIN bus_sample_attr bsa ON sa.id = bsa.id
ORDER BY
    bs.commission_date DESC;

在上述 SQL 查询中，我们添加了 `ORDER BY` 子句，并使用 `bs.commission_date DESC` 来按照 `bs.commission_date` 字段降序排列结果。这将把最近的日期放在前面。如果你想升序排列，可以使用 `ASC` 关键字，如 `bs.commission_date ASC`。