表A：A_ID,A_CODE 表B：B_ID,B_STATE,B_CODE,B_LOT 联表查询 统计表A的ID记录数，A_CODE=B_CODE，统计表B的B_LOT去重后B_LOT数量，统计B_LOT去重后B_STATE=0，B_LOT去重后B_STATE=1数量，B_STATE可能为空或者null，mysql的sql如何表示

你可以使用以下SQL语句来实现你的需求：

SELECT COUNT(A.A_ID) AS A_Record_Count, 
       COUNT(DISTINCT B.B_LOT) AS Distinct_B_LOT_Count, 
       COUNT(DISTINCT CASE WHEN B.B_STATE = 0 THEN B.B_LOT END) AS Distinct_B_LOT_State_0_Count, 
       COUNT(DISTINCT CASE WHEN B.B_STATE = 1 THEN B.B_LOT END) AS Distinct_B_LOT_State_1_Count
FROM 表A A
JOIN 表B B ON A.A_CODE = B.B_CODE;

这个SQL查询会联结表A和表B，并根据A_CODE和B_CODE进行匹配。然后，使用聚合函数COUNT来统计不同的记录数。使用COUNT(DISTINCT ...)可以去重统计B_LOT和B_STATE的数量。CASE WHEN语句用于条件判断，根据B_STATE的值来统计符合条件的B_LOT数量。

相关问题

优化以下SQL，给出优化后的SQL：SELECT un.*, sta.*, CASE WHEN COALESCE(un.lot_time, 0) > COALESCE(sta.dock_time_limit, 0) THEN COALESCE(un.lot_time, 0) - COALESCE(sta.dock_time_limit, 0) ELSE 0 END AS lotDuration FROM unload_over_time un LEFT JOIN ( SELECT parking_lot_code, dock_time_limit FROM stall_infor WHERE data_state = 0 AND delete_state = 1 AND industry_park_code='YQWQML' ) sta ON un.parking_lot_code = sta.parking_lot_code INNER JOIN ( SELECT DISTINCT plate_number FROM supplier_info WHERE data_state = 0 AND delete_state = 1 AND arrived_atetime IS NOT NULL AND left_datetime IS NULL AND industry_park_code='YQWQML' ) sup ON un.plate_number = sup.plate_number WHERE un.data_state = 0 AND un.delete_state = 1 AND un.is_out_of_time = 1 AND un.industry_park_code='YQWQML' AND un.parking_lot_code IN ( SELECT DISTINCT parking_lot_code FROM unload_over_time WHERE data_state = 0 AND delete_state = 1 AND is_out_of_time = 1 AND industry_park_code='YQWQML' )

优化后的SQL如下：

SELECT un.*, sta.*,
  CASE WHEN COALESCE(un.lot_time, 0) > COALESCE(sta.dock_time_limit, 0) THEN COALESCE(un.lot_time, 0) - COALESCE(sta.dock_time_limit, 0) ELSE 0 END AS lotDuration
FROM unload_over_time un
LEFT JOIN stall_infor sta ON un.parking_lot_code = sta.parking_lot_code
INNER JOIN supplier_info sup ON un.plate_number = sup.plate_number
WHERE un.data_state = 0
  AND un.delete_state = 1
  AND un.is_out_of_time = 1
  AND un.industry_park_code='YQWQML'
  AND EXISTS (
    SELECT 1
    FROM unload_over_time uot
    WHERE uot.data_state = 0
      AND uot.delete_state = 1
      AND uot.is_out_of_time = 1
      AND uot.industry_park_code='YQWQML'
      AND uot.parking_lot_code = un.parking_lot_code
    )
  AND EXISTS (
    SELECT 1
    FROM stall_infor si
    WHERE si.data_state = 0
      AND si.delete_state = 1
      AND si.industry_park_code='YQWQML'
      AND si.parking_lot_code = un.parking_lot_code
    )
  AND EXISTS (
    SELECT 1
    FROM supplier_info sp
    WHERE sp.data_state = 0
      AND sp.delete_state = 1
      AND sp.arrived_atetime IS NOT NULL
      AND sp.left_datetime IS NULL
      AND sp.industry_park_code='YQWQML'
      AND sp.plate_number = un.plate_number
    )

优化后的SQL做了以下几点改进：

1. 将子查询转化为 EXISTS 子句，避免使用 DISTINCT 关键字和 IN 子查询，提高查询性能。

2. 将 LEFT JOIN 子查询转化为直接的 LEFT JOIN 操作，避免嵌套查询，简化SQL结构。

3. 合并了相同的过滤条件，减少重复的条件判断。

优化后的SQL应该能够提高查询性能。但请注意，在实际应用中，最佳的优化方案还需要根据具体的数据量、索引等因素进行调整和测试。