Mysql 查询给出重复行

Question

SELECT 
        tc.expense AS Expense
        ,tc.`tour_sub_code`
        ,tc.login_id
        ,tc.currency
        ,tc.amount
        ,tc.date
        ,tc.from_date
        ,tc.to_date
        ,tc1.NoOfDays
        ,tc1.NoOfDays*tc.amount totalAmount

        FROM tc_wallet tc ,

       (SELECT 
        DATEDIFF(
          STR_TO_DATE(to_date, '%Y-%m-%d'),
          STR_TO_DATE(From_date, '%Y-%m-%d')
        ) NoOfDays 
      FROM
        tc_wallet 
      WHERE expense = 'DAILY ALLOWANCE' 
        AND login_id = 'sunny.thangaraj') AS tc1 

        WHERE tc.login_id = 'sunny.thangaraj'
        AND tc.expense = 'DAILY ALLOWANCE'       

在上面的查询中，我从同一个表中选择数据两次
1)单列数据
2)同一个表的列之间的DateDiff并用它来计算总金额

但它给了我两次输出，

输出

DAILY ALLOWANCE TCEB161017  sunny.thangaraj USD 50  12-02-2018  2018-12-08  2018-12-10  2   100
DAILY ALLOWANCE TCEB161017  sunny.thangaraj USD 50  12-02-2018  2018-12-08  2018-12-10  2   100
DAILY ALLOWANCE TCEB010915  sunny.thangaraj USD 2   17-01-2014  2018-12-08  2018-12-10  2   4
DAILY ALLOWANCE TCEB010915  sunny.thangaraj USD 2   17-01-2014  2018-12-08  2018-12-10  2   4

与 TOURSUBCODE TCEB010915 一样，TCEB161017 出现了 2 次，这是相同的并且是不需要的。
谁能告诉我我在这里做错了什么？
是因为我正在做Select From A,(Select From A)

Answer 1

删除重复项的最简单方法是使用 SELECT DISTINCT 而不是 SELECT。

但是您最好尝试改进查询逻辑。在这里我没有看到使用子查询的充分理由，il 应该只选择一次就可以了，如下所示；

SELECT 
    tc.expense AS Expense
    ,tc.`tour_sub_code`
    ,tc.login_id
    ,tc.currency
    ,tc.amount
    ,tc.date
    ,tc.from_date
    ,tc.to_date
    ,DATEDIFF(
      STR_TO_DATE(tc,to_date, '%Y-%m-%d'),
      STR_TO_DATE(tc.From_date, '%Y-%m-%d') NoOfDays
    ,DATEDIFF(
      STR_TO_DATE(tc.to_date, '%Y-%m-%d'),
      STR_TO_DATE(tc.From_date, '%Y-%m-%d')* tc.amount totalAmount

FROM tc_wallet tc

WHERE tc.login_id = 'sunny.thangaraj'
    AND tc.expense = 'DAILY ALLOWANCE'