mysql - 在 MySQL 的嵌套 Select 语句中使用 Select 数据

Question

我在 MySQL 中有一个查询。

完整查询

SELECT 
  tc.expense AS expense,
  tc.tour_sub_code,
  tc.login_id

FROM
  tc_wallet tc 
WHERE tc.login_id = 'vinod.kumbala' 
  AND tc.expense = 'Daily Allowance' 
  AND tc.delete_flag = 'F' 
  AND tc.status != 'reject' 

结果

Expense              Tour_sub_code          login_id 

DAILY ALLOWANCE       MOS-EUROPE100119      vinod.kumbala
DAILY ALLOWANCE       Test                  vinod.kumbala

最初我从tc_wallet 表中获取数据。
现在我的要求是我需要找到特定 tour_sub_code 的总出席人数。可以从 attendance_master 表中找到 that 特定 tour_sub_code 的出席人数。所以我包含了一个嵌套的选择查询，它是

嵌套选择

(SELECT 
    COUNT(*) 
  FROM
    (SELECT 
      * 
    FROM
      `attendance_master` 
    WHERE `delete_flag` = 'F' 
      AND login_id = 'vinod.kumbala' 
      AND `tour_sub_code` = tc.`tour_sub_code` 
    GROUP BY `device_date`) t1) AS newNoOfdays 

完整查询

SELECT 
      tc.expense AS expense,
      tc.tour_sub_code,
      tc.login_id,
      (SELECT 
        COUNT(*) 
      FROM
        (SELECT 
          * 
        FROM
          `attendance_master` 
        WHERE `delete_flag` = 'F' 
          AND login_id = 'vinod.kumbala' 
          AND `tour_sub_code` = tc.`tour_sub_code` 
        GROUP BY `device_date`) t1) AS newNoOfdays 

    FROM
      tc_wallet tc 
    WHERE tc.login_id = 'vinod.kumbala' 
      AND tc.expense = 'Daily Allowance' 
      AND tc.delete_flag = 'F' 
      AND tc.status != 'reject' 

现在这个查询给我的错误是

Unknown column 'tc.tour_sub_code' in 'where clause'

预期结果

Expense              Tour_sub_code          login_id            Count

    DAILY ALLOWANCE       MOS-EUROPE100119      vinod.kumbala     20
    DAILY ALLOWANCE       Test                  vinod.kumbala     44

我能知道我哪里出了问题吗？
还有其他方法可以通过使用 JOINS 获得结果吗？

Answer 1

在最内层的子查询中，您引用了最外层查询中的列。 FROM 子句(派生表)中的子查询不允许这样做。但是 - 你不需要那个子查询。你需要的是 COUNT(DISTINCT device_date)

重写

(SELECT 
    COUNT(*) 
  FROM
    (SELECT 
      * 
    FROM
      `attendance_master` 
    WHERE `delete_flag` = 'F' 
      AND login_id = 'vinod.kumbala' 
      AND `tour_sub_code` = tc.`tour_sub_code` 
    GROUP BY `device_date`) t1) AS newNoOfdays 

到

(SELECT 
    COUNT(DISTINCT device_date)
  FROM `attendance_master` 
  WHERE `delete_flag` = 'F' 
    AND login_id = 'vinod.kumbala' 
    AND `tour_sub_code` = tc.`tour_sub_code` 
) AS newNoOfdays 

您还可以将完整查询重写为 LEFT JOIN 查询:

SELECT 
  tc.expense AS expense,
  tc.tour_sub_code,
  tc.login_id,
  COUNT(DISTINCT device_date) AS newNoOfdays 
FROM tc_wallet tc
LEFT JOIN attendance_master am
  ON  am.tour_sub_code = tc.tour_sub_code
  AND am.delete_flag = 'F' 
  AND am.login_id = 'vinod.kumbala' 
WHERE tc.login_id = 'vinod.kumbala' 
  AND tc.expense = 'Daily Allowance' 
  AND tc.delete_flag = 'F' 
  AND tc.status != 'reject'